ToolBox

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.

Analyzing Perfmon with SQL Query (Part 1)

Performance counter is very useful for analyzing OS and SQL Server performance. Most DBA knows how to collect performance counter, so I won’t write it in this post. Just in case You don’t know, check Brent’s post here. Once the counters are collected, the next step is to analyze to find performance problem. I posted about Perfmon analysis with PAL , but in this post I will show different method by importing to SQL Server and query it T-SQL.
Using T-SQL query has several advantages over PAL:

  • You have full control on what counters to analyze and how to summarize (average, min. max, etc)
  • You can compare several sets of data collection and see their trends. You can filter the query and build crosstab report to see the trending between different time interval

So the steps to do it are as follow:

  1. Create empty database in SQL Server
  2. Setup ODBC System DSN
  3. Import Perfmon to SQL Server with relog tool
  4. Analyze perfmon by using T-SQL

 

Create empty database in SQL Server

Create an empty database in SQL Server, as this will be a storage for imported data. Sizing is important because the data is quite big. In my case, 1GB of Perfmon file becomes 3GB after imported into SQL Server. It’s a massive growth, but You will know why after seeing the information stored in the database. I usually pre-size the data file to 5GB with 100MB growth to improve performance. Don’t forget to change recovery mode to simple to prevent unexpected growth of log file.

 

Setup ODBC System DSN

The next step is to create ODBC DSN to connect to SQL Server. This is straight forward, by using ODBC Administrator in control panel. I choose the 64 bit version because both my client machine and SQL Server is 64 bit version. Make sure also to select System DSN instead of other type of DSN.

odbc-1

For some reason, relog will only work with SQL Server legacy driver instead of SQL Server native client. So You need to choose “SQL Server” driver instead of SQL Server Native Client when selecting driver type.

odbc-2

The next screen will ask for connection name, SQL Server instance name, and credential to connect to SQL Server. Other important note to specify the default database name to the one created in previous step. I created empty database PerfDB, so I specify the same name as my default database here.

odbc-3

 

Once created, test the connection to make sure it’s properly configured.

My machine is Windows 8.1 64 bit with SQL Server 2012 (also 64 bit). I created System ODBC DSN (64 bit) with SQL Server driver version 6.xxx and relog was successfully connected to my SQL Server. Initially, I used SQL Server Native Client with 32 or 62 bit combination, but always encountered error when executing relog command.

 

Import Perfmon to SQL Server with relog tool

Once ODBC is configured, the next step is to import Perfmon data to SQL Server. Relog is installed by default in your Windows, so no need to add additional tool. The syntax is as follow:

relog perfmonFileName.blg -f SQL -o SQL:ODBCDriverName!CounterIdentifier

The CounterIdentifier is any name to tag your data collection set. For example, If I have 3 Perfmon files and all of them was collected during August 2013 then I can group them together under one identifier: DataAugust2013. This is very useful when I want to group several Perfmon files under different category. I can collect baseline data and import it under “Baseline” identifier. Then collect another one and tag it under “Tuning” identifier.

To run the relog command, follow the following steps:

Open command prompt and navigate to the location of perfmon file. To make it easy, You need to be in the same folder with perfmon file. Run the following command:

relog1

Let me explain the command here:

  • The name of Perfmon file is DataCollector01.blg
  • The output type is SQL Server, by using -f parameter
  • The ODBC System DSN name is relog5
  • The identifier is BaselineSept2013

The command will import all counters data to SQL Server. If you want to filter the data by applying additional parameter, please read full relog tutorial here and here.

 

See what’s We got after import

Let’s connect to PerfDB and see what We have after import: there are 3 tables created inside the DB.

relog_db

 

Run the following simple query to see what We got in every tables:

select*from[dbo].[DisplayToID]

selecttop3 *from[dbo].[CounterDetails]

selecttop3 *from[dbo].[CounterData]

 

The query yields the following result:

relog_db1

As You can see here, I put BaselineSept2013 as identifier and It’s reflected in the table under DisplayString column. I also imported another set of files and name it as TuningDec2013. In this case, I have 2 group of imported files that I can compare to see the improvement between the baseline and tuning period.

Now let’s play the interesting part: querying imported data to analyze SQL Server performance. I will post this in Part 2, stay tuned :).

 

 

Easy Server Monitoring with PAL

The first step in SQL Server performance tuning usually starts with finding bottleneck at the server side. It includes monitoring to some suspicious symptoms on CPU, Memory, Disk, and various SQL Server counter. Yes, Performance Monitor tool is already available on Windows machine. It is a very valuable tool for detecting the server bottleneck, and it is free. Just type perfmon and we are ready to start to do monitoring.

Usually I create counter log and let the monitoring runs at the background based on specific schedule and sampling interval. With this way, I can minimize the performance impact by running Perfmon inside the box. Once I got the log data, then I am ready to start the analysis. There are thousands or even millions rows of logs inside. How do I analyze and finding the system behavior, detect the pattern and plan a possible actions? Obviously I can import the CSV log file to SQL table or Excel and then use my T-SQL knowledge to summarize the data. But actually there is easier way to do it, Clint Huffman of Microsoft already created PAL.

The PAL does query to the counter log data using Log Parser, and display it in a nice HTML format. It is a graphical chart data analysis, so that’s why PAL requires Office Web Component installed to view the report. We can download PAL from CodePlex, and don’t forget to install Log Parser and OWC11 as pre-requisites. So the steps to do analysis with PAL are:

  • Collect counter log data with Perfmon
  • Use counter log as input to PAL
  • Analyze the report with PAL

The first screen of PAL will ask a location of counter log file that was created by Perfmon.

image

At the next screen, there are several options of system monitoring template. It depends of what kind of servers installed in the system.

image

The good things is PAL already comes with predefined template and threshold, so we do not need to think about the threshold for various counter. We con modify this template as well to suit our needs.

Here are several reports generated by PAL, very nice analysis with graphical trends.

image

image

Extract and Transfer RDL files from SSRS

I was in the situation when I had to modify some reports, but I did not have the source code (RDL) files on hand. I tried to go to SSRS server, and looking for download or save-as menu. I was expecting there are some ways to download the report definition so I can modify it under Visual Studio.

Unfortunately not, there is no built-in features in SSRS that for download or extract report from the server. Once deployed, it is there forever. I just thought to create small application using SSRS web services or API to extract the definition. But I was lucky enough that Jasper Smith created an amazing tool for this purpose.

I downloaded the RSScripter and install it on my machine. It can connect to both SQL 2000 and 2005, and generate the RDL, user, roles, and also the data source. This free tool is really fantastic.

rsscripter1

I can also use this stuff to transfer the SSRS content from one server to another. A very good toolkit for server migration and deployment. The configuration is under option button, specify to transfer and as well as destination server.

rsscripter2