how to identify database file with most i/o

Question

Post reply

how to identify database file with most i/o

george sibbald

SSC Guru

Points: 104210
More actions
March 27, 2008 at 5:50 am

#128395

Hi,
I have a windows 2003 SP2 server running SQL 2000 SP4. Its a consolidated server supporting about 40, mostly small databases.
the .mdfs are on one drive and its becoming i/o bound. I want to identify some databases to move off the server. Anyone got any ideas how I can identify which of the database files have the most i/o activity. I guess I really want the perfmon i/o counters but reporting at a file level rather than drive level!
---------------------------------------------------------------------

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

anyone? is it not possible?

---------------------------------------------------------------------

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Process Explorer - free tool from Microsoft.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

george sibbald SSC Guru Points: 104210 More actions · Answer 3

cheers, I am havng trouble seeing where it shows i/o per file from the gumph but I'll give it a go and report back...............

---------------------------------------------------------------------

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

george sibbald (3/28/2008)
cheers, I am havng trouble seeing where it shows i/o per file from the gumph but I'll give it a go and report back...............

Rats! Sorry, George, I gave you the wrong tool. What you actually want is Process Monitor (Procmon.exe), also free from Microsoft and written by the same person (which is why I mixed them up).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

george sibbald SSC Guru Points: 104210 More actions · Answer 5

no probs, thanks for replying. I've downloaded procmon and will have a play next week.

---------------------------------------------------------------------

Greg Linwood SSC Journeyman Points: 88 More actions · Answer 6

fn_virtualfilestats() is definitely the tool for this job.

Here's a script I wrote last year which makes using this TSQL system function easy..

http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/03/15/59.aspx

Regards,
Greg Linwood

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Greg Linwood (4/3/2008)

fn_virtualfilestats() is definitely the tool for this job.

Here's a script I wrote last year which makes using this TSQL system function easy..

http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/03/15/59.aspx%5B/quote%5D

Greg, you have just made my day. Thankyou so much. 😀

---------------------------------------------------------------------

Greg Linwood SSC Journeyman Points: 88 More actions · Answer 8

You're welcome!

Just be aware that you need to poll those values into a table & query the delta between samples, as SQL Server continually increments the counters throughout the life of the process & resets them between restarts.

I have attached a .zip file which contains some simple logging code that might make this easier. The blog post is more or less designed for ad-hoc use from SSMS. To use the logging scripts in the .zip, simply create the table & proc in the first .sql file, then call the proc from a SQL Agent task every minute or hour & run the analysis query as required (changing the dbid / fileid params, depending on which file you want to track)

Regards,
Greg Linwood

george sibbald SSC Guru Points: 104210 More actions · Answer 9

thanks greg, having run the one off query I was thinking of adapting it to output to a table so I could order by most io or whatever.

This will be most useful because I need to know the busiest databases on my consolidated servers.

---------------------------------------------------------------------

Key DBA SSCertifiable Points: 6029 More actions · Answer 10

Greg,

Hooray! (... or should that be Huzzah!) I have another tool for my SQL Server Tool Belt.

Thanks for sharing your solution, I will definitely be using this function as we are beginning to look at how/where we can consolidate our own SQL Server systems.

Regards,

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP