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.

Advertisements
  1. Leave a comment

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

%d bloggers like this: