Configure PSSDIAG to capture high CPU issues

I am sure most of the SQL community is aware of the PSSDIAG and how helpful it is when it comes to troubleshooting performance issues. In this blog , I will discuss about how to configure the PSSDIAG in high CPU Scenarios.

Here are few blogs which I would like to share about how we can download and configure PSSDIAG:

Download PSSDIAG: – http://diagmanager.codeplex.com/releases/view/67449

Configure PSSDIAG :- http://sqlbg.wordpress.com/2011/06/15/pssdiag-and-sqldiag-manager/
http://troubleshootingsql.com/2011/05/25/tools-tips-and-tricks-9-pssdiag-configuration-manager/

Please be aware, this PSSDIAG is only for SQL server 2005,2008 and for earlier version you will still need to use sqldiag :- http://support.microsoft.com/kb/830232

Here are the things which we can configure in the PSSDIAG(SQL database engine):

1. Profiler Traces
2. SQL DMV outputs
3. Perfmon

Before configuring the PSSDIAG, we have to be very clear about the workload on the environment especially when it’s production. When we run the PSSDIAG, there can be slight performance degradation and if the workload is very high then it can even cause outage (if not configured properly). The heaviest resource taker in the PSSDIAG is profiler traces and in busy environments they can be disabled to avoid any outage/issue.

There may be a question in your mind – How to differentiate between low and high workload? Well, this question is open ended and different people may have different answers.  Here is what I think about it (completely based on my experiance)

Low Workload : – less than 100 batches per second
Medium Workload : – 100 – 500(approx.) batches per second
High Workload: – greater than 500 batches per second

The above is just a rough idea but any load even with less than 100 batches can be a high load depending upon the type of queries and throughput of the system. So, the configuration with the profiler traces needs some logic otherwise the configuration is very simple :

1. Select the architcture X64,X86 or IA64
2. Select Instance type(SQL 2005 or SQL 2008)
3. Enter machine name and instance name
4. Uncheck the SQL trace button

5. Select the files shown in the screen shot(SQL 2008 Perf Stats,SQL 2005 Perf Stats,SQL memory error,SQL blocking) and leave rest of the things to default unless there is any specifc requirement.

Here you go, PSSDIAG without traces for high CPU is ready. This approach works for heavy load environments especially for SQL server 2008. Because in SQL 2008, we can find out the high CPU queries accurately even without having profiler traces. The secret behind this the new feature called Plan hash and query hash which was introduced in SQL server 2008.

I am sure you will be aware of plan hashes(only SQL 2008 onwards): –  http://blogs.msdn.com/b/bartd/archive/2008/09/03/query-fingerprints-and-plan-fingerprints_3a00_-the-best-new-sql-2008-feature-you_2700_ve-never-heard-of.aspx (must read). And yes, we capture plan hashes in the PSSDIAG for SQL 2008 and which eliminates the need of profiler traces .

But for SQL 2005, we have to rely on sys.dm_exec_query_stats and as mentioned in the above blog, it’s not 100% correct. But it gives us fair idea of which query may be the culprit.

Now, what if we need to configure the PSSDIAG along with the profiler traces, here are the steps for that:

1. Select the architcture X64,X86 or IA64
2. Select Instance type(SQL 2005 or SQL 2008)
3. Enter machine name and instance name
4. In profiler traces, here are the events you could capture(Specifc for High CPU only).

5. Select the files shown in the screen shot(SQL 2008 Perf Stats,SQL 2005 Perf Stats,SQL memory error,SQL blocking) and leave rest of the things to default unless there is any specifc requirement.

Once this is configured, we are ready with the PSSDIAG to be run.  For various options on how PSSDIAG can be scheduled to run at specifc times, please check the article : – http://support.microsoft.com/kb/830232. I have created the PSSDIAG for SQL server 2008 and 2008 R2. Please copy the files , save them as .xml and open in the PSSDIAG by clicking on open button on the top left( PSSDiag_2008,PSSDiag_2008R2). Please test it before running it on production server.

In my next post, I will explain how can we analyze the PSSDIAG output logs without using SQL Nexus and what are the main files that we have to look at while troublehoosing high CPU issues.

Advertisements

3 thoughts on “Configure PSSDIAG to capture high CPU issues

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s