 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Periodic High Read Queries
Periodically I have queries that generate extremely high logical reads. One proc in specific that I've identified typically does in the range of 200 to 300. However occasionally the proc will do 2-3 million. If I re-run the proc with the same parameters, I am unable to reproduce the issue. I setup a job to capture when this occurs and also grab the XML Showplan at the same time - comparing the resulting plan to the same plan when the proc runs normally I don't see any differences. I also setup detailed perf counters to see if there were any specific memory ops going on which might result in excessive memory thrashing, but did not see any spikes at the time that the high read proc occured. Also compared scheduled jobs and check for other high impact queries running at the same time and came up empty. Any suggestions on further steps I can take to investigate this? It is most likely due to a feature known as Parameter Sniffing. Do a Google search on that and you will find tons of good info.  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Periodically I have queries that generate extremely high logical reads. > One proc in specific that I've identified typically does in the range of [quoted text clipped - 20 lines] > > Any suggestions on further steps I can take to investigate this? In order to grab the execution plan that's being used when this occurs, I'm querying sys.dm_exec_query_stats, filtering for last_logical_reads greater than 500000 and cross-applying sys.dm_exec_sql_text and filtering the text for my proc of interest and cross-applying sys.dm_exec_query_plan to obtain the execution plan that was used. Does this seem to be valid? The query plan that is returned for these instances is identical to query plan that is used when I manually run the proc at a later time with the same parameters. If parameter sniffing was the issue, I'd expect a different execution plan to result. > It is most likely due to a feature known as Parameter Sniffing. Do a Google > search on that and you will find tons of good info. [quoted text clipped - 23 lines] > > > > Any suggestions on further steps I can take to investigate this? Are you using the same parameters as the one that did all the reads? If the plan is using index seeks and the normal parameter values are such that it only returns or processes a few rows and you then pass in a parameter that will return lots of rows or process lots of data you can get these results. This is what parameter sniffing is all about. If the plan is set for a seek or a nested loop join and you process thousands of rows the total reads will be much greater than the normal query. The exec_query_stats does not show what parameter values were used. You need to runa trace that has the parameters that were actually passed to the sp when the high reads occurred. If the plans are always the same then it is the number of rows processed that differed and that would depend on the values passed in. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > In order to grab the execution plan that's being used when this occurs, > I'm [quoted text clipped - 42 lines] >> > >> > Any suggestions on further steps I can take to investigate this? Proc was re-run with the same parameters. In order to grab the parameters, I have a trace running in the background capturing all high read operations. This allows me to re-run after the fact with the same parameters. In order to verify that changing data in the database did not affect the resulting data, I did a point in time restore to an offline server, re-ran the proc there - got the same execution plan as was captured when the high read event occured, but with very few reads (on order of 300) Further thoughts? > Are you using the same parameters as the one that did all the reads? If the > plan is using index seeks and the normal parameter values are such that it [quoted text clipped - 54 lines] > >> > > >> > Any suggestions on further steps I can take to investigate this? That sounds fishy. Are you positive the two plans are 100% identical even though one had lots of reads and the other didn't? And are you sure the number of rows was the same? What did the plan look like? I mean was it nested loops, merge etc. and how many steps in the tree? Did you capture the showplan along with the proc execution in the trace? If not that is the only way to be sure which plan was used for that exact execution. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Proc was re-run with the same parameters. > In order to grab the parameters, I have a trace running in the background [quoted text clipped - 82 lines] >> >> > >> >> > Any suggestions on further steps I can take to investigate this? One simple quick answer is to compile the sproc using the WITH RECOMPILE option. You could also use the OPTION (RECOMPILE) option with individual statements within the sproc. Andrew is probably right on about it being parameter sniffing. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Periodically I have queries that generate extremely high logical reads. > One proc in specific that I've identified typically does in the range of [quoted text clipped - 20 lines] > > Any suggestions on further steps I can take to investigate this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.