Tag Archive for SQL Performance

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 :).

 

 

Practical SQL Performance Troubleshooting

DBA daily life is always hectic. They receive many issues ranging from network, performance, security, or application. I had collected many scripts and tools since couple of years ago. They are very useful for ad-hoc troubleshooting and diagnose the problem on the spot.

When I mentioned ad-hoc means the ability to locate and diagnose the problem quickly, without collecting data with SQL Profiler or Perfmon. I’m not saying that ad-hoc approach is better, but there are many situations when we just do not have enough time to collect the data while my users are screaming. The DMVs become handy tools for this purpose. It shipped since SQL Server 2005, and has been improved in 2008. I always has those DMVs in my shortcut folder, or in most cases in my thumb drive.

I had the opportunity to do demo on this topic at Singapore SQL User Group meeting last month. The response was fantastic, knowing that we have many free scripts to solve our daily problem. I promised to upload all presentation and sample codes, so here they are:

Some Notes on SQL Server 2008 Backup compression

I blogged about backup compression here, and I mentioned that the main advantages are saving space, reduced backup and restore time. After discussion with some fellow MVPs, I have to revise my statement in previous post.

Yes, backup compression saves backup and restore time only if there are enough CPU and IO resources available. This is obvious because IO is the biggest bottleneck when reading/writing backup file. Thanks to Aaron and Andrew for remind me about this fact.

However, IO bottleneck is solvable if we use fast removable storage (eSATA for example) instead of internal hard disk. With this way, we can take full advantage of compression without affecting current machine performance.

The SQL Customer Advisory team did a very comprehensive benchmark on this. Below is some screen shoot I cut and paste from their blog post:

sqlcatcompress1

sqlcatcompress2

The complete post for benchmark by SQLCAT is here

http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx