One of the tasks that every SQL Server database administrator for ENOVIA V6 needs to accomplish is the performance monitoring of database data and log files. Often, a DBA needs to understand the performance of their disk I/O and needs something that can break down the disk I/O requests for them. SQL Server 2005 introduced a perfect little dynamic management view that can help you understand the disk I/O requests made through your database by watching these requests at the file level.
The sys.dm_io_virtual_file_stats dynamic management view is very easy to use. It takes two parameters: database_id and file_id. To see all database and all files, simply execute the dynamic management view with NULL parameters. The only hard thing to understand about this dynamic management view is that the information contained in the dynamic management view has been accumulating since the last time SQL Server was started. This means that if your instance of SQL Server was started five months ago and a large data load or deletion took place on a database file four months ago that caused disk issues, you will still see the information today.
To overcome this cumulative effect, you will need to capture a baseline that will include all previous information and then capture the dynamic management view again on a periodic basis. Once you start capturing the dynamic management view again, simply take the differences to determine what disk I/O has taken place since the baseline or since the last capture.
Capturing the information from is dynamic management view is simple enough. The following script creates an audit table and a job that captures the dynamic management view information on a periodic basis. You can then use this audit table to report on your disk I/O usage.
USE master GO BEGIN TRY DROP TABLE file_stats END TRY BEGIN CATCH END CATCH GO CREATE TABLE file_stats ( instance_name VARCHAR(30) ,database_name VARCHAR(255) ,file_id BIGINT ,num_of_reads BIGINT ,num_of_bytes_read BIGINT ,io_stall_read_ms BIGINT ,num_of_writes BIGINT ,num_of_bytes_written BIGINT ,io_stall_write_ms BIGINT ,io_stall BIGINT ,size_on_disk_bytes BIGINT ,insert_date DATETIME DEFAULT GETDATE() ) /*This script goes into a job that executes once an hour INSERT INTO file_stats (instance_name,database_name,file_id, num_of_reads,num_of_bytes_read,io_stall_read_ms,num_of_writes, num_of_bytes_written,io_stall_write_ms,io_stall, size_on_disk_bytes) SELECT @@SERVERNAME,DB_NAME(database_id),file_id,num_of_reads, num_of_bytes_read,io_stall_read_ms ,num_of_writes,num_of_bytes_written,io_stall_write_ms, io_stall,size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(NULL,NULL) */ USE [msdb] GO /****** Object: Job [File Stats collection] Script Date: 09/03/2009 12:24:01 ******/ IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'File Stats collection') EXEC msdb.dbo.sp_delete_job @job_name = N'File Stats collection', @delete_unused_schedule=1 go /****** Object: Job [File Stats collection] Script Date: 09/03/2009 14:53:36 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/03/2009 14:53:36 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'File Stats collection', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Collect Data] Script Date: 09/03/2009 14:53:37 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Data', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'INSERT INTO file_stats (instance_name, database_name, file_id,num_of_reads, num_of_bytes_read,io_stall_read_ms,num_of_writes, num_of_bytes_written, io_stall_write_ms,io_stall,size_on_disk_bytes) SELECT @@SERVERNAME,DB_NAME(database_id),file_id,num_of_reads, num_of_bytes_read,io_stall_read_ms ,num_of_writes,num_of_bytes_written,io_stall_write_ms,io_stall, size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(NULL,NULL)', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'15 Minutes', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=15, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20090903, @active_end_date=20090918, @active_start_time=210000, @active_end_time=205959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
Once you start capturing this information, what will it tell you? Well, this dynamic management view gives six useful pieces of information for each file in your databases:
You can use the number of reads and writes and the amount read and written to help determine if your database is a read-heavy or write-heavy database. This information is useful when you try to determine your RAID levels, the number of spindles you need to service your data quickly, the configuration of your SAN, or even a simple thing like seeing if you have two files that are heavily used on the same disk storage array.
The stall information is useful in helping you determine if you are having disk issues. If the number of stalls rises or if you see many stalls during a given time period, you may want to start a more intensive disk monitoring solution to help determine the cause of the disk slowness. You may find out that one disk array out of 20 may not be able to handle the files placed on it, and this can help you correct that one disk array by adding more drives, changing the RAID configuration, or moving some files off of the array.
After you have captured the information and have an audit table full of useful file performance information, you will need to query that data. The following scripts query the data from the audit table, determine the differences in the data, and report those differences on hourly groupings. This allows you to determine the status of your files hour by hour, which will help you figure out what is causing any file performance issues.
USE master GO SET NOCOUNT ON BEGIN TRY DROP TABLE #instances END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #inputs END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #results END TRY BEGIN CATCH END CATCH CREATE TABLE #instances ( colid INT IDENTITY(1,1) ,instance_name VARCHAR(30) ,database_name VARCHAR(30) ,file_id TINYINT ) CREATE TABLE #inputs ( colid INT IDENTITY(1,1) ,instance_name VARCHAR(30) ,database_name VARCHAR(30) ,file_id TINYINT ,insert_date DATETIME ,insert_hour INT ,reads_for_period BIGINT ,read_bytes_for_period BIGINT ,read_io_stalls_for_period BIGINT ,writes_for_period BIGINT ,write_bytes_for_period BIGINT ,write_io_stalls_for_period BIGINT ,io_stalls_for_period BIGINT ,size_on_disk_growth_for_period BIGINT ) CREATE TABLE #results ( instance_name VARCHAR(30) ,database_name VARCHAR(30) ,file_id TINYINT ,start_date DATETIME ,end_date DATETIME ,start_time INT ,end_time INT ,reads_for_period BIGINT ,read_bytes_for_period BIGINT ,read_io_stalls_for_period BIGINT ,avg_read_io_stalls_for_period BIGINT ,writes_for_period BIGINT ,write_bytes_for_period BIGINT ,write_io_stalls_for_period BIGINT ,avg_write_io_stalls_for_period BIGINT ,io_stalls_for_period BIGINT ,size_on_disk_growth_for_period BIGINT ) DECLARE @instance VARCHAR(30) DECLARE @database VARCHAR(30) DECLARE @file TINYINT DECLARE @instance_number INT DECLARE @loop INT DECLARE @input_number INT DECLARE @loop2 INT DECLARE @insert_date2 DATETIME DECLARE @insert_hour2 INT DECLARE @reads_for_period2 BIGINT DECLARE @read_bytes_for_period2 BIGINT DECLARE @read_io_stalls_for_period2 BIGINT DECLARE @writes_for_period2 BIGINT DECLARE @write_bytes_for_period2 BIGINT DECLARE @write_io_stalls_for_period2 BIGINT DECLARE @io_stalls_for_period2 BIGINT DECLARE @size_on_disk_growth_for_period2 BIGINT DECLARE @insert_date1 DATETIME DECLARE @insert_hour1 INT DECLARE @reads_for_period1 BIGINT DECLARE @read_bytes_for_period1 BIGINT DECLARE @read_io_stalls_for_period1 BIGINT DECLARE @writes_for_period1 BIGINT DECLARE @write_bytes_for_period1 BIGINT DECLARE @write_io_stalls_for_period1 BIGINT DECLARE @io_stalls_for_period1 BIGINT DECLARE @size_on_disk_growth_for_period1 BIGINT SET @loop = 1 INSERT INTO #instances (instance_name, database_name,file_id) SELECT DISTINCT instance_name,database_name,file_id FROM dbo.file_stats SET @instance_number = @@ROWCOUNT WHILE @loop <= @instance_number BEGIN SELECT @instance = instance_name, @database = database_name, @file = file_id FROM #instances WHERE colid = @loop SET @insert_date2 = NULL SET @insert_hour2 = NULL SET @reads_for_period2 = NULL SET @read_bytes_for_period2 = NULL SET @read_io_stalls_for_period2 = NULL SET @writes_for_period2 = NULL SET @write_bytes_for_period2 = NULL SET @write_io_stalls_for_period2 = NULL SET @io_stalls_for_period2 = NULL SET @size_on_disk_growth_for_period2 = NULL SET @insert_date1 = NULL SET @insert_hour1 = NULL SET @reads_for_period1 = NULL SET @read_bytes_for_period1 = NULL SET @read_io_stalls_for_period1 = NULL SET @writes_for_period1 = NULL SET @write_bytes_for_period1 = NULL SET @write_io_stalls_for_period1 = NULL SET @io_stalls_for_period1 = NULL SET @size_on_disk_growth_for_period1 = NULL SET @loop2 = 2 TRUNCATE TABLE #inputs INSERT INTO #inputs (instance_name, database_name,file_id,insert_date,insert_hour ,reads_for_period,read_bytes_for_period, read_io_stalls_for_period ,writes_for_period,write_bytes_for_period, write_io_stalls_for_period ,io_stalls_for_period,size_on_disk_growth_for_period) SELECT instance_name,database_name, file_id,CONVERT(VARCHAR(10),insert_date,102),CASE WHEN DATEPART(hh,insert_date) > 12 THEN DATEPART(hh,insert_date) * 100 ELSE DATEPART(hh,insert_date) END ,SUM(num_of_reads), SUM(num_of_bytes_read), SUM(io_stall_read_ms) ,SUM(num_of_writes), SUM(num_of_bytes_written), SUM(io_stall_write_ms) ,SUM(io_stall),SUM(size_on_disk_bytes) FROM file_stats WHERE instance_name = @instance AND database_name = @database AND file_id = @file GROUP BY instance_name,database_name,file_id, CONVERT(VARCHAR(10),insert_date,102),CASE WHEN DATEPART(hh,insert_date) > 12 THEN DATEPART(hh,insert_date) * 100 ELSE DATEPART(hh,insert_date) END ORDER BY CONVERT(VARCHAR(10),insert_date,102),CASE WHEN DATEPART(hh,insert_date) > 12 THEN DATEPART(hh,insert_date) * 100 ELSE DATEPART(hh,insert_date) END SET @input_number = @@ROWCOUNT WHILE @loop2 <=@input_number BEGIN SELECT @insert_date1 = insert_date ,@insert_hour1 = insert_hour ,@reads_for_period1 = reads_for_period ,@read_bytes_for_period1 = read_bytes_for_period ,@read_io_stalls_for_period1 = read_io_stalls_for_period ,@writes_for_period1 = writes_for_period ,@write_bytes_for_period1 = write_bytes_for_period ,@write_io_stalls_for_period1 = write_io_stalls_for_period ,@io_stalls_for_period1 = io_stalls_for_period ,@size_on_disk_growth_for_period1 = size_on_disk_growth_for_period FROM #inputs WHERE colid = @loop2 - 1 SELECT @insert_date2 = insert_date ,@insert_hour2 = insert_hour ,@reads_for_period2 = reads_for_period ,@read_bytes_for_period2 = read_bytes_for_period ,@read_io_stalls_for_period2 = read_io_stalls_for_period ,@writes_for_period2 = writes_for_period ,@write_bytes_for_period2 = write_bytes_for_period ,@write_io_stalls_for_period2 = write_io_stalls_for_period ,@io_stalls_for_period2 = io_stalls_for_period ,@size_on_disk_growth_for_period2 = size_on_disk_growth_for_period FROM #inputs WHERE colid = @loop2 INSERT INTO #results(instance_name,database_name,file_id, start_date,start_time,end_date, end_time,reads_for_period,read_bytes_for_period , read_io_stalls_for_period,avg_read_io_stalls_for_period ,writes_for_period ,write_bytes_for_period, write_io_stalls_for_period,avg_write_io_stalls_for_period ,io_stalls_for_period,size_on_disk_growth_for_period) SELECT @instance,@database,@file, @insert_date1,@insert_hour1, @insert_date2,@insert_hour2 ,CASE WHEN @reads_for_period2 < @reads_for_period1 THEN @reads_for_period2 ELSE @reads_for_period2 - @reads_for_period1 END AS 'reads_for_period' ,CASE WHEN @read_bytes_for_period2 < @read_bytes_for_period1 THEN @read_bytes_for_period2 ELSE @read_bytes_for_period2 - @read_bytes_for_period1 END AS 'read_bytes_for_period' ,CASE WHEN @read_io_stalls_for_period2 < @read_io_stalls_for_period1 THEN @read_io_stalls_for_period2 ELSE @read_io_stalls_for_period2 - @read_io_stalls_for_period1 END AS 'read_io_stalls_for_period' ,(CASE WHEN @read_io_stalls_for_period2 < @read_io_stalls_for_period1 THEN @read_io_stalls_for_period2 ELSE @read_io_stalls_for_period2 - @read_io_stalls_for_period1 END)/(CASE WHEN @reads_for_period2 - @reads_for_period1 = 0 THEN 1 WHEN @reads_for_period2 < @reads_for_period1 THEN CASE WHEN @reads_for_period2 = 0 THEN 1 ELSE @reads_for_period2 END ELSE @reads_for_period2 - @reads_for_period1 END) AS 'avg_read_io_stalls_for_period' ,CASE WHEN @writes_for_period2 < @writes_for_period1 THEN @writes_for_period2 ELSE @writes_for_period2 - @writes_for_period1 END AS 'writes_for_period' ,CASE WHEN @write_bytes_for_period2 < @write_bytes_for_period1 THEN @write_bytes_for_period2 ELSE @write_bytes_for_period2 - @write_bytes_for_period1 END AS 'write_bytes_for_period' ,CASE WHEN @write_io_stalls_for_period2 < @write_io_stalls_for_period1 THEN @write_io_stalls_for_period2 ELSE @write_io_stalls_for_period2 - @write_io_stalls_for_period1 END AS 'write_io_stalls_for_period' ,(CASE WHEN @write_io_stalls_for_period2 < @write_io_stalls_for_period1 THEN @write_io_stalls_for_period2 ELSE @write_io_stalls_for_period2 - @write_io_stalls_for_period1 END)/(CASE WHEN @writes_for_period2 - @writes_for_period1 = 0 THEN 1 WHEN @writes_for_period2 < @writes_for_period1 THEN CASE WHEN @writes_for_period2 = 0 THEN 1 ELSE @writes_for_period2 END ELSE @writes_for_period2 - @writes_for_period1 END) AS 'avg_write_io_stalls_for_period' ,CASE WHEN @io_stalls_for_period2 < @io_stalls_for_period1 THEN @io_stalls_for_period2 ELSE @io_stalls_for_period2 - @io_stalls_for_period1 END AS 'io_stalls_for_period' ,@size_on_disk_growth_for_period2 - @size_on_disk_growth_for_period1 AS 'size_on_disk_growth_for_period' SET @loop2 = @loop2 + 1 END SET @loop = @loop + 1 END SELECT * FROM #results WHERE avg_read_io_stalls_for_period > 100 OR avg_write_io_stalls_for_period > 100 BEGIN TRY DROP TABLE #instances END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #inputs END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #results END TRY BEGIN CATCH END CATCH
Monitoring the performance of a database can actually affect its performance. Using a lightweight dynamic management view such as sys.dm_io_virtual_file_stats can help you monitor your performance without affecting your performance. With a simple SQL Server Agent job that captures the information on a periodic basis and a small reporting query, you can understand the performance of your data and log files without the heavy burden of some monitoring methods.
Randy Dyess is a mentor with Solid Quality Mentors and a former MVP. He's been working on SQL Server for more than 20 years and has authored multiple articles, white papers, and books about SQL Server. http://www.SQLServerManagement.com; http://www.SolidQ.com