Introducing sys.dm_io_virtual_file_stats

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.
How Do I Use This Dynamic Management View?

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

Read more: Dassault Systèmes ENOVIA V6 on SQL Server

Posted via email from jasper22's posterous