August 31, 2016 at 12:26 pm
We have a stored proc which runs every evening (as part of a nightly job)
Recently, one of the queries in this proc occasionally hangs and blows out tempdb (about once a week on average)
The only way I can get the query in question to run within normal expected duration and not blow out tempdb is to do the following (in order):
- Rebuild all the indexes on the 3 tables where most of the query's data comes from
- Update statistics on those 3 tables (columns only, default sampling)
- Run DBCC FREEPROCACHE
The following do not help:
- Running sp_recompile on the proc containing the query (and the proc that calls it)
- Update statistics on those 3 tables (table and columns, default sampling)
Can anyone shed some light on why I have to go to the extreme of rebuilding indexes? I would think that updating the statistics with default sampling and recompiling should be sufficient.
More detail:
I have captured the plans (estimated) for the query when it runs normally as well as when it hangs. Comparing the two plans, they appear similar except that:
- the "hang" plan contains a "nested loop (left outer join)" – the estimated number of rows coming out is approx. 800 million
- the "good" plan contains a "hash match (left outer join)" about the same place – the estimated number of rows coming out is approx. 58 million
the actual number of rows for that run was around 3 million, so both estimates are way off, but the "hang" plan is off compared to the "good" plan by over a factor of 14
Note that I am working on breaking up this query into 2 or more queries to reduce the complexity – it's a big query and I suspect that is at least part of the issue
August 31, 2016 at 5:03 pm
Please post the join and DDL for both tables involved.
_____________
Code for TallyGenerator
September 1, 2016 at 8:20 am
sorry, for me to do that would be a violation of our company's contractual agreements with outside clients
September 1, 2016 at 9:04 am
You may replace actual column names with Col1, Col2, etc.
And post not the whole query, but only that part where the tables involved in the join are mentioned.
Again, using made-up names for tables and columns.
_____________
Code for TallyGenerator
September 1, 2016 at 9:06 am
spoke to my boss about doing that, same issue, even with substitutions
September 1, 2016 at 2:32 pm
jgenovese (9/1/2016)
spoke to my boss about doing that, same issue, even with substitutions
The fault in the query must be too precious for your company.
Don't you dare to fix it!
:hehe:
_____________
Code for TallyGenerator
September 2, 2016 at 2:08 am
Given the lack of available information...
"parameter sniffing".
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply