Performance monitor, execution io question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gene. - 27 Jun 2008 04:48 GMT

Hi folks.
I hit problem which i can't explain so you could help me.
I try to fix existing application and run sql perf. monitor to get biggest
io offenders. So i get them into table and select them ordered by 'reads' in
desc order.
Than I get text and try to run it from my desktop with 'set statistics io
on' set.
What i see is that the same execution line from my desktop versus
application execution in sql perf. show fundamentaly different io's. I may
get 50 io versus 'reads' value for the same statement shows 3000. i should
add that all those executions are using sp_executesql.

So why the same statement may take much bigger io's depending on where it's
executed from?
Is that a valid approach to compare read io's from 'statistics io on' output
with 'reads' reading from sql performance monitor?

Gene.

Reply to this Message

Andrew J. Kelly - 27 Jun 2008 15:08 GMT

You are probably running into an issue with parameter sniffing. You can do a
Google search for lots of info but I would start here first:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi folks.
> I hit problem which i can't explain so you could help me.
[quoted text clipped - 17 lines]
>
> Gene.

Reply to this Message

Gene. - 27 Jun 2008 16:41 GMT

Hi Andrew

The info you sent is very interesting.
However here is what i found:
I start sql profiler catching only my user executions. When I run statement,
I get 95 io's from 'statistics io on'
The same statement shows 4369 reads in sql profiler.

how is that possible that the same statement reflects so different number of
io's in different tools?

I run sp_executesql. Before I had some discrepancy in these number, but this
difference is huge and make one of those output is incorrect.

What do you think?

Gene.

> You are probably running into an issue with parameter sniffing. You can do a
> Google search for lots of info but I would start here first:
[quoted text clipped - 21 lines]
> >
> > Gene.

Reply to this Message

Andrew J. Kelly - 27 Jun 2008 19:05 GMT

Gene,

Profiler shows reads related to system objects (security lookups etc.) which
I don't think statistics IO does. But this is indeed a big difference and I
am not quite sure why that is. One thought is that maybe some of that is
related to the compiling process. If you run that exact statement over and
over do you get the same results?

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi Andrew
>
[quoted text clipped - 48 lines]
>> >
>> > Gene.

Reply to this Message

Gene. - 27 Jun 2008 21:10 GMT

Hi Andrew

I figured the problem. 'statistics io' does not in fact reflect accurately
io's for sp_executesql statement.
When I ran dta, it produces some kind of output like:
Event does not reference table... event was replaced ... for tunning purposes.
And it does replace with what it would be without sp_executesql.

Now, if you run it with set io, it would produce what you have seen in sql
profiles.
Thank you for your input, Gene.

> Gene,
>
[quoted text clipped - 56 lines]
> >> >
> >> > Gene.

Reply to this Message

TheSQLGuru - 28 Jun 2008 15:14 GMT

Bet you have a user defined function in there somewhere. The IO due to
those are not included in set io output but are represented correctly in
profiler.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Hi Andrew
>
[quoted text clipped - 48 lines]
>> >
>> > Gene.

Reply to this Message

Gene. - 29 Jun 2008 15:49 GMT

Hi Kevin

I sent you mail but figured also how to fix my use of this forum - it did
not open window.
You was right, it uses functions. Intensive use of views makes tunning of
this app much harder. Now I have to start using dta. It's a good tool, but
many times it did not produce any recommendations when I was able manualy
improve code by creating indexes. I do not beleive much in it's 100%
efficency.
However, it seems the only way to deal with a complicated, multilevel sql
(views on views, function use other functions).
Let's see if i will get far with using it only.

Thank you all who participated in this discussion, Gene.

> Bet you have a user defined function in there somewhere. The IO due to
> those are not included in set io output but are represented correctly in
[quoted text clipped - 52 lines]
> >> >
> >> > Gene.

Reply to this Message

TheSQLGuru - 29 Jun 2008 21:21 GMT

One word of caution - be VERY careful with the use of DTA, especially with
regards to it's pervasive use of INCLUDED columns. I have routinely seen it
create obscene numbers of indexes and in include obscene numbers of columns.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Hi Kevin
>
[quoted text clipped - 73 lines]
>> >> >
>> >> > Gene.

Reply to this Message