Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating IO performance snapshots to find SQL Server performance problems


By:   |   Last Updated: 2011-04-11   |   Comments (4)   |   Related Tips: More > Dynamic Management Views and Functions

Problem

I/O is one of the most time consuming activities in SQL Server. If you can reduce the I/O wait time then you can improve performance. This can be done with indexing and tuning queries, but I/O issues may also be at the file level. In this tip we look at how to identify specific files that consume the most I/O activity using a SQL Server DMV and creating snapshots for analysis.

Solution

The solution I suggest here is to collect I/O statistics for a certain time period and then compare the data snapshots to identify potential I/O bottlenecks. First we will collect a baseline and then once a day (or hour or any time period) collect the statistics again and check the difference between two different snapshots.

The I/O statistics will be collected from a query using sys.dm_io_virtual_file_stats and sys.master_files. The data that will be included consists of the following data columns (Table from BOL).

Column Type Description
num_of_reads bigint Number of reads issued on the file.
num_of_bytes_read bigint Total number of bytes read on this file.
io_stall_read_ms bigint Total time, in milliseconds, that the users waited for reads issued on the file.
num_of_writes bigint Number of writes made on this file.
num_of_bytes_written bigint Total number of bytes written to the file.
io_stall_write_ms bigint Total time, in milliseconds, that users waited for writes to be completed on the file.
io_stall bigint Total time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytes bigint Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.

I/O Statistics collection process

First we must create tables to store the I/O statistics. The following code will create two tables. You can put this in the master database or whatever database you prefer.

-- create a table for snapshot sequence generation
CREATE TABLE io_snapshots
(snap_id INT IDENTITY NOT NULL,
snapshot_creation_date DATETIME NOT NULL )
GO
ALTER TABLE io_snapshots ADD CONSTRAINT PK_io_snapshots PRIMARY KEY (snap_id)
GO

-- create a table for the io statistics
CREATE TABLE io_snapshots_statistics
(snap_id INT NOT NULL,
[db_id] smallint NOT NULL,
[file_id] smallint NOT NULL,
database_name SYSNAME,
physical_file_name SYSNAME,
Diff_Number_of_reads bigint,
Diff_Bytes_Read bigint,
Diff_Read_stall_time_ms bigint,
Diff_Number_of_writes bigint,
Diff_Bytes_written bigint,
Diff_Write_stall_time_ms bigint,
Diff_Read_Write_stall_ms bigint,
size_on_disk_MB bigint)
GO
ALTER TABLE io_snapshots_statistics ADD CONSTRAINT PK_io_snapshots_statistics
PRIMARY KEY (snap_id,[db_id], [file_id])
GO
ALTER TABLE io_snapshots_statistics ADD CONSTRAINT FK_io_snapshots_statistics_io_snapshots
FOREIGN KEY (snap_id) REFERENCES io_snapshots (snap_id)
GO

Second we must create the following two stored procedures.

The first SP (usp_io_vf_stats_snap) inserts a new I/O snapshot of statistics each time it is run.

-- The First Procedure.
CREATE PROC [dbo].[usp_io_vf_stats_snap]
AS
BEGIN
SET NOCOUNT ON
INSERT INTO
io_snapshots ( snapshot_creation_date) SELECT GETDATE()

INSERT INTO io_snapshots_statistics
(snap_id,
[db_id],
[file_id],
database_name ,
physical_file_name,
Diff_Number_of_reads,
Diff_Bytes_Read,
Diff_Read_stall_time_ms,
Diff_Number_of_writes,
Diff_Bytes_written,
Diff_Write_stall_time_ms,
Diff_Read_Write_stall_ms,
size_on_disk_MB)
SELECT
(SELECT MAX(snap_id) FROM io_snapshots),
db_files.database_id,
db_files.FILE_ID,
DB_NAME(db_files.database_id) AS Database_Name,
db_files.physical_name AS File_actual_name,
num_of_reads AS Number_of_reads,
num_of_bytes_read AS Bytes_Read,
io_stall_read_ms AS Read_time_stall_ms,
num_of_writes AS Number_of_writes,
num_of_bytes_written AS Bytes_written,
io_stall_write_ms AS Write_time_stall_ms,
io_stall AS Read_Write_stall_ms,
size_on_disk_bytes / POWER(1024,2) AS size_on_disk_MB
FROM
sys.dm_io_virtual_file_stats(NULL,NULL) dm_io_vf_stats ,
sys.master_files db_files
WHERE
db_files.database_id = dm_io_vf_stats.database_id
AND db_files.[file_id] = dm_io_vf_stats.[file_id];

SET NOCOUNT OFF

END
GO

The second SP (usp_compare_io_stats_snaps) shows the difference between two snapshots and can be run several ways:

  1. If you don't pass in any parameter values it will compare the last two snapshots that were created
  2. If you pass in only the starting snapshot ID then it will compare that snapshot with the last snapshot
  3. Lastly, you can pass in a starting snapshot ID and an ending snapshot ID to compare any two time periods
-- The Second Procedure.
CREATE PROC [dbo].[usp_compare_io_stats_snaps]
(@start_snap_ID INT = NULL,
@end_snap_ID INT = NULL)
AS
DECLARE
@end_snp INT
DECLARE
@start_snp INT
BEGIN
SET NOCOUNT ON

IF
(@end_snap_ID IS NULL)
SELECT @end_snp = MAX(snap_id) FROM io_snapshots
ELSE SET @end_snp = @end_snap_ID

IF (@start_snap_ID IS NULL)
SELECT @start_snp = @end_snp -1
ELSE SET @start_snp = @start_snap_ID


SELECT
CONVERT(VARCHAR(12),S.snapshot_creation_date,101) AS snapshot_creation_date,
A.database_name,
A.physical_file_name,
A.size_on_disk_MB,
A.Diff_Number_of_reads - B.Diff_Number_of_reads AS Diff_Number_of_reads,
A.Diff_Bytes_read - B.Diff_Bytes_read AS Diff_Bytes_read,
A.Diff_Read_stall_time_ms - B.Diff_Read_stall_time_ms AS Diff_Read_stall_time_ms,
A.Diff_Number_of_writes - B.Diff_Number_of_writes AS Diff_Number_of_writes,
A.Diff_Bytes_written - B.Diff_Bytes_written AS Diff_Bytes_written,
A.Diff_Write_stall_time_ms - B.Diff_Write_stall_time_ms AS Diff_Write_stall_time_ms,
A.Diff_Read_Write_stall_ms - B.Diff_Read_Write_stall_ms AS Diff_Read_Write_stall_ms ,
DATEDIFF (hh,S1.snapshot_creation_date, S.snapshot_creation_date) AS Diff_time_hours
FROM
io_snapshots S ,
io_snapshots S1,
io_snapshots_statistics A ,
io_snapshots_statistics B
WHERE
S.snap_id = @end_snp AND
S.snap_id = A.snap_id AND
B.snap_id = @start_snp AND
A.[db_id] = B.[db_id] AND
A.[file_id] = B.[file_id] AND
S1.snap_id = @start_snp AND
S1.snap_id = B.snap_id
ORDER BY
A.database_name,
A.physical_file_name

SET NOCOUNT OFF
END
GO

After data has been collected using the first SP, the second SP would be used to compare the differences. The output would look something like the below images (the images were broken into two parts to make it easier to read).

We can see the time of the last snapshot, the database, the physical file, the differences between the stats that were collected and the difference in hours between the two snapshots.

using sql server dmv to identify specific that consume the most i/o activity

collect i/o statistics for a certain time period and compare them to the data snapshots

Next Steps
  • Create the two tables and stored procedures in the scripts above and begin collecting statistics.
  • Create a SQL Agent job to run usp_io_vf_stats_snap procedure on a daily or hourly basis.
  • After at least two statistics collections, you can execute usp_compare_io_stats_snaps
  • Check the result to see where the I/O bottlenecks are and try to load balance the activity on the files by separating them into two or more files or moving files to different drives.
  • Read these additional tips about Dynamic Management Views
  • Read these additional tips about Peformance Tuning


Last Updated: 2011-04-11


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 14, 2011 - 3:43:45 PM - Ray Back To Top

Hi Eli,

I think you should make a minor change to your logic. You should capture the SnapId right when you insert into the Snap table by Selecting Scope_Identity() into a local variable.  Then use that local variable for you main insert rather than "Select Max(...."

Otherwise I think it is very good.

Ray


Monday, June 13, 2011 - 9:26:14 AM - Virul Patel Back To Top

Hi Eli,

Very nice and clear article to understand, I/O on the files.

Thanks, Virul

 


Wednesday, April 13, 2011 - 6:11:31 AM - Ian Stirk Back To Top

Hi,

Nice article.

You can discover a lot more about improving SQL performance via DMVs in this forthcoming book www.manning.com/stirk. It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor

Finding your slowest queries

Find your missing indexes

Identifying what SQL is running now

Quickly find a cached plan

Thanks

Ian


Tuesday, April 12, 2011 - 5:38:33 PM - Cardy Back To Top

Nice and clear script Eli.

A couple of tweaks I'd recommend though as I've developed something similar myself.

I'd certainly steer clear of putting any such thing in my master database.

Also if you ever restart your SQLServer or Failover you risk getting some confusing negative figures from your collector as all the sys.dm_io_virtual_file_stats get reset at such times.

I therefore include the following DELETE which compares against the last SQL Restart date  and also helps to purge your data growth...

    DELETE "<dbo.IOSnapshots>"
    WHERE snapshot_date < (SELECT create_date FROM sys.databases WHERE name = 'tempdb')

 

 

 

 


Learn more about SQL Server tools