April 16, 2015 at 5:22 pm
Awesome, thanks, have not used that hint before. Will give it a go! 🙂
April 20, 2015 at 9:33 am
I'm not sure if there's anything else to be done here, but I did want to update you on things.
After our DBA ran all her behind-the-scenes wizardry, rebuilding indexes and updating statistics, there is still NO change. OPTION (RECOMPILE) didn't change things either. I've read through Gail's articles on parameter sniffing, and they make sense conceptually, but I don't think that's the problem here. I am running static SQL here without parameters or variables or sprocs, and this issue is persisting. For Node 16 in our execution plan, estimated number of rows is 3, actual number of rows is 6 million.
It seems SQL Server is simply unable to find an efficient/accurate query plan for this query when the WHERE value for the CostCenter column is '00966'. This seems insane. 🙁
April 20, 2015 at 11:17 am
Thanks for the update!
Yeah, if the estimated row counts after updating stats and forcing a recompile of the query are still wrong, it sounds like the cardinality estimator isn't up to the task on this one, at least with the statistics currently in place.
I'm a little suspicious about the stats, since in the postfullstatsupdate plan you posted, the estimate for the initial seek on _Result188 for CostCenter='00966' is off by more than 20x.
A couple of the subsequent joins also look like SQL Server thinks they're going to be more selective than they actually are, but it looks like the main problem is the 20x underestimation of results for '00966' in _Result188, as that seek gets used as input to several of the joins, so the poor estimate gets compounded.
Without being able to rewrite the code, I think the only real option left is to try creating filtered statistics for the value/column combinations that are so dramatically off, the primary one here of course being for the value of '00966' on the column CostCenter in _Result188.
http://www.sqlpassion.at/archive/2013/10/29/fixing-cardinality-estimation-errors-with-filtered-statistics/ is a nice introduction to using filtered statistics to mitigate this sort of problem.
If you are able to create those statistics and then recompile the query, let me know how the performance and plan look then.
Cheers!
EDIT: I just remembered that you specified SQL Server 2005, which eliminates filtered statistics as an option. Doh!
Given that, we may be running out of options. I'll keep mulling it over, though.
April 20, 2015 at 3:27 pm
Thank you so much for the ideas Jacob. The problem has spread to a second costcenter today, so I reached out to IBM to see what they can do. (Not holding my breath.) I've never seen or heard of something like this before, and I'm not a DBA, so I'm learning as I go! Will keep you posted.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply