Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel

Last updated Mar 28, 2003.

There are actually multiple ways to evaluate the performance of a SQL Server system. I’ve explained how to examine the system from a holistic point of view — starting with the various components that make up the round-trip of data from the client through the network, the middle tiers (if any) and then on to the database system. Even within those components there are sub-components. For instance, within the client, middle-tier and database computers there is a CPU, a set of memory chips, network interfaces and storage units. In fact, even within those components are sub-components. Hard Drives have processors, small motherboard, memory caches and more.

I still advocate using that process to tune the system. While of course I’ll explain the specific steps around the SQL Server itself, it’s important to keep that holistic view in mind at all times.

Measuring Wait States

What I’ve explained to this point for performance tuning is the method of evaluating the components that are facing the most “pressure” on your database server — or the busiest components. But that’s actually only one way of considering the overall performance for the SQL Server part of the equation.

In this tutorial, I’ll introduce another method of evaluating where you should start your tuning efforts. This methodology doesn’t measure what is taking the most time in the system, it measures what the system is waiting on. And to do that, I’ll point you back to the “Dynamic Management Views” I explained in an earlier tutorial.

Along the way, I’ll use some of the other tools I’ve explained on this site — PowerShell and Excel. PowerShell, as I’ve explained in other articles, is a scripting environment built on the Dot-Net framework. And so is Microsoft Excel. I’ve explained how to use PowerShell to run a query against SQL Server, so in this tutorial I’ll combine all of those things to query SQL Server about its “Wait States” to find out what the system is waiting on. I’ll put that into an Excel spreadsheet so that you can visualize the data.

Wait States

Within SQL Server, there are various things that are happening at any one time. Microsoft has assigned various “categories” for these events, and called them “Wait States”. Each has a cause — some are internal, others are caused by query activity such as waiting on the logging events, locking and so on. Evaluating the wait states will tell you what to focus on. Reduce the wiats and you’lll speed up the system.

The Wait-State Query

So how do you find out what the system is waiting on? You can use a variety of tools, but the simplest is to query the sys.dm_os_wait_stats Dynamic Management View (DMV).

This view returns a lot of information, but I’m normally only after the top ten or so things that I should focus on, and normally I’m only interested in the ones that are taking the most time. You can query that DMV with a SELECT * (which will get everything), but this query, which I modified from one I found at this site, gives me the two columns I’m interested in – the wait type and the maximum time it has spent on my system. Try this on your test system:

SELECT TOP 10 
wait_type AS 'WaitType', max_wait_time_ms AS 'MaxWaitTime'
FROM    sys.dm_os_wait_stats
WHERE   wait_type NOT IN -- remove system waits   
('KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT',
 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',
 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'LAZYWRITER_SLEEP',
 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
 'LAZYWRITER_SLEEP', 'ONDEMAND_TASK_QUEUE',
 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'SLEEP_TASK',
 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC

That returns a fairly easy to read set of information, and on my system it returned the WRITELOG wait as the top item. I did a web search on that term, and I discovered that mean that the logging process was waiting, usually for the hard drive to respond. Sure enough, the hard drive where I had the log files was far too busy — I moved the files and things moved along faster.

The PowerShell Script

With that investigation complete, I decided to take that query and turn it into a PowerShell script. I’ve explained how to work with a PowerShell Query in another tutorial, so I’ll point you to that in the references below if you’re not familiar with it. Here’s the script, and you can see at the end that I’m storing the results in a variable called $Waits:

# Waits from SQL Server  to Excel Chart
# Keep this next part on one line... This gets your objects to put in the chart

$serverName = "UNIVAC"
$databaseName = "master"
$qry = @"
SELECT TOP 10 
wait_type AS 'WaitType', max_wait_time_ms AS 'MaxWaitTime'
FROM    sys.dm_os_wait_stats
WHERE   wait_type NOT IN -- remove system waits   
('KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT',
 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',
 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'LAZYWRITER_SLEEP',
 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
 'LAZYWRITER_SLEEP', 'ONDEMAND_TASK_QUEUE',
 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'SLEEP_TASK',
 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC
"@

function Get-SqlData {
param([string]$serverName=$(throw 'serverName is required.'), 
[string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))     
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)    
$dt = New-Object "System.Data.DataTable"    
[void]$da.fill($dt)    
$dt 
} 
$Waits = Get-SqlData $serverName $databaseName $qry

Note that if you want to run this script on your system, you’ll need to change the server name to your Instance. Once you run it, you can just type $Waits to show the table returned.

Now all I need to do is create and Excel spreadsheet and then add a worksheet to it. From there, I’ll add the data from the table I returned from SQL Server. After that, I’ll take that data and add it to a chart in the Excel workbook. Here’s everything all put together:

$serverName = "UNIVAC"
$databaseName = "master"
$qry = @"
SELECT TOP 10 
wait_type AS 'WaitType', max_wait_time_ms AS 'MaxWaitTime'
FROM    sys.dm_os_wait_stats
WHERE   wait_type NOT IN -- remove system waits   
('KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT',
 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',
 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'LAZYWRITER_SLEEP',
 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
 'LAZYWRITER_SLEEP', 'ONDEMAND_TASK_QUEUE',
 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'SLEEP_TASK',
 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC
"@

function Get-SqlData {
param([string]$serverName=$(throw 'serverName is required.'), 
[string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))     
Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)    
$dt = New-Object "System.Data.DataTable"    
[void]$da.fill($dt)    
$dt 
} 
$Waits = Get-SqlData $serverName $databaseName $qry

$excel = new-object -comobject excel.application
$excel.visible = $true
$chartType = "microsoft.office.interop.excel.xlChartType.xlBarClustered" -as [type]
$workbook = $excel.workbooks.add()
$workbook.WorkSheets.item(1).Name = "Waits"
$sheet = $workbook.WorkSheets.Item("Waits")
$x = 2
$sheet.cells.item(1,1) = "Wait Type"
$sheet.cells.item(1,2) = "Max Wait Time"
Foreach($Wait in $Waits)
{
$sheet.cells.item($x,1) = $Wait.WaitType
$sheet.cells.item($x,2) = $Wait.MaxWaitTime
$x++
} 
$range = $sheet.usedRange
$range.EntireColumn.AutoFit()
$workbook.charts.add()
$workbook.ActiveChart.SetSourceData($range)

The Output

While the data is easy enough to look at, it really jumps out when you see it in a graph. Not only that, with the code stored in PowerShell, it can now be automated in multiple schedulers, including those in Windows or even in SQL Server 2008 SQL Agent Jobs.

Now I can track the wait states any time.

InformIT Articles and Sample Chapters

My Reference Guide series on working with PowerShell is here. The PowerShell script for running a query is here.

Books and eBooks

The “power” of PowerShell is in the fact it can work with SQL Server, the operating system, Excel and lots of other software. Learn more about that in Windows PowerShell Unleashed.

Online Resources

Want more PowerShell tips? Check out my MSDN series on that here.