March 5, 2010 at 12:59 pm
I'm not sure if this the correct forum for this question...
We are moving to a SAN. We wanted to see if there was any performance improvement. Would sys.dm_io_virtual_file_stats be a place to monitor? Or is there a different dmv that would be better to watch?
March 6, 2010 at 1:32 am
Some reading first:
http://msdn.microsoft.com/en-us/library/cc966412.aspx
Some perfmon stats to look at
Average Disk Seconds/Read
Average Disk Seconds/write
Each of those should be under 10ms for good, between 10-20ms is average,and over 50ms is bad.
Another perfmon stat
Avg. Disk Queue Length
This counter must be baselined over time. With your time based baseline, you can derive if it is functioning fine or not.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 6, 2010 at 4:02 am
sys.dm_io_virtual_file_stats is really only telling how the io is spread between the database files so is not really a help here.
To see if you have an improvement you need to know your current values so run a perfmon over a reasonable amount of time to get a baseline. Especially on SAN use logical disk counters rather than physical and do it on a per drive basis. As Jason says disk sec/read and write probably the best counter. Personally I find disk q length not too useful as there are too many factors which affect whether a value is good or bad.
Include disk reads/sec and disk writes/sec along with avg bytes/read and write to get an idea of throughput.
there are tools on the market to measure disk performance, free ones are SQLioSim (simulates typical SQL activity) and SQLio (measures maximum throughput).
the simplest test is to see whether heavy IO activities such as backups and defrags run faster, so ensure you have a history of the run times for these jobs.
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply