Track file access and IO activities in SQL Server 2000

Disk IO is one of the common bottleneck of SQL server. There are several ways to monitor IO activities, e.g. monitor the Avg. disk queue length, Avg. disk sec/read, and Avg. disk sec/write of a phisical and logical drive in windows performance monitor.

A more "SQL server" way is to call the system function ::fn_virtualfilestats in SQL server 2000. It has two parameters: DatabaseID and FileID. For more details about the function, please reference BOL. One benifit of this approcah is that we can keep a track of IO activity and analyze it by means SQL queries. We can shcedule a job to call the function and save the IO activities into a table. Because the function reports the accumulated value of IO counters since SQL server starts, we need to calculate the counters in unit time (say in a minute) and compare the IO performance.

After we figure out IO issue, the more important thing is find out what calls cause the heavy IO and improve the query. This can be done by means of sql profiler, index tunning wizard, sql execution plan analysis etc.

Starting from SQL 2000 SP4, it has a new feature to detect and report stalled or stuck I/O operations. A stalled I/O is an I/O operation that SQL Server has submitted to the operating system and is taking excessive time to complete. A stuck I/O is an I/O operation that has been submitted by the server to the OS and never completes. Please reference KB897284 for detailes (http://support.microsoft.com/?kbid=897284) The following is the sample code to track the IO stalls.

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_DBFileStats]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tbl_DBFileStats]
GO

CREATE TABLE [dbo].[tbl_DBFileStats] (
 [RecordTime] [datetime] NOT NULL ,
 [DbId] [smallint] NOT NULL ,
 [FileId] [smallint] NOT NULL ,
 [NumberReads] [bigint] NOT NULL ,
 [NumberWrites] [bigint] NOT NULL ,
 [BytesRead] [bigint] NOT NULL ,
 [BytesWritten] [bigint] NOT NULL ,
 [IoStallMS] [bigint] NOT NULL ,
 [NumberReadsPerMin] [decimal](16, 3) NULL ,
 [NumberWritesPerMin] [decimal](16, 3) NULL ,
 [BytesReadPerMin] [decimal](16, 3) NULL ,
 [BytesWrittenPerMin] [decimal](16, 3) NULL ,
 [IoStallsMsPerMin] [decimal](16, 3) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_DBFileStats] WITH NOCHECK ADD
 CONSTRAINT [PK_tbl_DBFileStats] PRIMARY KEY  CLUSTERED
 (
  [RecordTime],
  [DbId],
  [FileId]
 )  ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetDBFileStats]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[usp_GetDBFileStats]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

— SELECT * FROM tbl_DBFileStats where FileID=1
CREATE   PROCEDURE dbo.usp_STL_GetDBFileStats
      @Logon  nvarchar(255)=N”,
      @LocaleID nvarchar(50)=N’en-US’,
      @DbID   int=0,
      @FileID  int=-1
–$ Get file stats for databases
–// @DBID: database ID. -1, all databases; 0, Current db;
–// @FileID: -1, all files for the specified database
AS
SET NOCOUNT ON
IF @DbID=0 SELECT @DBid=DB_ID(DB_NAME())
DECLARE @CurrentTime  datetime
, @RecordID  int
, @LastTraceTime datetime
, @NumberReads decimal(16,3)
, @NumberWrites decimal(16,3)
, @BytesRead decimal(16,3)
, @BytesWritten decimal(16,3)
, @IoStallMS decimal(16,3)
, @Gap  int

DECLARE @tbl_DBFileStats TABLE
(
 [RecordID] int identity NOT NULL,
 [DbId] [smallint] NOT NULL ,
 [FileId] [smallint] NOT NULL ,
 [NumberReads] [bigint] NOT NULL ,
 [NumberWrites] [bigint] NOT NULL ,
 [BytesRead] [bigint] NOT NULL ,
 [BytesWritten] [bigint] NOT NULL ,
 [IoStallMS] [bigint] NOT NULL,
 [NumberReadsPerMin] decimal(16,3) NULL ,
 [NumberWritesPerMin] decimal(16,3) NULL ,
 [BytesReadPerMin] decimal(16,3) NULL ,
 [BytesWrittenPerMin] decimal(16,3) NULL ,
 [IoStallsMsPerMin] decimal(16,3) NULL
)

SELECT @CurrentTime=GETDATE()
— Get current file stats into the table variable
INSERT @tbl_DBFileStats (DBID,FileID,NumberReads,NumberWrites,BytesRead,BytesWritten,IoStallMS)
SELECT DBID,FileID,NumberReads,NumberWrites,BytesRead,BytesWritten,IoStallMS FROM ::fn_virtualfilestats(@DbID,@FileID)

–SELECT * FROM @tbl_DBFileStats

— Calculate the counter changes between this run and the last run, and calculate the counter per minute
SELECT @RecordID=MIN(RecordID) FROM @tbl_DBFileStats
WHILE @RecordID IS NOT NULL
BEGIN
 SELECT  @DBID=DBID,
  @FileID=FIleID
 FROM @tbl_DBFileStats
 WHERE RecordID=@RecordID

 SELECT @LastTraceTime=NULL,@NumberReads=NULL,@NumberWrites=NULL,@BytesRead=NULL,@BytesWritten=NULL,@IoStallMS=NULL
 SELECT @LastTraceTime=(SELECT MAX(RecordTime) FROM dbo.tbl_DBFileStats WHERE DBID=@DBID AND FileID=@FileID)
 IF @LastTraceTime IS NOT NULL
 BEGIN
  SELECT  @NumberReads=NumberReads,
   @NumberWrites=NumberWrites,
   @BytesRead=BytesRead,
   @BytesWritten=BytesWritten,
   @IoStallMS=IoStallMS
  FROM dbo.tbl_DBFileStats
  WHERE DBID=@DBID AND FileID=@FileID AND RecordTime=@LastTraceTime

  SELECT @Gap=DATEDIFF(second,@LastTraceTime,@CurrentTime)
  UPDATE @tbl_DBFileStats
  SET  NumberReadsPerMin=(NumberReads-@NumberReads)*60/@Gap,
   NumberWritesPerMin=(NumberWrites-@NumberWrites)*60/@Gap,
   BytesReadPerMin=(BytesRead-@BytesRead)*60/@Gap,
   BytesWrittenPerMin=(BytesWritten-@BytesWritten)*60/@Gap,
   IoStallsMsPerMin=(IoStallMS-@IoStallMS)*60/@Gap
  WHERE DBID=@DBID AND FileID=@FileID
 END
 SELECT @RecordID=MIN(RecordID) FROM @tbl_DBFileStats WHERE RecordID>@RecordID
END

INSERT dbo.tbl_DBFileStats(RecordTime,DBID,FileID,NumberReads,NumberWrites,BytesRead,BytesWritten,IoStallMS,NumberReadsPerMin,NumberWritesPerMin,BytesReadPerMin,BytesWrittenPerMin,IoStallsMsPerMin)
SELECT @CurrentTime,DBID,FileID,NumberReads,NumberWrites,BytesRead,BytesWritten,IoStallMS,NumberReadsPerMin,NumberWritesPerMin,BytesReadPerMin,BytesWrittenPerMin,IoStallsMsPerMin
FROM @tbl_DBFileStats

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

2 Responses to Track file access and IO activities in SQL Server 2000

  1. Youjin says:

    peter, question: when do you know you should monitor IO Activities?

  2. Peter says:

    there are two scenarios to monitor IO activities.The first is when you create the peformance baseline for your database. The baseline is built for normal time and peak load time (e.g. Monday, or end of month etc). Based on the baseline IO counters, you can build a performance trend for your database and predict and prevent the potential IO bottleneck.The second is when you have performance issue. You need to figure out whether it\’s caused by IO.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s