Performance monitor, execution io question
in [Server Configuration]
|
From: Gene. on 26 Jun 2008 23:48 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.
From: Andrew J. Kelly on 27 Jun 2008 10:08 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 -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Gene." <Gene(a)discussions.microsoft.com> wrote in message news:204EBA2C-8EFA-4CAC-A6B6-A5D9280EC27B(a)microsoft.com... > 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.
From: Gene. on 27 Jun 2008 11:41 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. "Andrew J. Kelly" wrote: > 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 > > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Gene." <Gene(a)discussions.microsoft.com> wrote in message > news:204EBA2C-8EFA-4CAC-A6B6-A5D9280EC27B(a)microsoft.com... > > 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. > >
From: Andrew J. Kelly on 27 Jun 2008 14:05 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? -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Gene." <Gene(a)discussions.microsoft.com> wrote in message news:B45A797A-399C-4CE9-826E-FDED008F0F0C(a)microsoft.com... > 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. > > "Andrew J. Kelly" wrote: > >> 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 >> >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Gene." <Gene(a)discussions.microsoft.com> wrote in message >> news:204EBA2C-8EFA-4CAC-A6B6-A5D9280EC27B(a)microsoft.com... >> > 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. >> >>
From: Gene. on 27 Jun 2008 16:10
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. "Andrew J. Kelly" wrote: > 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? > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Gene." <Gene(a)discussions.microsoft.com> wrote in message > news:B45A797A-399C-4CE9-826E-FDED008F0F0C(a)microsoft.com... > > 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. > > > > "Andrew J. Kelly" wrote: > > > >> 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 > >> > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> Solid Quality Mentors > >> > >> > >> "Gene." <Gene(a)discussions.microsoft.com> wrote in message > >> news:204EBA2C-8EFA-4CAC-A6B6-A5D9280EC27B(a)microsoft.com... > >> > 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. > >> > >> > > |