Developing Matt

My Technical Journal

Wait Stats and File Stats by Andrew Kelly part 1

leave a comment »

I attended a class taught by Andrew Kelly in the sqlpass 24 hour free training hosted by sql pass.  Wait Stats and File stats are the core of Andrew Kelly’s tools to check performance. 

Part 1: FILE STATS

Definition: Any time sql server reads or writes to the physical file (any file: data, log, secondary, etc) it records information.  It records how often, how much data, how long (it takes to read and write), overall size.  This is tracked for every file.

Mechanism used? sys.dm_io_virtual_file_stats (replaced fn_virtualfilestats() in 2000)

These numbers are cumulative since the last time sql server was restarted (no other way to restart these stats).  These are presented as views; they are actually being held in memory.  So Autoclose will clear the stats for that database.

These numbers are physical i/o.  If your query is already in cache  it is using logical i/o and it doesn’t affect these numbers. 

Don’t forget Temp db…this is a database that has a lot of physical i/o.  (tends to have more than other databases).  Pay special attention to this database ( temp tables, sorting, cached joins, etc…these things create a tremendous amount of physical i/o)

You can use the two (file stats and wait stats) together to get more information.

Be aware that backups can skew these results.  You have to account for them. 

File stats are easy to monitor, so there isn’t any excuse to not see it.  Tip #1: Get a baseline and work from there.  Without a baseline there is no way to know if there is a problem that is sneaking up on you.

Most common issues that show up in file stats and what to do about it

High stalls on writes.  He recommended the following

  1. add more write back cache to the controller or san
  2. change the ratio to be 100% write back & 0% read
  3. Change the raid type
  4. Add more spindles to disk array
  5. Always separte log and data files from different arrays  (sequential vs. random access)
  6. Separate tempdb log file

High Stalls on Reads

  1. You can often get more performance from optimizing queries over by throwing hardware at it.
  2. Same as 2-6 above (increasing write speed increases read speed)

File Stat columns:

andrewkelly1

Advertisements

Written by matt

September 11, 2009 at 9:56 am

Posted in Sql Performance, Sql Server

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: