Utilizing fn_virtualfilestats

Utilizing ::fn_virtualfilestats - Example

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 (

??????????????????????????? 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

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)

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.

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

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

Utilizing fn_virtualfilestats