April 16, 2018 at 7:57 am
Afternoon, I wonder if someone can offer any suggestions I haven't thought of on the below.
I have a query in production doing the following, the query is executed by an application generating the SQL code:
SELECT SUM("Column1"),SUM("Column2"),SUM("Column3"),SUM("Column4") FROM dbo.table WITH (READUNCOMMITTED)
The code is not in house and has been hacked by multiple 3rd parties over time, so I have no scope to change what the application does. 18 months a go I created an indexed view to cover the query which the query optimiser picked up and ran with all the way up until 2 weeks a go. Since then, the query plan is back to doing a clustered index scan of the base table. The query consumes 30% of the CPU on the server and is triggered a few hundred times a minute. I have tried the following to try and resolve this:
Updated Statistics with Full Scan on both the view and the table
Rebuilt indexes (even with small fragmentation percentages to see if it triggered a difference)
Dropped and recreated the view along with the index
Annoyingly, I have created a staging environment to replicate everything down to the server configuration, restored the database to this environment and voila, it will use the indexed view with just the base query executed. I have worked around a couple of issues previously in this environment by using a plan guide, but the only way I can get the query to use the indexed view is to select directly from the view and force the noexpand hint.
If anyone has any suggestions or anything to add that would be great, as I am currently stumped with my options.
Thanks
April 16, 2018 at 9:09 am
I'm honestly not sure, however, my suggestion would be to use extended events to capture the statistics used by the query when the optimizer builds the plan (you can also see these in SSMS 17.5, but I haven't tested it on 2012, it might work). Here's a blog post I wrote on the topic. You'll want to run this on the test system that is working the way you want as well as the production system (taking care because you are running debug events, good filtering is a must here). Compare the statistics that are getting used successfully to those that are failing. Stats drive optimizer choices, so it's likely where the issue lies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2018 at 9:37 am
Maybe capture the execution plans as well and try to see there are material differences in estimates, in row counts based on what you know about the data.
April 16, 2018 at 2:20 pm
Thanks Grant/Steve. The show statistics option sadly doesn't work with SQL Server 2012 but I will dig further down the statistics route with Extended Events and see what I can dig up. The estimated and actual rows unfortunately don't give a lot away in the Execution Plans with this particular issue.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply