SQL Server T-SQL User-Defined Function of the Week

Using fn_virtualfilestats to Get I/O Stats by Database.

Volume 1 Number 37     July 29, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

There are many ways to analyze performance of SQL Server.  In 
recent months I've written about using Perfmon and SQL Profiler
to take a look at various aspects of performance.  You'll find
one of the articles on www.DatabaseJournal.com at the link:
http://www.databasejournal.com/article.php/10888_2203601_3

SQL Server has several other ways to provide performance data
including several built-in functions and a system user-defined
function named fn_virtualfilestats.

fn_virtualfilestats returns statistics on Input/Output (I/O)
activity on a database file by database file basis.  There
are times when you might want to see it at the individual file
basis but there are times when you'd prefer that it be
aggregated at a different level.

This week's UDF aggregates the output of fn_virtualfilestats
by database.  This gives you an idea about which databases 
are the most active.

Here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_Perf_FS_ByDbTAB (

    @DB_Name_Pattern sysname = NULL -- LIKE name of the database
             -- to get stats for or NULL for ALL
)   RETURNS  TABLE
   -- No SCHEMABINDING due to use of system UDF
/*
* Returns a table of total statistics for one databases or
* a group of databases where the name matches a pattern. Null for
* all.  Done by grouping by database.
*
* Example:
select * from dbo.udf_Perf_FS_ByDbTAB ('pubs')
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 1 #37 7/29/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
*****************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES 
       DB_Name(DbId) [DatabaseName]-- get the name
     , DbId AS [DBID]-- ID might be useful some times
     , Count(DbId) [NumberOfFiles]-- Number of files
     , Sum(NumberReads) as [NumberReads]
     , Sum(NumberWrites) as [NumberWrites]
     , Sum(BytesRead) as [BytesRead]
     , Sum(BytesWritten) as [BytesWritten]
     , Sum(IoStallMS) as [IoStallMS]
     , Avg([TimeStamp] / 1000) as SecondsInMeasurement
   FROM ::fn_virtualfilestats(-1, -1) -- -1 for all db and files
   WHERE (@DB_Name_Pattern IS NULL
         OR db_Name(dbid) LIKE @DB_Name_Pattern)
   GROUP BY DbID
   ORDER BY Sum(NumberReads)
          + Sum(NumberWrites) desc -- Top I/O first

GO
GRANT SELECT on dbo.udf_Perf_FS_ByDbTAB to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

The following query gets the file stats for pubs.  As you can
see, there hasn't been to much activity yet today.  I've used
column aliases to shorten the column names enough that the output
fits on a line.
/------- Start copying below this line -------------------------\
select DatabaseName as Name
     , NumberOfFiles as Files
     , NumberReads as Reads
     , NumberWrites as Writes
     , BytesRead
     , BytesWritten
     , IoStallMS
     , SecondsInMeasurement as Sec
    FROM dbo.udf_Perf_FS_ByDbTAB ('pubs')
GO        
\-------Stop copying above this line ---------------------------/
(Results)
Name  Files  Reads Writes BytesRead BytesWritten IoStallMS    Sec
----- ------ ----- ------ --------- ------------ --------- ------
pubs       2    25      8   1327104        96768      2170   5851

The parameter isn't just one name.  You can use NULL to get
the results for all database or a pattern that works with the
LIKE operator to get several databases as in this query:

/------- Start copying below this line -------------------------\
select DatabaseName as Name
     , NumberOfFiles as Files
     , NumberReads as Reads
     , NumberWrites as Writes
     , BytesRead
     , BytesWritten
     , SecondsInMeasurement as Sec
    FROM dbo.udf_Perf_FS_ByDbTAB ('TSQL%')
GO
\-------Stop copying above this line ---------------------------/
(Results)
Name            Files  Reads Writes BytesRead BytesWritten Sec
--------------- ------ ----- ------ --------- ------------ ------
TSQLWorking          2    34      8    872448       156672    252
TSQLUDFS             2    21      6   1003520       139264    252
TSQLFunctions5       2    17      8    741376       156672    252

fn_virtualfilestats only shows statistics accumulated since the
SQL Server Instance started.  The Sec column tells you how long
that's been.

As you can see, my server was just rebooted.  The numbers
get more interesting when it's been up for a while.

My next article on Database Journal contains code that accumulates
the results of fn_virtualfilestats over time so that you can get 
a look at what's happening at peak database usage time periods, 
when they really count.