how to identify database file with most i/o

  • george sibbald

    SSC Guru

    Points: 104200

    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!

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

  • george sibbald

    SSC Guru

    Points: 104200

    anyone? is it not possible?

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

  • RBarryYoung

    SSC Guru

    Points: 143327

    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: 104200

    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: 143327

    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: 104200

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

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

  • Greg Linwood

    SSC Journeyman

    Points: 88

    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: 104200

    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

    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: 104200

    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

    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

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

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