Analyzing Perfmon with SQL Query (Part 2)

I wrote about how to import Perfmon file into SQL Server in previous post, and now I let’s see how to analyze it using SQL query. I will write couple of examples that I usually use. The exact query may varies depending on the requirement and server environment. The most common question are as follow:

  • how high is CPU utilization?
  • Do I have enough memory in the system?
  • Is there bottleneck in the disk?

A quick query will help to give high level idea, but in-depth analysis is required to pinpoint what’s actually happens in the system. High CPU does not always mean a CPU problem, until detail examination is done to other related counters. In many cases, We need to investigate SQL Server internal by using DMV or Profiler to get more comprehensive views. I will blog this separately, so let’s focus on how to mine the data from imported perfmon.

 

Simple Query: How’s my CPU and Memory?

I wanted to see CPU and Memory information in the system. This is quite simple to do by querying the tables as follow:

SELECT ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), 
		CASE
			WHEN ct.[InstanceName] IS NULL THEN ''
			WHEN ct.[InstanceName] IS NOT NULL THEN RTRIM(ct.InstanceName)
		END AS "InstanceName", 
			AVG(cda.CounterValue) as Average,
			Min(cda.CounterValue) as MinValue,
			MAX(cda.CounterValue) as MaxValue,
			STDEV(cda.CounterValue) as StdValue,
			COUNT (cda.CounterValue) as Freq ,
			di.DisplayString
FROM CounterData cda
	JOIN CounterDetails ct ON cda.CounterID = ct.CounterID
	JOIN DisplayToID di ON cda.GUID = di.GUID
WHERE ((ct.CounterName = '% Processor Time' AND ct.ObjectName = 'Processor')
	OR [ct].[CounterName] = 'Available MBytes')
GROUP BY ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), ct.InstanceName, di.DisplayString
ORDER BY di.DisplayString, RTRIM(ct.ObjectName), RTRIM(ct.CounterName)

Here is the query result from my sample data:

counter-simple

It’s clear that average CPU utilization after tuning is lower than the baseline. It goes down from 19.4% to 13.3%. It’s also useful to get not only average, but also MIN, MAX, and Standard deviation. This gives better idea on various spike that might happen during sampling. High variation means the counter is up and down frequently.

I usually use the following formula to assess system variation: Average + 2 x STDEV. Recalling to basic statistical lesson, the interval between average and 2xVariation represents 68% of population. I’m not statistical expert though, You may refer to better sampling theory for more accuracy.

 

Filtering Data only for Working Hours

Let’s continue further by adding additional filter to the query above. Assuming that the busy hours is in daytime, I can add additional filter by limiting CounterDateTime column. There is a small caveat here: the column is in char with many whitespaces so it will fail in Datetime conversion. Combining TRIM or LEFT function with CONVERT to force it become Datetime should solve the problem.

----filter by working hours 9AM to 5pm
SELECT ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), 
		CASE
			WHEN ct.[InstanceName] IS NULL THEN ''
			WHEN ct.[InstanceName] IS NOT NULL THEN RTRIM(ct.InstanceName)
		END AS "InstanceName", 
			AVG(cda.CounterValue) as Average,
			Min(cda.CounterValue) as MinValue,
			MAX(cda.CounterValue) as MaxValue,
			STDEV(cda.CounterValue) as StdValue,
			COUNT (cda.CounterValue) as Freq ,
			di.DisplayString
FROM CounterData cda
	JOIN CounterDetails ct ON cda.CounterID = ct.CounterID
	JOIN DisplayToID di ON cda.GUID = di.GUID
WHERE ((ct.CounterName = '% Processor Time' AND ct.ObjectName = 'Processor')
	OR [ct].[CounterName] = 'Available MBytes')
	AND DATEPART(HOUR,
					(CONVERT(datetime, LEFT([CounterDateTime],19)))) BETWEEN 9 AND 17
GROUP BY ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), ct.InstanceName, di.DisplayString
ORDER BY di.DisplayString, RTRIM(ct.ObjectName), RTRIM(ct.CounterName)

 

Using PIVOT to compare Baseline and Tuning data
I have 2 sets of counter data in my database: BaselineSept2013 and TuningDec2013. Comparing them should give better view to narrow down the analysis. There are couple of important counters related to SQL Server performance, so I usually pull the following for quick analysis:

  • % Processor Time
  • Avg. Disk sec/Read
  • Avg. Disk sec/Write
  • Available MBytes
  • Page life expectancy
  • Index Searches/sec
  • Full Scans/sec

Each counters are related each other, so it’s important to understand their relationship and not to judge system performance by looking to only CPU. Brent has a very good post about this. Jonathan also wrote nice explanation on fundamental SQL Server counter in his post.

I can use CTE as temporary storage before PIVOT, and select only 7 counters that useful for quick SQL Server analysis. The following query compares performance data between baseline and tuning data set:

--summarize Average Value with pivot, and compare between Baseline and Tuning dataset
WITH CTEPerfData(SourceData, CounterID, ObjectName,	CounterName, 
						InstanceName, CounterValue)
AS
(
	SELECT di.DisplayString, ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), 
			CASE
				WHEN ct.[InstanceName] IS NULL THEN ''
				WHEN ct.[InstanceName] IS NOT NULL THEN RTRIM(ct.InstanceName)
			END AS "InstanceName", 
				cda.CounterValue
	FROM CounterData cda
		JOIN CounterDetails ct ON cda.CounterID = ct.CounterID
		JOIN DisplayToID di ON cda.GUID = di.GUID
	WHERE ((ct.CounterName = '% Processor Time' AND ct.ObjectName = 'Processor')
		OR ([ct].[CounterName] = 'Avg. Disk sec/Read' )
		OR ([ct].[CounterName] = 'Avg. Disk sec/Write' )
		OR [ct].[CounterName] = 'Available MBytes'
		OR [ct].CounterName = 'Page life expectancy'
		OR [ct].CounterName = 'Index Searches/sec'
		OR [ct].CounterName = 'Full Scans/sec')
			AND DATEPART(HOUR,
					(CONVERT(datetime, LEFT([CounterDateTime],19)))) BETWEEN 9 AND 17
)
SELECT * FROM CTEPerfData
		PIVOT (AVG(CounterValue) FOR SourceData IN ([BaselineSept2013], [TuningDec2013])
											) as pvt

The query yields the following results, so I can compare important counters side by side before and after tuning (Sept and Dec) data.

perfmon-pivot

It worth to explain relationship between counter and how to derive conclusion of system performance in the next post. In the meantime, You may need to read Jimmy May’s post about VitalSign spreadsheet.

Leave a Reply