x

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?
more ▼

asked Apr 06 '11 at 08:38 AM in Default

Jos Menhart gravatar image

Jos Menhart
222 6 7 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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/
more ▼

answered Apr 06 '11 at 12:54 PM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

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
Apr 07 '11 at 02:49 AM Jos Menhart
Have you tried taking the data from the correlated information and transforming that inside excel?
Apr 07 '11 at 04:10 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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 and [here][2] are a tutorial on how to do it.

[2]: http://sqltechconsulting.com/2009/12/08/auto-magically-visualize-your-perfmon-and-profiler-results/#axzz1ImDgWL8N
more ▼

answered Apr 06 '11 at 01:18 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x29
x8
x2

asked: Apr 06 '11 at 08:38 AM

Seen: 1388 times

Last Updated: Apr 06 '11 at 08:38 AM