SQL SERVER – Reducing TempDB Recompilation with Fixed Plan

3 years ago

Recently, I had a very interesting situation while working with my client on the Comprehensive Database Performance Health Check. Let us talk about how we were able to reduce tempdb recompilation with Fixed Plan.

Real-World Scenario

One of the largest E-Commerce company recently reached out to me with help with their SQL Server Performance Tuning. We went ahead with a systematic and scientific way to look for the in-efficiency in the code. After spending a couple of hours we were able to list all the inefficiencies and implement their solutions.

However, when we were looking at the queries, we found an excessive amount of recompilation in the TempDB. This was happening because in a big long stored procedure they created was using a specific temporary table for an insane amount of the time. The temp table was again and again recompiled and that was actually slowing down the performance of the query big time. The need which we had was that either temp table does compile or we change how our entire stored procedure was written.

This is when we were able to take advantage of the fixed plan hint. Here is what we did with our stored procedure.

Fixed Plan

If your queries are accessing the TempDB frequently and leading to your query to change the execution plan, you can use the query hint KEEP PLAN.

SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
INNER JOIN #TempTable AS TT
ON PermTable.col1 = TT.col2
OPTION (KEEP PLAN);

When you run the above query which contains a regular permanent table and a temptable and if there is a need to recompile the execution plan due to frequent change in the TempDB, the query will not recompile as the query hint says to keep the original plan.

In the later version of SQL Server, this is usually not spotted due to the new feature of the statement-level recompilation. With that said due to some strange reason, even though we were using the latest version of the SQL Server, we still faced the performance issue and once we used the query hint Option Keep Plan, the performance of the stored procedure was under control.

I have previously written on this topic in detail over here: Plan Recompilation and Reduce Recompilation – Performance Tuning. I strongly suggest that you read the blog post for further understanding of this topic.

Reference: Pinal Dave (https://blog.sqlauthority.com)