Log DISK IO statistics per Database file!!!
Well, ever wanted to know what was happening on disk....
You could with FN_vitrualfilestats....
Here is a NICE SPROC that samples all databases and puts it in a table. You can choose if you want to Retain values in the table or that it must be cleaned every time the sproc runs.
( Put this data in a VBForm and you have a nice DISK
activity monitor )
Well calling the SPROC like this : LOG_FILESTATS 0
Causes the SPROC to gather more and more date.
Every time you run the sproc the data will be inserted into the table.... ( Nice for analyzing data later..... )
Calling the SPROC like this : LOG_FILESTATS
Truncates the table so you wil get only one sample.
Combine this with A timer + GRID on a VB form and
you have a nice activity monitor
This is a revisited version, int has been replaced with bigint. Not every server is rebooted every night.....
CREATE PROCEDURE LOG_FILESTATS
( @BIT_DELETE_RESULTS BIT = 1 )
AS
SET NOCOUNT ON
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(500)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
IF OBJECT_ID('TBL_DATABASEFILES') IS NOT NULL
BEGIN
TRUNCATE TABLE TBL_DATABASEFILES
END
ELSE
BEGIN
CREATE TABLE TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
DATABASENAME SYSNAME)
END
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO TBL_DATABASEFILES(DBID,FILEID,FILENAME,DATABASENAME) SELECT '+STR(@INT_DBID)+',FILEID,NAME,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
IF OBJECT_ID('TBL_FILESTATISTICS') IS NOT NULL
BEGIN
IF @BIT_DELETE_RESULTS = 1 TRUNCATE TABLE TBL_FILESTATISTICS
END
ELSE
BEGIN
CREATE TABLE TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
END
SELECT @INT_MAXCOUNTER=MAX(ID) FROM TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END 
