Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Utilizing fn_virtualfilestats

By Chris Kempster,

File Stats Example

Utilizing ::fn_virtualfilestats - Example

 

In this example, we will attempt to get some statistical information on file-group utilisation to assist us with IO monitoring.   The DBA should re-read the article “How to Diagnose and Fix Wait Stats” by Cathan Kirkwood before attempting to analyse the statistics returned for your particular database.

 

The script below is very simple and is a starting point for a single stored procedure you can build in the future.  The paradigm itself is simple enough. We will store every N seconds the results from ::fn_virtualfilestats into a working table.  We will then get the difference between the previous scan for each file-group within the selected database.  From here we will utilise Analysis Services to build a cube and query the results through the Excel pivot control.

 

The script I used is shown below; remembering that I was extremely lazy (hang on, make that “busy”) and did not write an generic routine.  The routine was run via the tempdb database.

 

-- We have pre-queried sysdatabases and filegroups and will be monitoring as follows:

-- dbid = 5             

-- 1 = system

-- 2 = log

-- 3 = data

-- 4 = index

-- 5 = audit

 

-- don’t create it in the DB we are monitoring

drop table  tempdb.ck_filestats           

 

-- table to store out statistic data

create table ck_filestats (

id                             int identity(1,1) clustered index,

dbid                         int,

dbname                   varchar(50),

fileid                        int,

[filegroup]               varchar(150),

timestmp                bigint,

numreads                bigint,

numreads_diff         bigint,

numwrites               bigint,

numwrites_diff        bigint,

bytesread               bigint,

bytesread_diff        bigint,

byteswrite               bigint,

byteswrite_diff        bigint,

iostallms bigint,

iostallms_diff          bigint,

statstime                datetime default getdate()    

)

 

 

begin

declare @aa int

 

set @aa= 1440      -- approx 4hrs of data collection every 10 seconds

 

while @aa > 1 begin

 

               

                waitfor delay '00:00:10'       -- 10 sec delay per looper

 

                -- get stats for file 1

insert into ck_filestats (dbid, fileid, timestmp, numreads, numwrites, bytesread, byteswrite, iostallms) select * from ::fn_virtualfilestats(5,1)

               

                update     ck_filestats

                set           numreads_diff = ck_filestats.numreads - (select numreads from      ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

numwrites_diff = ck_filestats.numwrites - (select numwrites from    ck_filestats B
where      B.id = (select          max(id) from           ck_filestats C         where      ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

bytesread_diff = ck_filestats.bytesread - (select bytesread from     ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

byteswrite_diff = ck_filestats.byteswrite - (select byteswrite from   ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

iostallms_diff =  ck_filestats.iostallms - (select iostallms from        ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id))

where      dbid = 5 and fileid = 1 and id = (select max(id) from ck_filestats C where ck_filestats.dbid = C.dbid and ck_filestats.fileid = C.fileid)

               

                -- get stats for file 2

                insert into ck_filestats (dbid, fileid, timestmp, numreads, numwrites, bytesread, byteswrite, iostallms)       select * from ::fn_virtualfilestats(5,2)

               

                update     ck_filestats

                set           numreads_diff = ck_filestats.numreads - (select numreads from      ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                numwrites_diff = ck_filestats.numwrites - (select numwrites from    ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                bytesread_diff = ck_filestats.bytesread - (select bytesread from     ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                byteswrite_diff = ck_filestats.byteswrite - (select byteswrite from   ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                iostallms_diff =  ck_filestats.iostallms - (select iostallms from        ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id))

                where      dbid = 5 and fileid = 2 and id = (select max(id) from ck_filestats C where ck_filestats.dbid =        C.dbid and ck_filestats.fileid = C.fileid)

 

                -- get stats for file 3
               

                insert into ck_filestats (dbid, fileid, timestmp, numreads, numwrites, bytesread, byteswrite, iostallms)       select * from ::fn_virtualfilestats(5,3)

               

                update     ck_filestats

                set           numreads_diff = ck_filestats.numreads - (select numreads from      ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                numwrites_diff = ck_filestats.numwrites - (select numwrites from    ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                bytesread_diff = ck_filestats.bytesread - (select bytesread from     ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                byteswrite_diff = ck_filestats.byteswrite - (select byteswrite from   ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                iostallms_diff =  ck_filestats.iostallms - (select iostallms from        ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id))

                where      dbid = 5 and fileid = 3 and id = (select max(id) from ck_filestats C where ck_filestats.dbid =        C.dbid and ck_filestats.fileid = C.fileid)

 

                -- get stats for file 4
               

                insert into ck_filestats (dbid, fileid, timestmp, numreads, numwrites, bytesread, byteswrite, iostallms)       select * from ::fn_virtualfilestats(5,4)

               

                update     ck_filestats

                set           numreads_diff = ck_filestats.numreads - (select numreads from      ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                numwrites_diff = ck_filestats.numwrites - (select numwrites from    ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                bytesread_diff = ck_filestats.bytesread - (select bytesread from     ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                byteswrite_diff = ck_filestats.byteswrite - (select byteswrite from   ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                iostallms_diff =  ck_filestats.iostallms - (select iostallms from        ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id))

                where      dbid = 5 and fileid = 4 and id = (select max(id) from ck_filestats C where ck_filestats.dbid =        C.dbid and ck_filestats.fileid = C.fileid)

 

                -- get stats for file 5

                insert into ck_filestats (dbid, fileid, timestmp, numreads, numwrites, bytesread, byteswrite, iostallms)       select * from ::fn_virtualfilestats(5,5)

               

                update     ck_filestats

                set           numreads_diff = ck_filestats.numreads - (select numreads from      ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                numwrites_diff = ck_filestats.numwrites - (select numwrites from    ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                bytesread_diff = ck_filestats.bytesread - (select bytesread from     ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                byteswrite_diff = ck_filestats.byteswrite - (select byteswrite from   ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id)),

                                iostallms_diff =  ck_filestats.iostallms - (select iostallms from        ck_filestats B         where      B.id = (select          max(id) from                 ck_filestats C         where      ck_filestats.dbid = C.dbid     and ck_filestats.fileid = C.fileid and C.id <> ck_filestats.id))

                where      dbid = 5 and fileid = 5 and id = (select max(id) from ck_filestats C where ck_filestats.dbid =        C.dbid and ck_filestats.fileid = C.fileid)

 

                set @aa = @aa - 1

end

 

 

-- Manually upate the table, can be simply done by quering the sys tables

update ck_filestats set dbname = 'MyDB’

update ck_filestats set [filegroup] = 'SYSTEM' where fileid = 1

update ck_filestats set [filegroup] = 'LOG' where fileid = 2

update ck_filestats set [filegroup] = 'CORPSYS' where fileid = 3

update ck_filestats set [filegroup] = 'DATA' where fileid = 4

update ck_filestats set [filegroup] = 'AUDIT' where fileid = 5

 

end

 

Next we create a very simple OLAP cube.  Once created, we will utilise the pivot table control in Excel to analyse the results. 

 

The ck_filestats table will be the fact table and will also drive the dimensions for the cube.  The measures will be the ­_diff columns.  The dimensions will include:

a)     time – broken down to hour and minute - (based on the statstime column)

b)     database – not really required as we only have one - (based on dbname column)

c)     file group – (based on [filegroup] column)

 

To create the cube, run query analyser, select the foodmart sample cube database, and run the new cube wizard.

 

 

 

Click on the New Dimension button after pressing Next> above.  For each dimension we create we will select the star-schema and the dimension table will be the same as the fact table (ck_filestats). 

 

The first dimension is Time, so we can map data over the hours and minutes for which we run the collection.

 

 

Use the dimension levels below (going to the minute), we will alter this later:

 

 

Skip the options screen, and save as “Time”, uncheck the “share this dimension with other cubes” check box.

 

The next dimension will be called Database.  This is a standard dimension using the dbname column.  Again, skip the options screen and uncheck the share dimension option.

 

The final dimension will be called File Group, as above but select the filegroup column from ck_filestats.  You will end up with these non-shared (local) dimensions as shown in the wizard screen:

 

 

Complete the wizard with the cube name:

 

 

The cube editor screen is shown. 

 

Expand the Time dimension and delete Year, Quarter, Month.

 

 

The final structure will look like this:

 

 

Rename the measures:

 

The select Tools -> process cube, select all defaults, if it asked about aggregations not being designed/built say you know and continue as per normal.  Select MOLAP storage scheme, slick STARS button to pre-calculate the storage and optimizations required, then next and process now.

 

 

Click on the Data tab at the bottom on the designer, move the dimensions around as shown in the screen shot to have a quick look at the statistics generated.  It is not until we graph it will you see patterns emerge.

 

 

To generate a graph.  Run Excel, this must be Office 2000 or above.

 

Select Data à  PivotTable and PivotChart Report

 

 

Select OLAP cubes, new data source.  See example below, utilising my local PC’s analysis server:

 

 

Note for the Server name in the connect window, I entered in my pc (host) name, the IP address did not work.

 

 

switch to chart view:

 

Bytes Read, Bytes Written, Total Reads, Total Writes à data Area

Time à category axis

File Group à series axis

Database à page area

 

I selectively removed (filtered) Total Reads and Total Writes from the screen shot below:

 

 

Moving between the views, altering graphs types etc, we can really get a gist of the IO characteristics over the four hours of analysis, with complete drill through to each hour.

 

 

Interpretation and analysis of the results should be done hand in hand with performance monitor statistics.  The DBA should not forget that the statistics presented will differ based on buffer cache hit ratios, insert/update/delete activity and the overarching application dynamics.

 

 

 

Chris Kempster

 

References

How to Diagnose and Fix Wait Locks, Cathan Kirkwood

http://www.sqlservercentral.com/columnists/ckirkwood/wait_types.asp

 

Total article views: 7949 | Views in the last 30 days: 4
 
Related Articles
FORUM

"Select Where In" using a parameter?

select intCol from Tablename where intCol in (@intList)

FORUM

How to select count(*) where count < 4

How to select count(*) where count < 4

FORUM

SELECT statement hangs on certain where conditions

Bizarre issue with certain conditions in where statement cause the select to hang forever

FORUM

Conditional Where Clause

Select how the Where Clause operates

FORUM

Select Where Any Column Equals (or Like) Value

Select row(s) where any of the column names in table equals a value

Tags
administration    
advanced querying    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones