Dec 172013
 

In a previous post, Collecting Historical Wait Statistics, I discussed how you can easily collect historical wait stats by using the DMV sys.dm_os_wait_stats. Well today, I’d like to cover the same concept, but this time collect historical IO file stats from the DMV, sys.dm_io_virtual_files_stats. However, I wanted to improve on the code to make it even easier to implement.

The data collection process is still implemented the same way.  First, we’ll need to create a history table to store the data. The data is stored in time slices with the cumulative values as well as the difference (TimeDiff_ms, NumOfReadsDiff, NumOfWritesDiff, etc) in those values since the last collection time.

CREATE TABLE dbo.IoVirtualFileStatsHistory(
  SqlServerStartTime DATETIME NOT NULL
 ,CollectionTime DATETIME NOT NULL
 ,TimeDiff_ms BIGINT NOT NULL
 ,DatabaseName NVARCHAR(128) NOT NULL
 ,DatabaseId SMALLINT NOT NULL
 ,FileId SMALLINT NOT NULL
 ,SampleMs INT NOT NULL
 ,SampleMsDiff INT NOT NULL
 ,NumOfReads BIGINT NOT NULL
 ,NumOfReadsDiff BIGINT NOT NULL
 ,NumOfBytesRead BIGINT NOT NULL
 ,NumOfBytesReadDiff BIGINT NOT NULL
 ,IoStallReadMs BIGINT NOT NULL
 ,IoStallReadMsDiff BIGINT NOT NULL
 ,NumOfWrites BIGINT NOT NULL
 ,NumOfWritesDiff BIGINT NOT NULL
 ,NumOfBytesWritten BIGINT NOT NULL
 ,NumOfBytesWrittenDiff BIGINT NOT NULL
 ,IoStallWriteMs BIGINT NOT NULL
 ,IoStallWriteMsDiff BIGINT NOT NULL
 ,IoStall BIGINT NOT NULL
 ,IoStallDiff BIGINT NOT NULL
 ,SizeOnDiskBytes BIGINT NOT NULL
 ,SizeOnDiskBytesDiff BIGINT NOT NULL
 ,FileHandle VARBINARY(8) NOT NULL
 ,CONSTRAINT PK_IoVirtualFileStatsHistory PRIMARY KEY CLUSTERED
  (CollectionTime,DatabaseName,DatabaseId,FileId)
 )WITH (DATA_COMPRESSION = PAGE);
GO

The next step will be to get the start time of SQL Server, so that we can compare it to the previous collection. If the dates are different, then we must take that into account when calculating the diff values. Because if SQL Server is restarted, then all values in the DMV are reset back to zero. At this point, we know the diff values are actually the same value as the current counters, because this is the first collection after a restart.

IF @CurrentSqlServerStartTime <> ISNULL(@PreviousSqlServerStartTime,0)
BEGIN
 -- If SQL started since the last collection, then insert starter values
 -- Must do DATEDIFF using seconds instead of milliseconds to avoid arithmetic overflow.
 INSERT INTO dbo.IoVirtualFileStatsHistory
 SELECT
   @CurrentSqlServerStartTime
  ,CURRENT_TIMESTAMP
  ,CONVERT(BIGINT,DATEDIFF(SS,@CurrentSqlServerStartTime,CURRENT_TIMESTAMP))*1000
  ,@DatabaseName
  ,@DatabaseId
  ,file_id
  ,sample_ms
  ,sample_ms
  ,num_of_reads
  ,num_of_reads
  ,num_of_bytes_read
  ,num_of_bytes_read
  ,io_stall_read_ms
  ,io_stall_read_ms
  ,num_of_writes
  ,num_of_writes
  ,num_of_bytes_written
  ,num_of_bytes_written
  ,io_stall_write_ms
  ,io_stall_write_ms
  ,io_stall
  ,io_stall
  ,size_on_disk_bytes
  ,size_on_disk_bytes
  ,file_handle
 FROM sys.dm_io_virtual_file_stats(@DatabaseId,NULL);
END
GO

You may notice the DATEDIFF is using “seconds” instead of “milliseconds”.  This is because DATEDIFF only returns an INT value. The largest number it can return is equal to about 24 days before it hits an arithmetic overflow error. By converting it to seconds, we can avoid that error. All of the following data collections will do a DATEDIFF using milliseconds.

If the current start time is the same as the previous collection, then we’ll grab the difference in values and insert those into the history table.

WITH CurrentIoVirtualFileStats AS
(
 SELECT
   CURRENT_TIMESTAMP AS 'CollectionTime'
  ,@DatabaseName AS 'DatabaseName'
  ,*
 FROM sys.dm_io_virtual_file_stats(@DatabaseId,NULL)
)
INSERT INTO dbo.IoVirtualFileStatsHistory
SELECT
  @CurrentSqlServerStartTime
 ,CURRENT_TIMESTAMP
 ,CONVERT(BIGINT,DATEDIFF(MS,@PreviousCollectionTime,curr.CollectionTime))
 ,@DatabaseName
 ,@DatabaseId
 ,file_id
 ,sample_ms
 ,curr.sample_ms - hist.SampleMs
 ,num_of_reads
 ,curr.num_of_reads - hist.NumOfReads
 ,num_of_bytes_read
 ,curr.num_of_bytes_read - hist.NumOfBytesRead
 ,io_stall_read_ms
 ,curr.io_stall_read_ms - hist.IoStallReadMs
 ,num_of_writes
 ,curr.num_of_writes - hist.NumOfWrites
 ,num_of_bytes_written
 ,curr.num_of_bytes_written - hist.NumOfBytesWritten
 ,io_stall_write_ms
 ,curr.io_stall_write_ms - hist.IoStallWriteMs
 ,io_stall
 ,curr.io_stall - hist.IoStall
 ,size_on_disk_bytes
 ,curr.size_on_disk_bytes - hist.SizeOnDiskBytes
 ,file_handle
FROM CurrentIoVirtualFileStats curr INNER JOIN dbo.IoVirtualFileStatsHistory hist
 ON (curr.DatabaseName = hist.DatabaseName
  AND curr.database_id = hist.DatabaseId
  AND curr.file_id = hist.FileId)
  AND hist.CollectionTime = @PreviousCollectionTime;
GO

At this point, we’re through collecting the raw data. However, as I mentioned earlier, I added a lot of functionality into this script. The script is actually a stored procedure that can run all of this code for you; including creation of the history table, data collection, historical data purging and finally reporting.

The stored procedure has 5 input parameters.

@Database – This is used to specify a single database, a list of databases, or a wildcard.

  • ‘*’ is the default value which selects all databases
  • ‘MyDatabase1’  will process only that single database
  • ‘MyDatabase1,MyDatabase2,MyDatabase3’ Comma delimited list of databases
  • ‘USER_DATABASES’ used to process all user databases
  • ‘SYSTEM_DATABASES’ used to process only the system databases.

@GenerateReport – Flag. When off, the stored procedure will collect data. When on, it will generate an aggregated report of the historical data.

@HistoryRetention – The is the number of days to keep in the history table. Default is 365.

@ExcludedDBs – Is a comma delimited list of databases to exclude from processing. It should be used when @Database is set to ‘*’.

@Debug – Flag. When on, it will output TSQL commands that being executed.

Examples:

1. Collect data for all databases.

EXEC dbo.sp_CollectIoVirtualFileStats
  @Database = '*';
GO

2. Collect data for all databases except for AdventureWorks, and output all debug commands.

EXEC dbo.sp_CollectIoVirtualFileStats
   @Database = '*'
  ,@ExcludedDBs = 'AdventureWorks'
  ,@Debug = 1;
GO

3. Output an aggregated report of data collected so far for tempdb.

EXEC dbo.sp_CollectIoVirtualFileStats
   @Database = 'tempdb'
  ,@GenerateReport = 1;
GO

The report would look like this.

Finally, you can copy this report data into Excel and generate some easy to read charts.

From the chart, we can see there was a spike in write latency between 3PM and 4PM for tempdb. If we collect this data over time and identify a similar spike each day then we’d want to investigate further to find out what is causing it. But that can only be done if you’re collecting these metrics and storing them for historical analysis. Hopefully, this stored procedure will help you be more proactive in collecting performance metrics for each of your servers.

The entire script is available on the resources page.

Dec 102013
 

As a DBA, I’m sure you’ve heard many times to always check the sys.dm_os_wait_stats DMV to help diagnose performance issues on your server. The DMV returns information about specific resources SQL Server had to wait for while processing queries. The counters in the DMV are cumulative since the last time SQL Server was started and the counters can only be reset by a service restart or by using a DBCC command. Since DMVs don’t persist their data beyond a service restart, we need to come up with a way to collect this data and be able to run trending reports over time.

Collecting the data seems easy enough by simply selecting all rows into a permanent table. However, that raw data won’t help us determine the time in which a particular wait type occurred. Think about it for a minute. If the raw data for the counters is cumulative, then how can you tell if a bunch of waits occurred within a span of a few minutes or if they occurred slowly over the past 6 months that SQL Server has been running. This is where we need to collect the data in increments.

First, we need to create a history table to store the data. The table will store the wait stat values as well as the difference (TimeDiff_ms, WaitingTasksCountDiff, WaitTimeDiff_ms, SignalWaitTimeDiff_ms) in those values between collection times.

CREATE TABLE dbo.WaitStatsHistory
(
     SqlServerStartTime DATETIME NOT NULL
    ,CollectionTime DATETIME NOT NULL
    ,TimeDiff_ms INT NOT NULL
    ,WaitType NVARCHAR(60) NOT NULL
    ,WaitingTasksCountCumulative BIGINT NOT NULL
    ,WaitingTasksCountDiff INT NOT NULL
    ,WaitTimeCumulative_ms BIGINT NOT NULL
    ,WaitTimeDiff_ms INT NOT NULL
    ,MaxWaitTime_ms BIGINT NOT NULL
    ,SignalWaitTimeCumulative_ms BIGINT NOT NULL
    ,SignalWaitTimeDiff_ms INT NOT NULL
    ,CONSTRAINT PK_WaitStatsHistory PRIMARY KEY CLUSTERED (CollectionTime, WaitType)
)WITH (DATA_COMPRESSION = PAGE);
GO

Next, we need to get a couple of timestamps when we collect each sample. The first will be the SQL Server start time. We need the SQL Server start time, so we can identify when the service was restarted.

SELECT @CurrentSqlServerStartTime = sqlserver_start_time FROM sys.dm_os_sys_info;
GO

The second set is the previous start time and previous collection time, if they exist in the history table.

SELECT
   @PreviousSqlServerStartTime = MAX(SqlServerStartTime)
  ,@PreviousCollectionTime = MAX(CollectionTime)
FROM msdb.MSDBA.WaitStatsHistory;
GO

The last timestamp is the collection time. We’ll also use this timestamp to calculate the difference in wait stat values between each collection.

SELECT GETDATE() AS 'CollectionTime',* FROM sys.dm_os_wait_stats;
GO

We need to compare the current SQL Server start time to the previous start time from the history table. If they don’t equal, then we assume the server was restarted and insert “starter” values. I call them starter values, because we just collect the current wait stat values and insert 0 for each of the diff columns.

IF @CurrentSqlServerStartTime <> ISNULL(@PreviousSqlServerStartTime,0)
BEGIN
    -- Insert starter values if SQL Server has been recently restarted
    INSERT INTO dbo.WaitStatsHistory
    SELECT
         @CurrentSqlServerStartTime
        ,GETDATE()
        ,DATEDIFF(MS,@CurrentSqlServerStartTime,GETDATE())
        ,wait_type
        ,waiting_tasks_count
        ,0
        ,wait_time_ms
        ,0
        ,max_wait_time_ms
        ,signal_wait_time_ms
        ,0
    FROM sys.dm_os_wait_stats;
END
GO

If the timestamps are the same, we will collect the current wait stats and calculate the difference (in milliseconds) in collection time as well as the difference in values.

INSERT msdb.MSDBA.WaitStatsHistory
SELECT
     @CurrentSqlServerStartTime
    ,cws.CollectionTime
    ,DATEDIFF(MS,@PreviousCollectionTime,cws.CollectionTime)
    ,cws.wait_type
    ,cws.waiting_tasks_count
    ,cws.waiting_tasks_count - hist.WaitingTasksCountCumulative
    ,cws.wait_time_ms
    ,cws.wait_time_ms - hist.WaitTimeCumulative_ms
    ,cws.max_wait_time_ms
    ,cws.signal_wait_time_ms
    ,cws.signal_wait_time_ms - hist.SignalWaitTimeCumulative_ms
FROM CurrentWaitStats cws INNER JOIN MSDBA.WaitStatsHistory hist
    ON cws.wait_type = hist.WaitType
    AND hist.CollectionTime = @PreviousCollectionTime;
GO

You could filter the collection to only specific wait stat counters that you want to track by just a where clause, but I prefer to collect them all and then filter at the reporting end.

At this point, we’re ready to schedule the job. The script could be run at any interval, but I usually leave it to collect data once a day. If I notice a spike in a specific wait stat counter, then I could easily increase the job frequency to once every few hours or even once an hour. Having those smaller, more granular data samples will allow us to isolate which time frame we need to concentrate on.

For example, if we notice the CXPACKET wait suddenly spikes when collecting the data each day, then we could schedule the collection every hour to see if it’s happening during a specific window.

SELECT * FROM msdb.MSDBA.WaitStatsHistory
WHERE WaitType = 'CXPACKET';
GO

Finally, we can use Excel to format this raw data into an easy to read chart.

From this chart, we can see at 5PM there was a spike in CXPACKET waits, but a low number of tasks that waited. In this case, I would be assume there is a single process running in parallel that caused these waits and from there I could dig further into finding the individual query.
Data compression is enabled on this table to help keep it small. It can easily turn it off for the table by removing WITH (DATA_COMPRESSION = PAGE) from the CREATE TABLE statement. However, with page compression enabled, 24 collections (one per hour) only takes up 775KB of space. Without compression, the same sample of data consumes about 2.2MB. If you plan to keep a lot of history, then it’s best to leave page compression enabled.
Hopefully, this script will help you keep track of your historical wait statistics, so you can have better knowledge of what has happened to your environment over time. The entire script is posted below. If you want to read further into what each wait statistics means, then check out Paul Randal’s article about wait stats. Additionally, if you want more info on using DMVs, check out Glenn Berry’s diagnostic queries.

/***********************************************
    Create the historical table
***********************************************/

USE msdb;
GO

-- Create the history table if it does not exist
IF OBJECT_ID('dbo.WaitStatsHistory') IS NULL
BEGIN
    CREATE TABLE dbo.WaitStatsHistory
    (
         SqlServerStartTime DATETIME NOT NULL
        ,CollectionTime DATETIME NOT NULL
        ,TimeDiff_ms INT NOT NULL
        ,WaitType NVARCHAR(60) NOT NULL
        ,WaitingTasksCountCumulative BIGINT NOT NULL
        ,WaitingTasksCountDiff INT NOT NULL
        ,WaitTimeCumulative_ms BIGINT NOT NULL
        ,WaitTimeDiff_ms INT NOT NULL
        ,MaxWaitTime_ms BIGINT NOT NULL
        ,SignalWaitTimeCumulative_ms BIGINT NOT NULL
        ,SignalWaitTimeDiff_ms INT NOT NULL
        ,CONSTRAINT PK_WaitStatsHistory PRIMARY KEY CLUSTERED (CollectionTime, WaitType)
    )WITH (DATA_COMPRESSION = PAGE);
END
GO

/***********************************************
    Schedule this section as an on-going job
***********************************************/

DECLARE
     @CurrentSqlServerStartTime DATETIME
    ,@PreviousSqlServerStartTime DATETIME
    ,@PreviousCollectionTime DATETIME;

SELECT @CurrentSqlServerStartTime = sqlserver_start_time FROM sys.dm_os_sys_info;

-- Get the last collection time
SELECT
     @PreviousSqlServerStartTime = MAX(SqlServerStartTime)
    ,@PreviousCollectionTime = MAX(CollectionTime)
FROM msdb.dbo.WaitStatsHistory;

IF @CurrentSqlServerStartTime <> ISNULL(@PreviousSqlServerStartTime,0)
BEGIN
    -- Insert starter values if SQL Server has been recently restarted
    INSERT INTO dbo.WaitStatsHistory
    SELECT
         @CurrentSqlServerStartTime
        ,GETDATE()
        ,DATEDIFF(MS,@CurrentSqlServerStartTime,GETDATE())
        ,wait_type
        ,waiting_tasks_count
        ,0
        ,wait_time_ms
        ,0
        ,max_wait_time_ms
        ,signal_wait_time_ms
        ,0
    FROM sys.dm_os_wait_stats;
END
ELSE
BEGIN
    -- Get the current wait stats
    WITH CurrentWaitStats AS
    (
        SELECT GETDATE() AS 'CollectionTime',* FROM sys.dm_os_wait_stats
    )
    -- Insert the diff values into the history table
    INSERT msdb.dbo.WaitStatsHistory
    SELECT
         @CurrentSqlServerStartTime
        ,cws.CollectionTime
        ,DATEDIFF(MS,@PreviousCollectionTime,cws.CollectionTime)
        ,cws.wait_type
        ,cws.waiting_tasks_count
        ,cws.waiting_tasks_count - hist.WaitingTasksCountCumulative
        ,cws.wait_time_ms
        ,cws.wait_time_ms - hist.WaitTimeCumulative_ms
        ,cws.max_wait_time_ms
        ,cws.signal_wait_time_ms
        ,cws.signal_wait_time_ms - hist.SignalWaitTimeCumulative_ms
    FROM CurrentWaitStats cws INNER JOIN dbo.WaitStatsHistory hist
        ON cws.wait_type = hist.WaitType
        AND hist.CollectionTime = @PreviousCollectionTime;
END
GO