question

Jos Menhart avatar image
Jos Menhart asked

Sql server read demand vs physical disk reads delivered

Hi All, I would like to know if it is possible to capture perfmon or other counters to correlate the physical reads issued by the SQL Server Engine to the number of reads the physical disk delivers at a given moment in time. For instance this could show SQL Server issues 3000 (page) reads per second at a point in time, while the disk is processing reads at 200 reads per sec. Can anyone tell me if this is possible using either perfmon counters or maybe by periodically gathering IO stats from dynamic management views?
statisticsstoragedisk-iodisk-usage
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
You can combine perfmon with profiler, the outputs can be synched-up so you can see what query caused what sort of hardware load at the O/S level. Check out Brent Ozar's great tutorial: [SQL Server Perfmon Counters Tutorial][1] [1]: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jos Menhart avatar image Jos Menhart commented ·
I have correlated profiler with perfmon before, but this does not give me exactly what I want. I would like to end up with a table like this: ![alt text][1] [1]: http://demo.mijnteleena.nl/images/Example.png
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Have you tried taking the data from the correlated information and transforming that inside excel?
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
Just to add an example on what @WilliamD suggested on how you can integrate perfmon with profiler trace to correlate each SQL statement and performance counters. [Here][1] and [here][2] are a tutorial on how to do it. [1]: http://www.sqlteam.com/article/integrating-profiler-and-perfmon-log-files [2]: http://sqltechconsulting.com/2009/12/08/auto-magically-visualize-your-perfmon-and-profiler-results/#axzz1ImDgWL8N
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.