Getting rid of slow searches and bringing back missing clips

Our customer has noticed that certain searches for media files performed by operators would run slowly and not yield any results. In this case, however, it did not mean that there was no media to return: we have faced one of the strangest SQL Server errors known to DBAs.

Whilst going through the Microsoft SQL Server ERRORLOG I have noticed the following stack dump:
StackDumpInErrorlog

 

Within it I could see the query that the server had a problem with. When I tried to manually execute it via SQL Management Studio I have received the following error:
Could not produce query plan

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

 

Luckily, I have found a detailed guide on MSDN, describing how such issues can be analysed.
Since SQL Server Query Optimizer relies on statistics, it could be that some stale stats have been preventing the query plan from being successfully generated. So I have manually updated statistics for all tables that were included in the query, using the following command:

UPDATE STATISTICS Media.Table WITH FULLSCAN;
GO

But to no avail – the query plan still failed to be generated, resulting in the same error message.

 

Then I have added a clause at the end of the query: OPTION (FORCE ORDER)
According to the article above, it “tells the optimizer not to use its own logic and just follow the join order which we specified in the query and produce the query plan (may be a bad plan but just produce it).”
Excellent! Now the query executes! But how can we ensure that it does it in future, when the application initiates it? The article suggests that we can analyse this query in Database Engine Tuning Advisor and identify the missing indexes… Sadly, the query analyzer did not provide any suggestions at all:
NoRecommendations

 

Then I have checked a number of variables to see if they could be causing the problem, but they were all set to recommended values:

Checked compatibility level – it was set to 90
ANSI_NULLS was active, as well as QUOTED IDENTIFIER:
(5496)
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

 

As the last-resort measure, I have decided to wait for a quiet evening (when DB usage was low) and update all statistics for all tables where rows have been modified, by running the following command:

sp_updatestats

Output of that command spans many pages, but I could clearly see that 66 indexes/statistics have been also updated for one of the tables that was used in our query:
ManyUpdates

Aaand… The query now executes successfully!


Successful After UpdateStats

Well, one less annoying error in ERRORLOG and no more missing search results for the operators!
I have reported my findings to the application vendor, so hope that this helps other users too.