Technical Article

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating