Archive for category Performance

Performance analysis with Wireshark

Here’s a three part video series from Paul Offord demonstrating SQL Server performance analysis with Wireshark:

http://www.youtube.com/watch?v=fY9S72FJbmE

http://www.youtube.com/watch?v=lM0p8ng_Yo4

http://www.youtube.com/watch?v=vVxyFl65Yx0

Paul loads the Wireshark CSV output into Excel and uses formulas to calculate the difference between network request and response times. The CSV output could just as easily be loaded into a SQL Server table and the same values calculated using subqueries or analytic functions. Paul charts the results in Part 3 of the video. There’s a good illustration of blocking in one of the charts.

Leave a comment

Identifying high CPU SQL Server threads

Some great tips are available to those of us who subscribe to the SQLSkills Insiders email list (http://www.sqlskills.com/JoinCommunity.asp). The 29th April 2012 email featured a video by Jonathan Kehayias demonstrating how to use Process Explorer to identify high CPU SQL Server threads. Jonathan showed that once you’ve identified the thread IDs you can use the following SQL to obtain the query and plan details:

-- Source: SQLSkills Insiders email 29/04/2012
-- Use the system thread id to find the executing session information
SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <YourThreadID>

Today’s blog post presents a Powershell script that can be used in multi-instance environments (eg clusters) to identify high CPU SQL Server threads. The script uses the performance monitor counters for threads.

The long standing issue for multiple instances is that performance monitor differentiates each running sqlservr.exe process using a hash followed by a number. Trying to relate these hashes to the actual SQL Server instances can be difficult. For example, if you have three instances of SQL Server the instance names for the threads performance counters will be as follows:

For the first SQL Server instance: \\computername\thread(sqlservr/0)\<countername> to \\computername\thread(sqlservr/xxx)\<countername> where xxx is the number of threads you have for this instance

For the second SQL Server instance: \\computername\thread(sqlservr/0#1)\<countername> to \\computername\thread(sqlservr/xxx#1)\<countername>

For the third SQL Server instance: \\computername\thread(sqlservr/0#2)\<countername> to \\computername\thread(sqlservr/xxx#2)\<countername>

Using the registry change listed in the following Microsoft support document

http://support.microsoft.com/kb/281884

it is possible to replace the hashes with process IDs. We then have meaningful names that we can relate back to our SQL Server instances. Along the same lines as ProcessNameFormat, another document

http://msdn.microsoft.com/en-us/library/windows/desktop/aa372175%28v=vs.85%29.aspx

mentions ThreadNameFormat. Using both these format keys we are able replace the meaningless hash symbol and /0, /1, /2 with meaningful names. Our full counter names will now appear as follows:

\\computername\thread(sqlservr_3012/4896)\<countername>

With these registry changes in place the Powershell script simply fetches five samplings of the thread % processor time. For each distinct process ID it groups on processID/threadID (eg 3012/4896 for the example above) and calculates the average, minimum, maximum and count for the five samplings. It finally sorts by the average descending and selects the first five rows.


$ComputerName = "YOURCOMPUTERNAME"

$PerfCounter = "Thread(sqlservr*)\% Processor Time"

$MaxSamples = 5

$ThreadData = Get-Counter -ComputerName $ComputerName -Counter $PerfCounter -MaxSamples $MaxSamples -ErrorAction:SilentlyContinue |
ForEach-Object {$_.CounterSamples | Select-Object Timestamp,CookedValue,Path}

# Distinct process IDs
$PerfCounterPrefix = $ThreadData | Select-Object -Unique @{Name="PerfCounterPrefix";Expression={ $_.Path.SubString(0, $_.Path.IndexOf("/"))}}

# For each of the distinct process IDs select the five threads with the highest average % processor time
ForEach ($Prefix in $PerfCounterPrefix) {
$ThreadData |
Where-Object {$_.Path -match (Select-Object -ExpandProperty $Prefix)} |
# This groups by ProcessID/ThreadID
Group-Object -Property { $_.Path.SubString($_.Path.IndexOf("_") + 1, $_.Path.IndexOf(")") - $_.Path.IndexOf("_") - 1) } |
Select-Object `
@{Name="ProcessID";Expression={($_.Name -split "/")[0]}}, # Left side of / is ProcessID
@{Name="ThreadID";Expression={($_.Name -split "/")[1]}},  # Right side of / is ThreadID
@{Name="PercentProcessorAvg";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Average).Average,2)}},
@{Name="PercentProcessorMin";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Minimum).Minimum,2)}},
@{Name="PercentProcessorMax";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Maximum).Maximum,2)}},
@{Name="NumberOfSamples";Expression={[Math]::Round(($_.Group | Measure-Object).Count,2)}} |
Sort-Object -Property PercentProcessorAvg -Descending |
Select-Object -First 5 |
Format-Table -Auto
}

Please take note of the line continuation back ticks.

Example output:

For a multi-instance server you’ll have five rows for each process ID. You can identify the named instance by plugging the process ID into this (replace xxxx):

(Get-WmiObject Win32_Service -ComputerName $ComputerName -Filter “ProcessId=xxxx”).Name

You can use this same approach to identify other performance metrics at the thread or process level.

Leave a comment

Workload analysis using query statistics

Earlier posts described how you can collect and analyse waits to identify resource limitations and constraints for your SQL Servers. This post outlines a similar approach for identifying workload impact.

In many cases we use server side traces to answer questions such as ‘what is running against my SQL Server ?’. Such questions normally arise as a result of performance issues affecting a front end application. When used with event and column filters SQL traces are very good at identifying specific troublesome queries. Extended events, as an alternative to traces, also meet this requirement. But to answer a very high level question such as ‘has my workload on this server increased or decreased ?’ using traces you’d have to capture practically all queries and analyse the results using tools such as SQL Nexus and RML utilities.

A much easier way to answer overall workload questions is to collect and analyse the query statistics that SQL Server automatically saves for you. They can be found in the sys.dm_exec_query_stats table. By sampling this table at regular intervals you can plot trends for execution counts, worker times, durations, reads, writes, row counts etc. The statistics are available at the statement level granularity. This means you have performance data for ad hoc statements, prepared statements and the individual statements for stored procedures, functions and triggers. By saving historic figures at this level of granularity you can answer questions not just at the overall workload level but also at the individual statement level. For example, a question such as ‘has the performance of a specific statement in a stored procedure degraded over time ?’

There are caveats to using sys.dm_exec_query_stats. Because the statistics are associated with cached plans certains executions will not have their statistics stored. For example, last_execution_time, execution_count etc won’t be updated if you execute a stored procedure WITH RECOMPILE. Certain commands will not have query statistics entries – BEGIN TRAN, ROLLBACK TRAN, DBCC, BACKUP etc. The only way to deal with these missing entries is to capture the entire workload using traces. The downside is the potentially tens of gigabytes worth of trace files that can be generated for busy systems. So, using query statistics for the analysis we trade off some of the commands we may miss for the saving in disk space and overhead of processing a potentially large number of trace files.

Gauging the impact of missing commands is fairly straightforward. You compare a trace analysis with a query statistics analysis. If total reads, total duration, total cpu from a trace analysis match the corresponding totals from a query statistics analysis then for the given workload you’ve validated the query statistics approach.

Having set the background we’ll move on to describe one approach to collecting and analysing data from sys.dm_exec_query_stats.

In SQL 2008 and above we have the query_hash column. Grouping on the query_hash, statement start offset and statement end offset we can aggregate the values for execution count, total work time, last reads etc to measure the workload hitting your SQL Server. To minimise the impact of statistics collection the approach listed here does not fetch the actual query text. This can easily be obtained once the overall workload picture is presented.

Just as for the collection of wait, latch and spinlock statistics (see early post), the approach here is to collect the query statistics from target servers and store the data in a warehouse on another server. Compared to the earlier post the only dimension table that changes is the dimServer table. We add a column to store the date and time that query statistics were last fetched. We avoid resampling the whole data by making use of this column. Here is the modified dimServer table:

-- Server table
IF OBJECT_ID('dbo.dimServer','U') IS NOT NULL DROP TABLE dbo.dimServer;
GO

CREATE TABLE dbo.dimServer
(
ServerID int NOT NULL IDENTITY(1,1),
ServerName sysname NOT NULL,
QueryStatsLastFetch datetime NOT NULL CONSTRAINT df_dimServer_QueryStatsLastFetch DEFAULT ('19900101')
CONSTRAINT pk_dimServer PRIMARY KEY CLUSTERED (ServerID),
CONSTRAINT uq_dimServer UNIQUE NONCLUSTERED (ServerName)
);

-- Pre-populate with your list of servers
INSERT INTO dbo.dimServer (ServerName)
SELECT CAST(SERVERPROPERTY('ServerName') AS sysname);

The procedure to get and set the QueryStatsLastFetch column is here:

IF OBJECT_ID('dbo.procServerQueryStatsLastFetch','P') IS NOT NULL DROP PROCEDURE dbo.procServerQueryStatsLastFetch;
GO

CREATE PROCEDURE [dbo].[procServerQueryStatsLastFetch]
@GetSet char(3),
@ServerName sysname,
@QueryStatsLastFetch datetime OUTPUT
AS
BEGIN

SET NOCOUNT ON

IF UPPER(@GetSet) = 'SET'
BEGIN
UPDATE dbo.dimServer SET QueryStatsLastFetch = @QueryStatsLastFetch WHERE ServerName = @ServerName
END

IF UPPER(@GetSet) = 'GET'
BEGIN
SELECT @QueryStatsLastFetch = QueryStatsLastFetch FROM dbo.dimServer WHERE ServerName = @ServerName
END

RETURN
END;

The query_hash, statement_start_offset and statement_end_offset are stored in another dimension table and are referenced through the QueryID column:

IF OBJECT_ID('dbo.dimQuery','U') IS NOT NULL DROP TABLE dbo.dimQuery;
GO

CREATE TABLE dbo.dimQuery
(
QueryID int IDENTITY(1,1) NOT NULL,
ServerID int NOT NULL,
query_hash binary(8) NOT NULL,
statement_start_offset int NOT NULL,
statement_end_offset int NOT NULL,
CONSTRAINT pk_dimQuery PRIMARY KEY CLUSTERED (QueryID),
CONSTRAINT uq_dimQuery UNIQUE NONCLUSTERED (ServerID,query_hash,statement_start_offset,statement_end_offset)
);

The query statistics are stored in this fact table:

IF OBJECT_ID('dbo.factQueryStats','U') IS NOT NULL DROP TABLE dbo.factQueryStats;
GO

CREATE TABLE dbo.factQueryStats(
ServerID int NOT NULL,
DateID int NOT NULL,
TimeID int NOT NULL,
QueryID int NOT NULL,
query_hash_count int NULL,
plan_generation_num bigint NULL,
creation_time datetime NULL,
last_execution_time datetime NULL,
execution_count bigint NULL,
total_worker_time bigint NULL,
last_worker_time bigint NULL,
min_worker_time bigint NULL,
max_worker_time bigint NULL,
total_physical_reads bigint NULL,
last_physical_reads bigint NULL,
min_physical_reads bigint NULL,
max_physical_reads bigint NULL,
total_logical_writes bigint NULL,
last_logical_writes bigint NULL,
min_logical_writes bigint NULL,
max_logical_writes bigint NULL,
total_logical_reads bigint NULL,
last_logical_reads bigint NULL,
min_logical_reads bigint NULL,
max_logical_reads bigint NULL,
total_clr_time bigint NULL,
last_clr_time bigint NULL,
min_clr_time bigint NULL,
max_clr_time bigint NULL,
total_elapsed_time bigint NULL,
last_elapsed_time bigint NULL,
min_elapsed_time bigint NULL,
max_elapsed_time bigint NULL,
total_rows bigint NULL,
last_rows bigint NULL,
min_rows bigint NULL,
max_rows bigint NULL,
CONSTRAINT pk_factQueryStats PRIMARY KEY CLUSTERED (ServerID,QueryID,DateID,TimeID)
);

To complete the setup we have the table type and stored procedure that inserts into the fact table:

-- Drop the stored procedure that use the table valued parameter
IF OBJECT_ID('dbo.procInsertQueryStats','P') IS NOT NULL DROP PROCEDURE dbo.procInsertQueryStats;
GO

-- Table type for table valued parameter
IF EXISTS (SELECT * FROM sys.types WHERE name = 'typeQueryStats') DROP TYPE dbo.typeQueryStats;
GO

CREATE TYPE dbo.typeQueryStats AS TABLE
(
ServerName sysname NOT NULL,
SampleDateTime datetime NOT NULL,
query_hash binary(8) NOT NULL,
statement_start_offset int NOT NULL,
statement_end_offset int NOT NULL,
query_hash_count int NULL,
plan_generation_num bigint NULL,
creation_time datetime NULL,
last_execution_time datetime NULL,
execution_count bigint NULL,
total_worker_time bigint NULL,
last_worker_time bigint NULL,
min_worker_time bigint NULL,
max_worker_time bigint NULL,
total_physical_reads bigint NULL,
last_physical_reads bigint NULL,
min_physical_reads bigint NULL,
max_physical_reads bigint NULL,
total_logical_writes bigint NULL,
last_logical_writes bigint NULL,
min_logical_writes bigint NULL,
max_logical_writes bigint NULL,
total_logical_reads bigint NULL,
last_logical_reads bigint NULL,
min_logical_reads bigint NULL,
max_logical_reads bigint NULL,
total_clr_time bigint NULL,
last_clr_time bigint NULL,
min_clr_time bigint NULL,
max_clr_time bigint NULL,
total_elapsed_time bigint NULL,
last_elapsed_time bigint NULL,
min_elapsed_time bigint NULL,
max_elapsed_time bigint NULL,
total_rows bigint NULL,
last_rows bigint NULL,
min_rows bigint NULL,
max_rows bigint NULL
);
GO

CREATE PROCEDURE dbo.procInsertQueryStats
@NearestSeconds int,
@tblStats typeQueryStats READONLY
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ServerID int;
DECLARE @ServerName sysname;
DECLARE @SampleDateTime datetime;
DECLARE @QueryStatsLastFetch datetime;
DECLARE @DateID int;
DECLARE @TimeID int;

-- Summary dimension data from stats table
SELECT TOP 1
@ServerID = ds.ServerID,
@ServerName = ds.ServerName,
@SampleDateTime = s.SampleDateTime,
@QueryStatsLastFetch = s.last_execution_time
FROM @tblStats s
INNER JOIN dbo.dimServer ds
ON ds.ServerName = s.ServerName
ORDER BY last_execution_time DESC;

-- Normalise the sample time - round off to nearest seconds
SET @SampleDateTime = DATEADD(second,@NearestSeconds*(DATEPART(second,@SampleDateTime)/@NearestSeconds),CONVERT(varchar(40),@SampleDateTime,100));

-- Date and time IDs
SELECT @DateID = DateID
FROM dbo.dimDate
WHERE DateValue = CAST(@SampleDateTime AS date);

SELECT @TimeID = TimeID
FROM dbo.dimTime
WHERE TimeValue = CAST(@SampleDateTime AS time);

-- Insert queries that are new since last collection
INSERT INTO dimQuery(ServerID,query_hash,statement_start_offset,statement_end_offset)
SELECT @ServerID,query_hash,statement_start_offset,statement_end_offset
FROM @tblStats s
WHERE NOT EXISTS (
SELECT * FROM dimQuery dq
WHERE dq.ServerID = @ServerID
AND dq.query_hash = s.query_hash
AND dq.statement_start_offset = s.statement_start_offset
AND dq.statement_end_offset = s.statement_end_offset
);

INSERT INTO dbo.factQueryStats
SELECT
@ServerID,
@DateID,
@TimeID,
dq.QueryID,
s.query_hash_count,
s.plan_generation_num,
s.creation_time,
s.last_execution_time,
s.execution_count,
s.total_worker_time,
s.last_worker_time,
s.min_worker_time,
s.max_worker_time,
s.total_physical_reads,
s.last_physical_reads,
s.min_physical_reads,
s.max_physical_reads,
s.total_logical_writes,
s.last_logical_writes,
s.min_logical_writes,
s.max_logical_writes,
s.total_logical_reads,
s.last_logical_reads,
s.min_logical_reads,
s.max_logical_reads,
s.total_clr_time,
s.last_clr_time,
s.min_clr_time,
s.max_clr_time,
s.total_elapsed_time,
s.last_elapsed_time,
s.min_elapsed_time,
s.max_elapsed_time,
s.total_rows,
s.last_rows,
s.min_rows,
s.max_rows
FROM @tblStats s
INNER JOIN dbo.dimQuery dq
ON dq.ServerID = @ServerID
AND dq.query_hash = s.query_hash
AND dq.statement_start_offset = s.statement_start_offset
AND dq.statement_end_offset = s.statement_end_offset

-- Update the last collection time
EXEC dbo.procServerQueryStatsLastFetch 'SET',@ServerName,@QueryStatsLastFetch OUTPUT

END;
GO

Similarly to the case for wait, latch and spinlock statistics, there are many ways by which we could populate the fact table. A Powershell job step using the following script is one possibility:

# Query stats

# Get last collection datetime from data warehouse server
$targetserver = "DWHSRV"
$dbname = "DBAdwh"
$procname = "dbo.procServerQueryStatsLastFetch"
$getset = "GET"
$servername = "TARGETSRV"
[System.DateTime]$querystatslastfetch = "01 January 1990"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=$dbname;Integrated Security=SSPI;")
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($procname,$conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@GetSet",$getset) | Out-Null
$cmd.Parameters.Add("@ServerName",$servername) | Out-Null
$cmd.Parameters.Add("@QueryStatsLastFetch",$querystatslastfetch) | Out-Null
$cmd.Parameters["@QueryStatsLastFetch"].Direction = [System.Data.ParameterDirection]::Output
$cmd.ExecuteNonQuery() | Out-Null
[System.DateTime]$querystatslastfetch = $cmd.Parameters["@QueryStatsLastFetch"].Value
$conn.Close()
$conn.Dispose()

# Fetch stats from target server
$targetserver = "TARGETSRV"
$sql = @"
EXEC sp_executesql
N'SELECT CAST(SERVERPROPERTY(''ServerName'') AS sysname) AS ServerName,GETDATE() AS SampleDateTime,

query_hash,
statement_start_offset,
statement_end_offset,
COUNT(*) AS query_hash_count,

MAX(plan_generation_num) as plan_generation_num,
MAX(creation_time) as creation_time,
MAX(last_execution_time) as last_execution_time,
SUM(execution_count) as execution_count,

SUM(total_worker_time) as total_worker_time,
MAX(last_worker_time) as last_worker_time,
MIN(min_worker_time) as min_worker_time,
MAX(max_worker_time) as max_worker_time,

SUM(total_physical_reads) as total_physical_reads,
MAX(last_physical_reads) as last_physical_reads,
MIN(min_physical_reads) as min_physical_reads,
MAX(max_physical_reads) as max_physical_reads,

SUM(total_logical_writes) as total_logical_writes,
MAX(last_logical_writes) as last_logical_writes,
MIN(min_logical_writes) as min_logical_writes,
MAX(max_logical_writes) as max_logical_writes,

SUM(total_logical_reads) as total_logical_reads,
MAX(last_logical_reads) as last_logical_reads,
MIN(min_logical_reads) as min_logical_reads,
MAX(max_logical_reads) as max_logical_reads,

SUM(total_clr_time) as total_clr_time,
MAX(last_clr_time) as last_clr_time,
MIN(min_clr_time) as min_clr_time,
MAX(max_clr_time) as max_clr_time,

SUM(total_elapsed_time) as total_elapsed_time,
MAX(last_elapsed_time) as last_elapsed_time,
MIN(min_elapsed_time) as min_elapsed_time,
MAX(max_elapsed_time) as max_elapsed_time,

0,
0,
0,
0

FROM sys.dm_exec_query_stats
WHERE last_execution_time > @querystatslastfetch

GROUP BY
query_hash,
statement_start_offset,
statement_end_offset',
N'@querystatslastfetch datetime',
'$querystatslastfetch'
"@

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=master;Integrated Security=SSPI;")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql,$conn)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$conn.Dispose()

# Save results to data warehouse
$targetserver = "DWHSRV"
$dbname = "DBAdwh"
$procname = "dbo.procInsertQueryStats"
$nearestseconds = 60

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=$dbname;Integrated Security=SSPI;")
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($procname,$conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@NearestSeconds",$nearestseconds) | Out-Null
$cmd.Parameters.Add("@tblStats",$dataset.tables[0]) | Out-Null
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
$conn.Dispose()

Notice in the sys.dm_exec_query_stats prepared statement I’m setting the total_rows, last_rows, min_rows,max_rows columns to zero. These columns are only available in SQL 2008 R2 and higher. The code can be changed to dynamically include or exclude these columns based on the target server being examined.

For a busy system a potentially large number of rows will be returned for each collection. It is important to put in place a purge routine (scheduled job) for the removal of historic data. The retention period can be tailored for each target server by mapping this attribute to a new column in the dimServer table. The following script takes a simpler, ‘one retention period for all servers’ approach:


SET NOCOUNT ON

DECLARE @RetainDays int
SET @RetainDays = 7

DECLARE @MinDateID int

SET @MinDateID = CONVERT(varchar(8),DATEADD(day,-1*@RetainDays,GETDATE()),112)

SELECT 'Remove statistics less than or equal to ',@MinDateID

SELECT 'Deleting historic QueryStats...'

WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (1000)
FROM dbo.factQueryStats
WHERE DateID <= @MinDateID
END

SELECT 'Deleting Queries which have no QueryStats...'

WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (1000) q
FROM dbo.dimQuery q
WHERE NOT EXISTS (
SELECT *
FROM dbo.factQueryStats qs
WHERE qs.QueryID = q.QueryID
)
END

In terms of presentation of the collected data, Powerpivot once again is the best tool. All tables with the exception of the dimQuery are imported into Powerpivot as tables. The varbinary query_hash column in dimQuery generates a data type error if the import table option is chosen. To overcome this create the following view in the data warehouse and import the data through it:


CREATE VIEW dbo.vwdimQuery
AS
SELECT
QueryID,
ServerID,
master.sys.fn_varbintohexstr(query_hash) AS query_hash,
statement_start_offset,
statement_end_offset
FROM dbo.dimQuery

After creating the appropriate relationships between the fact and dimension ID columns the final data model should appears as this:

Below I chart the historic execution count. We can see it increasing for QueryID 14. Note, I stopped my SQL Server at about 13:25 and restarted at about 19:30. The DMV statistics are reset when SQL Server is restarted:

As mentioned early, to minimise the impact on the target server the collection process does not fetch the actual query text. Once you’ve identified a query of interest you use its QueryID to fetch the query_hash from dimQuery. You then examine the target server’s DMV to get the full query details (the text, the plan, the plan attributes etc).

If you’re only interested in statistics at the procedure level and not at the individual statement level then a similar collection and analysis based on sys.dm_exec_procedure_stats can be performed.

This simple approach to collecting and analysing query statistics will reveal interesting details about your SQL Server workloads. Regular patterns will emerge. Together with the wait, latch and spinlock analysis you should now have a complete picture of your SQL Servers.

Leave a comment

Analysing wait type, latch and spinlock statistics

The previous blog post outlined an approach for collecting wait type, latch and spinlock statistics. This post shows how the collected statistics can be analysed and graphed.

SQL Server retains cumulative figures for waiting task counts and wait times. These figures are reset to zero when SQL Server is restarted or when the DBCC SQLPERF(‘sys.dm_os_xxx_stats’,CLEAR) command is run. These raw cumulative figures can be easily graphed. You’ll see steady increases in most of these as well as sharp increases when resource issues arise. As well as cumulative figures we’d also like to see absolute values for waiting tasks and waiting times. These can be obtained by calculating the delta of the cumulative figures for consecutive sampling times. Using the factWaitStats table from the previous post as an example we could using the SQL Server 2012 LAG function to obtain the deltas:

SELECT *,
waiting_tasks_count - LAG(waiting_tasks_count,1,0) OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,TimeID) AS delta_waiting_tasks_count,
wait_time_ms - LAG(wait_time_ms,1,0) OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,TimeID) AS delta_wait_time_ms,
max_wait_time_ms - LAG(max_wait_time_ms,1,0) OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,TimeID) AS delta_max_wait_time_ms,
signal_wait_time_ms - LAG(signal_wait_time_ms,1,0) OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,TimeID) AS delta_signal_wait_time_ms,
TimeID - LAG(TimeID,1,0) OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,TimeID) AS delta_sample_time_sec
FROM dbo.factWaitStats

In versions prior to SQL 2012 you could use CTE’s, views, subqueries, CROSS APPLYs to obtain the deltas.

Having obtained the deltas you can use the relationships between the dimension tables and the fact tables to graph the figures that you are interested in.

Powerpivot is a wonderful analysis tool and can be used to great effect here. You’d import the dimServer, dimDate, dimTime and dimWaitType dimension tables. You’d set the date format of the dimTime TimeValue column to HH:MM (so that you don’t see today’s date appearing along with the time). You’d import the factWaitStats data using the SQL 2012 LAG statement above as the source SQL query. If you’re not using SQL 2012 you’d use the following SQL query to import factWaitStats data…

-- If you swap rownum and partitionnum in the SELECT column list you incur a SORT operation
-- in the query plan
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ServerID,WaitTypeID ORDER BY DateID,timeID) AS rownum,
DENSE_RANK() OVER (ORDER BY ServerID,WaitTypeID) AS partitionnum
FROM dbo.factWaitStats;

…and then add the following calculated columns to derive the deltas:

delta_waiting_tasks_count:
=IF([rownum]=1,0,[waiting_tasks_count]-MAXX(FILTER(factWaitStats,[partitionnum] = EARLIER([partitionnum]) && [rownum]=EARLIER([rownum]) - 1),[waiting_tasks_count]))

delta_wait_time_ms:
=IF([rownum]=1,0,[wait_time_ms]-MAXX(FILTER(factWaitStats,[partitionnum] = EARLIER([partitionnum]) && [rownum]=EARLIER([rownum]) - 1),[wait_time_ms]))

delta_max_wait_time_ms:
=IF([rownum]=1,0,[max_wait_time_ms]-MAXX(FILTER(factWaitStats,[partitionnum] = EARLIER([partitionnum]) && [rownum]=EARLIER([rownum]) - 1),[max_wait_time_ms]))

delta_signal_wait_time_ms:
=IF([rownum]=1,0,[signal_wait_time_ms]-MAXX(FILTER(factWaitStats,[partitionnum] = EARLIER([partitionnum]) && [rownum]=EARLIER([rownum]) - 1),[signal_wait_time_ms]))

delta_sample_time_sec:
=IF([rownum]=1,0,[TimeID]-MAXX(FILTER(factWaitStats,[partitionnum] = EARLIER([partitionnum]) && [rownum]=EARLIER([rownum]) - 1),[TimeID]))

I won’t go into the details of these DAX statements but you can see they are all of the same form. The only difference in them is one column name. The single partitionnum column saves us from having to type [ServerID]=EARLIER([ServerID]) && [WaitTypeID]=EARLIER([WaitTypeID]) in each definition. In other words, we import partitionnum using our source SQL statement and trade off some very minor storage cost for code simplification.

You then define the relationships between factWaitStats and dimServer, dimDate, dimTime and dimWaitType across ServerID, DateID, TimeID and WaitTypeID respectively. These are prerequisites for charting the data. The final Powerpivot worksheet should look something like this:

You can then use a pivot table and chart to graph the data. Here I’ve selected a day and am charting my WRITELOG waits:

In my case the statistics are read every 60 seconds so the deltas are relative to this sampling interval. You could use calculated measures in the pivot tables and charts to account for the sampling interval.

All sorts of simple dashboards could be created using the approach listed in this post.

Collecting wait type, latch and spinlock statistics and analysing them in this way gives you a true picture of the workloads your SQL Servers have to deal with and the resource limitations they face.

Leave a comment

Collecting wait type, latch and spinlock statistics

Collecting and charting wait type, latch and spinlock statistics gives you a true picture of how busy your SQL Servers are and the resource limitations they face. Such statistics will either verify or contradict an application owner’s view of their SQL Server. For example, blocking is forecast to be high during certain times in the morning but now you are observing blocking running into the afternoon. Similarly, you know CPU and writes to the transaction log can be high in the early hours of the morning but now you are observing them to be high up to mid morning. The wait type, latch and spinlock statistics, if gathered historically and analysed correctly, provide a quick and easy way to confirm and compare performance.

There are many commercial products available which collect wait type, latch and spinlock statistics. Collecting these statistics using your own code is fairly straightforward. What follows is one possible approach.

We can make use of a centralised data warehouse for collecting statistics. The dimension tables will hold the static attributes – server names, dates, times, wait type names, latch class names, spinlock names. The fact tables will hold the measures – waiting task counts, wait times etc. The largest tables in terms of rows counts will be the fact tables. Not having repeated string name columns in these fact tables will result in large space savings as our history builds up.

To keep the statistics collection code as simple as possible it’s best to pre-populate the dimension tables rather than introduce convoluted ‘if not exists then insert else fetch existing id’ type code. Here are the dimension tables with the code that pre-populates them:

-- Server table
IF OBJECT_ID('dbo.dimServer','U') IS NOT NULL DROP TABLE dbo.dimServer;

CREATE TABLE dbo.dimServer
(
ServerID int NOT NULL IDENTITY(1,1),
ServerName sysname NOT NULL,
CONSTRAINT pk_dimServer PRIMARY KEY CLUSTERED (ServerID),
CONSTRAINT uq_dimServer UNIQUE NONCLUSTERED (ServerName)
);

-- Pre-populate with your list of servers
INSERT INTO dbo.dimServer (ServerName)
SELECT CAST(SERVERPROPERTY('ServerName') AS sysname);
-- Date table
IF OBJECT_ID('dbo.dimDate','U') IS NOT NULL DROP TABLE dbo.dimDate;

CREATE TABLE dbo.dimDate
(
DateID int NOT NULL,
DateValue date NOT NULL ,
[Year] int NOT NULL,
Quarter tinyint NOT NULL,
[Month] tinyint NOT NULL,
[Day] tinyint NOT NULL,
DayOfYear int NOT NULL,
MonthName varchar(9) NOT NULL,
DayName varchar(9) NOT NULL,
CONSTRAINT pk_dimDate PRIMARY KEY CLUSTERED (DateID),
CONSTRAINT uq_dimDate UNIQUE NONCLUSTERED (DateValue)
);

SET NOCOUNT ON;

DECLARE @id int;
SET @id = 0;

DECLARE @thisdate date;

-- Pre-populate with 90 days from today
-- DateID is integer of the form YYYYMMDD
WHILE @id < 90
BEGIN
SET @thisdate = DATEADD(day,@id,GETDATE());

INSERT INTO dbo.dimDate (DateID,DateValue,[Year],Quarter,[Month],[Day],DayOfYear,MonthName,DayName)
SELECT CONVERT(varchar(8),@thisdate,112),@thisdate,YEAR(@thisdate),DATEPART(quarter,@thisdate),MONTH(@thisdate),
DAY(@thisdate),DATEPART(dayofyear,@thisdate),DATENAME(month,@thisdate),DATENAME(weekday,@thisdate);

SELECT @id = @id + 1;
END;

SET NOCOUNT OFF;
-- Time table
IF OBJECT_ID('dbo.dimTime','U') IS NOT NULL DROP TABLE dbo.dimTime;

CREATE TABLE dbo.dimTime
(
TimeID int NOT NULL,
TimeValue time NOT NULL,
[Hour] tinyint NOT NULL,
[Minute] tinyint NOT NULL,
[Second] tinyint NOT NULL,
CONSTRAINT pk_dimTime PRIMARY KEY CLUSTERED (TimeID),
CONSTRAINT uq_dimTime UNIQUE NONCLUSTERED (TimeValue)
);

SET NOCOUNT ON;

DECLARE @TimeValue time;
SET @TimeValue = '00:00:00';

DECLARE @timeid int;
SET @timeid = 0;

DECLARE @thistime time;

BEGIN TRAN;

-- TimeID is incrementing seconds in a day - 0 to 24*60*60 - 1
WHILE @timeid < 24*60*60
BEGIN
SET @thistime = DATEADD(second,@timeid,@TimeValue)

INSERT INTO dbo.dimTime (TimeID,TimeValue,Hour,Minute,Second)
SELECT @timeid,@thistime,DATEPART(Hour,@thistime),DATEPART(Minute,@thistime),DATEPART(Second,@thistime)

SELECT @timeid = @timeid + 1
END;

COMMIT TRAN;

SET NOCOUNT OFF;
-- Wait table
IF OBJECT_ID('dbo.dimWaitType','U') IS NOT NULL DROP TABLE dbo.dimWaitType;

CREATE TABLE dbo.dimWaitType
(
WaitTypeID int NOT NULL IDENTITY(1,1),
WaitTypeName nvarchar(60) NOT NULL,
SQLVersion numeric(4,2) NOT NULL,
CONSTRAINT pk_dimWaitType PRIMARY KEY CLUSTERED (WaitTypeID),
CONSTRAINT uq_dimWaitType UNIQUE NONCLUSTERED (WaitTypeName)
);

-- Pre-populate with wait_type names from the different versions of SQL Server at your site.
-- Run the following against a SQL 2012, a SQL 2008 R2, a SQL 2008 and a SQL 2005 changing the 'SET @sqlversion' to 11.00, 10.50, 10.00 and 9.00 respectively
-- Run the resulting SQL string against the data warehouse database to populate the dimension table
SET NOCOUNT ON

DECLARE @sqlversion char(5)
SET @sqlversion = '11.00'

DECLARE @sqlstring TABLE (id int IDENTITY, [/*sqlstring*/] nvarchar(1000))

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N'INSERT INTO dbo.dimWaitType (WaitTypeName,SQLVersion) SELECT wait_type,' + @sqlversion + ' FROM (VALUES'

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N',(''' + wait_type + N''')'
FROM sys.dm_os_wait_stats ws

-- Remove leading comma from first VALUES row constructor
UPDATE @sqlstring
SET [/*sqlstring*/] = REPLACE([/*sqlstring*/],N',(',N'(')
WHERE id = 2

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N') AS x (wait_type) WHERE NOT EXISTS (SELECT * FROM dbo.dimWaitType dw WHERE dw.WaitTypeName = x.wait_type)'

SELECT [/*sqlstring*/] FROM @sqlstring ORDER BY id
-- Latch table
IF OBJECT_ID('dbo.dimLatchClass','U') IS NOT NULL DROP TABLE dbo.dimLatchClass;

CREATE TABLE dbo.dimLatchClass
(
LatchClassID int NOT NULL IDENTITY(1,1),
LatchClassName nvarchar(60) NOT NULL,
SQLVersion numeric(4,2) NOT NULL,
CONSTRAINT pk_dimLatchClass PRIMARY KEY CLUSTERED (LatchClassID),
CONSTRAINT uq_dimLatchClass UNIQUE NONCLUSTERED (LatchClassName)
);

-- Pre-populate with latch_class names from the different versions of SQL Server at your site.
-- Run the following against a SQL 2012, a SQL 2008 R2, a SQL 2008 and a SQL 2005 changing the 'SET @sqlversion' to 11.00, 10.50, 10.00 and 9.00 respectively
-- Run the resulting SQL string against the data warehouse database to populate the dimension table
SET NOCOUNT ON

DECLARE @sqlversion char(5)
SET @sqlversion = '11.00'

DECLARE @sqlstring TABLE (id int IDENTITY, [/*sqlstring*/] nvarchar(1000))

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N'INSERT INTO dbo.dimLatchClass (LatchClassName,SQLVersion) SELECT latch_class,' + @sqlversion + ' FROM (VALUES'

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N',(''' + latch_class + N''')'
FROM sys.dm_os_latch_stats ws

-- Remove leading comma from first VALUES row constructor
UPDATE @sqlstring
SET [/*sqlstring*/] = REPLACE([/*sqlstring*/],N',(',N'(')
WHERE id = 2

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N') AS x (latch_class) WHERE NOT EXISTS (SELECT * FROM dbo.dimLatchClass dl WHERE dl.LatchClassName = x.latch_class)'

SELECT [/*sqlstring*/] FROM @sqlstring ORDER BY id
-- Spinlock table
IF OBJECT_ID('dbo.dimSpinLock','U') IS NOT NULL DROP TABLE dbo.dimSpinLock;

CREATE TABLE dbo.dimSpinLock
(
SpinLockID int NOT NULL IDENTITY(1,1),
SpinLockName nvarchar(256) NOT NULL,
SQLVersion numeric(4,2) NOT NULL,
CONSTRAINT pk_dimSpinLock PRIMARY KEY CLUSTERED (SpinLockID),
CONSTRAINT uq_dimSpinLock UNIQUE NONCLUSTERED (SpinLockName)
);

-- Pre-populate with spinlock names from the different versions of SQL Server at your site.
-- Run the following against a SQL 2012, a SQL 2008 R2, a SQL 2008 and a SQL 2005 changing the 'SET @sqlversion' to 11.00, 10.50, 10.00 and 9.00 respectively
-- Run the resulting SQL string against the data warehouse database to populate the dimension table
-- For SQL 2005 no DMV. Instead, use DBCC SQLPERF('spinlockstats')
SET NOCOUNT ON

DECLARE @sqlversion char(5)
SET @sqlversion = '11.00'

DECLARE @sqlstring TABLE (id int IDENTITY, [/*sqlstring*/] nvarchar(1000))

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N'INSERT INTO dbo.dimSpinLock (SpinLockName,SQLVersion) SELECT name,' + @sqlversion + ' FROM (VALUES'

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N',(''' + name + N''')'
FROM sys.dm_os_spinlock_stats ws

-- Remove leading comma from first VALUES row constructor
UPDATE @sqlstring
SET [/*sqlstring*/] = REPLACE([/*sqlstring*/],N',(',N'(')
WHERE id = 2

INSERT INTO @sqlstring([/*sqlstring*/])
SELECT N') AS x (name) WHERE NOT EXISTS (SELECT * FROM dbo.dimSpinLock ds WHERE ds.SpinLockName = x.name)'

SELECT [/*sqlstring*/] FROM @sqlstring ORDER BY id

Here are the fact tables:

-- Wait stats
IF OBJECT_ID('dbo.factWaitStats','U') IS NOT NULL DROP TABLE dbo.factWaitStats

CREATE TABLE dbo.factWaitStats
(
ServerID int NOT NULL,
DateID int NOT NULL,
TimeID int NOT NULL,
WaitTypeID int NOT NULL,
waiting_tasks_count bigint NOT NULL,
wait_time_ms bigint NOT NULL,
max_wait_time_ms bigint NOT NULL,
signal_wait_time_ms bigint NOT NULL,
CONSTRAINT pk_factWaitStats PRIMARY KEY (ServerID,WaitTypeID,DateID,TimeID)
);

-- Latch stats
IF OBJECT_ID('dbo.factLatchStats','U') IS NOT NULL DROP TABLE dbo.factLatchStats

CREATE TABLE dbo.factLatchStats
(
ServerID int NOT NULL,
DateID int NOT NULL,
TimeID int NOT NULL,
LatchClassID int NOT NULL,
waiting_requests_count bigint NOT NULL,
wait_time_ms bigint NOT NULL,
max_wait_time_ms bigint NOT NULL,
CONSTRAINT pk_factLatchStats PRIMARY KEY (ServerID,LatchClassID,DateID,TimeID)
);

-- Spinlock stats
IF OBJECT_ID('dbo.factSpinLockStats','U') IS NOT NULL DROP TABLE dbo.factSpinLockStats

CREATE TABLE dbo.factSpinLockStats
(
ServerID int NOT NULL,
DateID int NOT NULL,
TimeID int NOT NULL,
SpinLockID int NOT NULL,
collisions bigint NOT NULL,
spins bigint NOT NULL,
spins_per_collision real NOT NULL,
sleep_time bigint NOT NULL,
backoffs int NOT NULL,
CONSTRAINT pk_factSpinLockStats PRIMARY KEY (ServerID,SpinLockID,DateID,TimeID)
);

There statistics collection jobs should be run on the data warehouse SQL Server. The result sets required from each target SQL Server are as follows:

SELECT
CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,
GETDATE() AS SampleDateTime,
*
FROM sys.dm_os_wait_stats;

SELECT
CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,
GETDATE() AS SampleDateTime,
*
FROM sys.dm_os_latch_stats;

SELECT
CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,
GETDATE() AS SampleDateTime,
*
FROM sys.dm_os_spinlock_stats;

These queries can be run against the target SQL Servers in number of ways. For example, via T-SQL job steps using linked servers. Another way is via Powershell job steps. The requirements for these are as follows (these objects reside in the data warehouse database):

-- Drop the stored procedures that use the table valued parameters
IF OBJECT_ID('dbo.procInsertWaitStats','P') IS NOT NULL DROP PROCEDURE dbo.procInsertWaitStats;
GO

IF OBJECT_ID('dbo.procInsertLatchStats','P') IS NOT NULL DROP PROCEDURE dbo.procInsertLatchStats;
GO

IF OBJECT_ID('dbo.procInsertSpinLockStats','P') IS NOT NULL DROP PROCEDURE dbo.procInsertSpinLockStats;
GO

-- Table types for table valued parameters
IF EXISTS (SELECT * FROM sys.types WHERE name = 'typeWaitStats') DROP TYPE dbo.typeWaitStats;
GO

CREATE TYPE dbo.typeWaitStats AS TABLE
(
ServerName sysname NOT NULL,
SampleDateTime datetime NOT NULL,
wait_type nvarchar(60) NOT NULL,
waiting_tasks_count bigint NOT NULL,
wait_time_ms bigint NOT NULL,
max_wait_time_ms bigint NOT NULL,
signal_wait_time_ms bigint NOT NULL
);
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'typeLatchStats') DROP TYPE dbo.typeLatchStats;
GO

CREATE TYPE dbo.typeLatchStats AS TABLE
(
ServerName sysname NOT NULL,
SampleDateTime datetime NOT NULL,
latch_class nvarchar(60) NOT NULL,
waiting_requests_count bigint NOT NULL,
wait_time_ms bigint NOT NULL,
max_wait_time_ms bigint NOT NULL
);
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'typeSpinLockStats') DROP TYPE dbo.typeSpinLockStats;
GO

CREATE TYPE dbo.typeSpinLockStats AS TABLE
(
ServerName sysname NOT NULL,
SampleDateTime datetime NOT NULL,
name nvarchar(256) NOT NULL,
collisions bigint NOT NULL,
spins bigint NOT NULL,
spins_per_collision real NOT NULL,
sleep_time bigint NOT NULL,
backoffs int NOT NULL
);
GO

-- The insert fact stored procedures which use table valued parameters

CREATE PROCEDURE dbo.procInsertWaitStats
@NearestSeconds int,
@tblStats typeWaitStats READONLY
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SampleDateTime datetime;

-- Normalise the sample time - round off to nearest seconds
SELECT TOP 1 @SampleDateTime = SampleDateTime
FROM @tblStats;

SET @SampleDateTime = DATEADD(second,@NearestSeconds*(DATEPART(second,@SampleDateTime)/@NearestSeconds),CONVERT(varchar(40),@SampleDateTime,100));

INSERT INTO dbo.factWaitStats
SELECT ds.ServerID,dd.DateID,dt.TimeID,dw.WaitTypeID,s.waiting_tasks_count,s.wait_time_ms,s.max_wait_time_ms,s.signal_wait_time_ms
FROM @tblStats s
INNER JOIN dbo.dimServer ds
ON ds.ServerName = s.ServerName
INNER JOIN dbo.dimWaitType dw
ON dw.WaitTypeName = s.wait_type
INNER JOIN dbo.dimDate dd
ON dd.DateValue = CAST(@SampleDateTime AS date)
INNER JOIN dbo.dimTime dt
ON dt.TimeValue = CAST(@SampleDateTime AS time);

END;
GO

CREATE PROCEDURE dbo.procInsertLatchStats
@NearestSeconds int,
@tblStats typeLatchStats READONLY
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SampleDateTime datetime;

-- Normalise the sample time - round off to nearest seconds
SELECT TOP 1 @SampleDateTime = SampleDateTime
FROM @tblStats;

SET @SampleDateTime = DATEADD(second,@NearestSeconds*(DATEPART(second,@SampleDateTime)/@NearestSeconds),CONVERT(varchar(40),@SampleDateTime,100));

INSERT INTO dbo.factLatchStats
SELECT ds.ServerID,dd.DateID,dt.TimeID,dl.LatchClassID,s.waiting_requests_count,s.wait_time_ms,s.max_wait_time_ms
FROM @tblStats s
INNER JOIN dbo.dimServer ds
ON ds.ServerName = s.ServerName
INNER JOIN dbo.dimLatchClass dl
ON dl.LatchClassName = s.latch_class
INNER JOIN dbo.dimDate dd
ON dd.DateValue = CAST(@SampleDateTime AS date)
INNER JOIN dbo.dimTime dt
ON dt.TimeValue = CAST(@SampleDateTime AS time);

END;
GO

CREATE PROCEDURE dbo.procInsertSpinLockStats
@NearestSeconds int,
@tblStats typeSpinLockStats READONLY
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SampleDateTime datetime;

-- Normalise the sample time - round off to nearest seconds
SELECT TOP 1 @SampleDateTime = SampleDateTime
FROM @tblStats;

SET @SampleDateTime = DATEADD(second,@NearestSeconds*(DATEPART(second,@SampleDateTime)/@NearestSeconds),CONVERT(varchar(40),@SampleDateTime,100));

INSERT INTO dbo.factSpinLockStats
SELECT ds.ServerID,dd.DateID,dt.TimeID,dl.SpinLockID,s.collisions,s.spins,s.spins_per_collision,s.sleep_time,s.backoffs
FROM @tblStats s
INNER JOIN dbo.dimServer ds
ON ds.ServerName = s.ServerName
INNER JOIN dbo.dimSpinLock dl
ON dl.SpinLockName = s.name
INNER JOIN dbo.dimDate dd
ON dd.DateValue = CAST(@SampleDateTime AS date)
INNER JOIN dbo.dimTime dt
ON dt.TimeValue = CAST(@SampleDateTime AS time);

END;
GO

The purpose of the @NearestSeconds parameter is to round the sampling time off to the nearest x seconds. For example, you could set this to a value of 300 to ensure all your sample time values fall on 5 minute intervals. You’ll see this parameter being set to 60 in the Powershell job step script:


# Wait stats

# Fetch stats from target server
$targetserver = "TARGETSRV"
$sql = "SELECT CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,GETDATE() AS SampleDateTime,* FROM sys.dm_os_wait_stats"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=master;Integrated Security=SSPI;")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql,$conn)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$conn.Dispose()

# Save results to data warehouse
$targetserver = "DWHSRV"
$dbname = "DBAdwh"
$procname = "dbo.procInsertWaitStats"
$nearestseconds = 60

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=$dbname;Integrated Security=SSPI;")
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($procname,$conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@NearestSeconds",$nearestseconds) | Out-Null
$cmd.Parameters.Add("@tblStats",$dataset.tables[0]) | Out-Null
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
$conn.Dispose()

# Latch stats

# Fetch stats from target server
$targetserver = "TARGETSRV"
$sql = "SELECT CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,GETDATE() AS SampleDateTime,* FROM sys.dm_os_latch_stats"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=master;Integrated Security=SSPI;")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql,$conn)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$conn.Dispose()

# Save results to data warehouse
$targetserver = "DWHSRV"
$dbname = "DBAdwh"
$procname = "dbo.procInsertLatchStats"
$nearestseconds = 60

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=$dbname;Integrated Security=SSPI;")
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($procname,$conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@NearestSeconds",$nearestseconds) | Out-Null
$cmd.Parameters.Add("@tblStats",$dataset.tables[0]) | Out-Null
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
$conn.Dispose()

# Spinlock stats

# Fetch stats from target server
$targetserver = "TARGETSRV"
$sql = "SELECT CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName,GETDATE() AS SampleDateTime,* FROM sys.dm_os_spinlock_stats"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=master;Integrated Security=SSPI;")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql,$conn)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$conn.Dispose()

# Save results to data warehouse
$targetserver = "DWHSRV"
$dbname = "DBAdwh"
$procname = "dbo.procInsertSpinLockStats"
$nearestseconds = 60

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetserver;Initial Catalog=$dbname;Integrated Security=SSPI;")
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($procname,$conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@NearestSeconds",$nearestseconds) | Out-Null
$cmd.Parameters.Add("@tblStats",$dataset.tables[0]) | Out-Null
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
$conn.Dispose()

This script is hardcoded to collect statistics from a target server named TARGETSRV. The collected statistics are stored in the data warehouse database named DBAdwh on server DWHSRV. You can set TARGETSRV to be equal to DWHSRV. You’d then have a local collection. All time values are local to the server being sampled.

As mentioned earlier, sys.dm_os_spinlock_stats doesn’t exist in SQL 2005. The Powershell SQL string needs modifying if you’re collecting from this version. One approach would be to use INSERT INTO…EXEC DBCC SQLPERF. For example,

# SQL string change to cater for 2005

$sql=@"
CREATE TABLE #stats
(
ServerName sysname NOT NULL DEFAULT CAST(SERVERPROPERTY('ServerName') AS sysname),
SampleDateTime datetime NOT NULL DEFAULT GETDATE(),
name nvarchar(256) NOT NULL,
collisions bigint NOT NULL,
spins bigint NOT NULL,
spins_per_collision real NOT NULL,
sleep_time bigint NOT NULL,
backoffs int NOT NULL
);

INSERT INTO #stats (name,collisions,spins,spins_per_collision,sleep_time,backoffs)
EXEC sp_executesql N'DBCC SQLPERF(''spinlockstats'') WITH NO_INFOMSGS';

SELECT * FROM #stats;
"@

# The rest of the code stays the same

The code required for collection of statistics seems quite lengthy. It’s not as bad as it seems when you consider that large chunks are simple repetitions to cater for three different types of statistics.

Depending on your sampling schedule your fact tables could become quite large. The following script can be used in a scheduled job (run once a day) to purge data that is older than 7 days:


SET NOCOUNT ON

DECLARE @RetainDays int
SET @RetainDays = 7

DECLARE @MinDateID int

SET @MinDateID = CONVERT(varchar(8),DATEADD(day,-1*@RetainDays,GETDATE()),112)

SELECT 'Remove statistics less than or equal to ',@MinDateID

SELECT 'Deleting historic WaitStats...'

WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (1000)
FROM dbo.factWaitStats
WHERE DateID <= @MinDateID
END

SELECT 'Deleting historic LatchStats...'

WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (1000)
FROM dbo.factLatchStats
WHERE DateID <= @MinDateID
END

SELECT 'Deleting historic SpinLockStats...'

WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (1000)
FROM dbo.factSpinLockStats
WHERE DateID <= @MinDateID
END

The next post will show how the statistics can be analysed.

Leave a comment