This is to document a bug we have uncovered in SQL Server 2012. Microsoft have acknowledged this bug and are releasing a fix for it in SQL Server 2012 SP1 CU3.

Background

We have monitoring software which registers a trace and reads data from this trace in order to provide metrics. We noticed that after moving to our new SQL 2012 platform that we had performance problems every few days during our peak/busy season. At one point they were happening every other day. The symptoms we saw were an increase in CPU, and an increase in recompiles. We felt that the CPU increase was due to the recompiles, but the key thing was that the busier the system was, the quicker we knew the problem was going to happen – recompiles went up high, CPU maxed and the system crawled to a halt.

In order to resolve as we couldn’t figure what was going on initially, we failed over to our HA node. Our solution uses AlwaysOn and we had a HA node on the local site which was synchronous so this was easy and took a matter of seconds. Failing over to our secondary replica instantly fixed the problem, but we would see the problem occur in the next day or so and we would have to flip back. This went on for a week or so and we raised support tickets with Microsoft to get to the bottom of the issue.

To cur a long story short, by the time we were through to the right department and we had run all the standard diagnostics, we had already determined ourselves that the issue was due to the CACHESTORE_OBJCP entry in sys.dm_os_memory_cache_counters and specifically that the value for pages_kb was excessively high. We recorded a value of 8.5GB during one of the issues, which was high for our 140GB RAM server.

CACHESTORE_OBJCP

CACHESTORE_OBJCP is a cache store in memory for object plans (stored procs, triggers and functions). As for any of the cache stores, if they get 75% towards the calculated ‘Cache Plan Pressure Limit’ (see technet article at the end) it triggers an internal ‘memory pressure’ event. This is to alert SQL Server that there is memory pressure and SQL Server starts reacting to that trigger.

Tracking down the leak!

We were able to track down the statement which caused the object olan cache store to grow by monitoring the dm_exec_cached_plans dmv after failover (ordering by size_in_bytes DESC). The field to look at here as well is the objtype column – we don’t care about prepared types when we’re looking at the OBJCP cache store which only deals with procs, triggers and functions plans.

We collected readings from dm_exec_cached_plans every 3 minutes and we noticed one compiled plan which arose to the top of the list every time. It grew to the maximum size_in_bytes this dmv would allow with it being and int (214783647):

We can only presume that it further grows in the background to make up the value of the OBJCP cachestore value.

We can figure out which SQL statement the plan belongs to by CROSS APPLYing sys.dm_exec_sql_text:

SELECT TOP 10 *
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
order by size_in_bytes DESC

For us this showed the statement sp_trace_getdata which we tracked down to the trace SQL Sentry (performance monitoring) uses to collect data.

That’s not to say that SQL Sentry is the cause here. We raised it to SQL Sentry’s attention however and they are keen to learn about this as we are.

Resolution

We found that restarting the SQL Sentry service resolved the issue during peak times. The pages_kb value for CACHESTORE_OBJCP would then reset and start growing slowly again. We then put a job in place which kills the trace (SQL Sentry restarts the trace automatically) and this stopped the issue getting out of control and causing us problems at peak times.

We raised these facts with Microsoft and asked them if there were any known bugs around sp_trace_getdata or OPENROWSET which may cause the object case to bloat. They used our data collected to uncover this was a bug and it has been scheduled to be included in SP1 CU3 which is scheduled for March.

Conclusion

Obviously, this bug is not due to our monitoring service being SQL Sentry, this bug could have quite happily raised itself from any product which uses the same method, and if we had Sentry pointing at a different version of SQL there would have been no problem.

Keep an eye on http://blogs.msdn.com/b/sqlreleaseservices/ for the release of SQL Server 2012 SP1 CU3 to resolve this issue.

References:

Technet – Cache Plan Internals

This has been a very handy script recently. I created it so I can calculate the surface area of data and log files on disk. It can be executed remotely as long as you have administrative permissions. Using the script below I can for example calculate the log files total size on a server excluding TempDB as follows:

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "LOGS"

Or I can find the size of the DATA files (again excluding TempDB):

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "LOGS"

I was asked recently to split a bunch of databases from one server onto two servers. I used the function get a list of all the files on a server (data and logs), and then I could figure out how much disk space would be needed by each side of the split (I took the figures into Excel for this bit…i’m not an animal!).

So in order to find out the totals for data AND logs I would execute it as follows:

Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB

It’s been a handy one for the toolbelt recently. The full code listing is below. Use the copy button which pops up to copy the code.

Enjoy.

<#
    .SYNOPSIS
        Get-SQLDataFileSize function returns a file size for each SQL file and a total
    .DESCRIPTION
        Get-SQLDataFileSize function returns a file size for each SQL file and a total. 
	This can be limited to calculating just one database, or all databases and to just the log files, or the data files.
    .PARAMETER ServerInstance
        SQL Instance of the server to query. This must be on the local machine, unless you have administrative access to the remote machine.
    .PARAMETER ExcludeTempDB
        If you pass this switch, the calculations will not take into account the TempDB files.
    .PARAMETER DatabaseName
        The name of the database you require to calculate. If you include this option it will only calculate for this database, rather than all the databases on the server.
    .PARAMETER FileType
	Options: ROWS, DATA, LOG, LOGS, LDF, MDF, NDF, ALL. The Default is ALL. 
	
	ROWS/DATA	: all data files (excluding logs)
	LOG/LOGS	: all log files
	MDF		: only files with the MDF extension
	NDF		: only files with the NDF extension
	LDF		: only files with the LDF extension

	ALL		: includes all files regardless of extension and whether it is LOG or DATA.


    .EXAMPLE
	Get-SQLDataFileSize -ServerInstance DBS01\DEV -ExcludeTempDB -FileType "DATA"
    .EXAMPLE
	Get-SQLDataFileSize -ServerInstance DBS01\DEV -DatabaseName "ReportServer"
#>
function Get-SQLDataFileSize () 
{
	Param
	(
		[parameter(Mandatory=$True)][string] $ServerInstance,
		[switch] $ExcludeTempDB,
		[parameter(Mandatory=$False)][string] $DatabaseName=$null,
		[parameter(Mandatory=$False)][ValidateSet('ROWS','DATA','LOG','LOGS','LDF','MDF','NDF','ALL')][string]$FileType="ALL",
		[switch] $ShowFullPaths
	)


	[string] $type_desc = "";
	[string] $ext = "";

	switch ($FileType)
		{
			"ROWS" { $type_desc = "ROWS"; $ext = $null}
			"DATA" { $type_desc = "ROWS"; $ext = $null}
			"LOG" { $type_desc = "LOG"; $ext = $null}
			"LOGS" { $type_desc = "LOG"; $ext = $null}
			"LDF" { $type_desc = $null; $ext = ".ldf"}
			"NDF" { $type_desc = $null; $ext = ".ndf"}
			"MDF" { $type_desc = $null; $ext = ".mdf"}
			"ALL" { $type_desc = $null; $ext = $null}
		}

	$sql = "
			SELECT 
				SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Hostname], 
				physical_name, 
				CASE WHEN SUBSTRING(physical_name,2,1) = ':' 
					THEN '\\' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(255)) + '\' + REPLACE(physical_name,':','$') 
				END [physical_name_urn]
			FROM sys.master_files

			WHERE (type_desc = '$type_desc' OR '$type_desc' ='')
			AND (RIGHT(physical_name,4) = '$ext' OR '$ext' = '')
			AND (database_id = db_id('$DatabaseName') OR '$DatabaseName' = '')
			AND (database_id != db_id('tempdb') OR '$ExcludeTempDB'='False')
		";

	$logFiles = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query $sql `
			| ForEach-Object { Get-Item $_.physical_name_urn }

	if($ShowFullPaths) {
		$logFiles = $logFiles | Select FullName, Length
	} else {
		$logFiles = $logFiles | Select Name, Length
	}

	
		
	$logFilesSum = ($logFiles | Measure-Object -Property Length -Sum).Sum / 1GB
	
	$logFiles	
	"         {0:N2}" -f $logFilesSum + " GB"
}

A while ago at work now we were given a requirement that an external company were going to log ship a database over to us. I was asked to design a robust PowerShell function which we could use to regularly check and apply transaction log backups to a database backup which they had supplied. In essence Log Shipping via PowerShell, or at least the LSRestore portion of it.

Knowing how many problems I’ve had over the years with Log Shipping, I wanted to make the function as robust as possible within the time I had, as well as allowing flexibility so the function could be reused if needs be.

I looked into how the current LSRestore works – specifically: How does it check the file to ensure it can be applied to the backup?

My findings led me to use the stored proc [master].[sys].[sp_can_tlog_be_applied] within the function to check if the file was applicable. Then, if the file is applicable it is restored with the various options passed from the parameters. Finally, a check is done on the restorehistory tables to ensure the transaction log backup was applied succesfully. If there are any problems, the function will throw an error, which will cause any job step that is executing it to fail also.

The end product was Invoke-LSRestore below which we have had running now for a few months. Of course this will come nowhere near to the SQL Server LSRestore, but it is a PowerShell alternative if you are in a similar situation where you need to use it.

We have had it running regularly via a SQL Agent job on one of our production environments for the last few months, but as with any post I make – always test, test and test again on your own environment.

I created another function “Handle-Output” which was just a nicety really for running it in the console.

Handle-Output :

function Handle-Output($Object,$ForegroundColor="white") {
		if ($host.Name -eq "ConsoleHost") {
			Write-Host -ForegroundColor $ForegroundColor -Object $Object
		} else {
			Write-Output $Object
		}
}

Invoke-LSRestore :

function Invoke-LSRestore($ServerInstance, $DatabaseName,$BackupLocation,[switch]$Debug,[switch]$Standby,[switch]$NoRecovery)
{
	$now = Get-Date -Format u
	Handle-Output -Object "$now - Restore Started" -ForegroundColor "Green"
	
	$now = Get-Date -Format u
	Handle-Output -Object "$now - Settings:"	-ForegoundColor DarkYellow
	Handle-Output "`tServer: $ServerInstance"	-ForegoundColor DarkYellow
	Handle-Output "`tDatabase: $DatabaseName"	-ForegoundColor DarkYellow
	Handle-Output "`tBackup Source: $BackupLocation"	-ForegoundColor DarkYellow

	$FilesRestored = 0

	$BackupLocSQL = "
		DECLARE @backuploc as TABLE (Value varchar(255), Data varchar(255))

		INSERT INTO @backuploc(Value,Data)
		EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

		SELECT TOP 1 Data
		FROM @backuploc"

	$DefaultBackupLocation = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $BackupLocSQL | ForEach-Object {$_.Data}
	if ($debug -eq $TRUE) {Handle-Output -Object $DefaultBackupLocation -ForegroundColor "DarkYellow"}

	if ($debug) { get-childitem -path $BackupLocation\$DatabaseName*.trn | Select-Object Name | Sort-Object Name }
	
	get-childitem -path $BackupLocation\$DatabaseName*.trn | Sort-Object Name | ForEach-Object {
		$trn_path = $_
		$sql = "
			declare @p3 bit
			set @p3=1

			exec master.sys.sp_can_tlog_be_applied 
				 @backup_file_name=N'$trn_path'
				,@database_name=N'$DatabaseName'
				,@result=@p3 output

			select @p3 [Result]"
			
		if ($debug -eq $TRUE) {Handle-Output -Object $sql -ForegroundColor "DarkYellow"}
		
		$ret = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $sql | ForEach-Object {$_.Result}
		
		if ($debug -eq $TRUE) {Handle-Output -Object $ret -ForegroundColor "DarkYellow"}
		
		if ($ret -eq $TRUE)
		{
			$RestoreSQL = "
				RESTORE LOG [$DatabaseName] 
				FROM  DISK = N'$trn_path'
				WITH  NOUNLOAD,  STATS = 10"
				
				if($Standby) {
					$RestoreSQL = $RestoreSQL + ", STANDBY =N'$DefaultBackupLocation\ROLLBACK_UNDO_$DatabaseName.TUF'";
				}
				if($NoRecovery) {
					$RestoreSQL = $RestoreSQL + ", NORECOVERY";
				}
			
			if ($debug) {$RestoreSQL}
			
			$now = Get-Date -Format u
			Handle-Output -Object "$now - Attempting to restore: `'$trn_path`'" -ForeGroundColor "Yellow"
			
			$RestoreResult = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $RestoreSQL -Database Master -ErrorAction Stop -QueryTimeout 600
			
			if ($debug -eq $TRUE) {Handle-Output -Object $RestoreResult -ForegroundColor "DarkYellow"}
			
			$CheckRestoreSQL = "
				SELECT CAST(1 as bit) [IsRestored]
				FROM msdb.dbo.restorehistory h
				INNER JOIN msdb.dbo.backupset bs ON h.backup_set_id = bs.backup_set_id
				INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
				WHERE 
					h.destination_database_name = '$DatabaseName' 
				AND bmf.physical_device_name = '$trn_path'
				AND bs.[type]='L'
			"
			
			if ($debug) {$checkRestoreSQL}
			
			$RestoreResult = $FALSE
			$RestoreResult = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $CheckRestoreSQL | ForEach-Object {$_.IsRestored}
			
			if ($RestoreResult -eq $TRUE)
			{
				Handle-Output -Object "Restored Successfully`n" -ForegroundColor "Green"
			} else {
				Throw "Restore Failed!"
			}
			
			$FilesRestored ++;
		} else {
			Handle-Output -Object "`'$trn_path`' skipped." -ForegroundColor yellow
		}
	}
	
	Handle-Output -Object "Number of files restored $FilesRestored." -ForegroundColor "Green"
}

# Invoke-LSRestore -ServerInstance "UATDBS01" -DatabaseName "Accounts" -BackupLocation "Y:\MSSQL\Backups" -NoRecovery
# Invoke-LSRestore -ServerInstance "INTDBS01" -DatabaseName "Sales" -BackupLocation "Y:\MSSQL\Backups" -NoRecovery

I came across one of our SQL Servers which had the product number 10.50.1757. There didn’t seem much in the way of documentation to support this version number, but we came across a link which the file versions matched this product:

http://support.microsoft.com/kb/2492381

FIX: SQL Server 2008 R2 performs poorly when most threads wait for the CMEMTHREAD wait type if the threads use table variables or temp tables to load or update rowsets