Have you ever encountered high compilations and recompilations in your SQL Server environment? These issues can cause serious performance problems. In this article, we will explore a common cause of this problem and discuss potential solutions.

When SQL Server executes a query, it typically caches the execution plan and reuses it for subsequent executions. This helps to improve performance by avoiding the need to recompile the query every time it is executed. However, there are scenarios where SQL Server is unable to reuse the cached plan, leading to excessive compilations and recompilations.

One such scenario involves the use of temp tables in stored procedures. Let’s consider an example to understand this better. Suppose we have a stored procedure that uses an internally declared temp table:

CREATE PROC TempTableTest1
AS
BEGIN
    CREATE TABLE #temptable
    (col1 INT NOT NULL)
    
    INSERT INTO #temptable VALUES(1)
    
    SELECT * 
    FROM #temptable
    JOIN sys.databases ON col1 = database_id
END

In this case, SQL Server is able to reuse the execution plan for the stored procedure, as the temp table is declared within the procedure itself. However, if we declare the temp table outside of the stored procedure and then access it from within, SQL Server is unable to determine if the schema of the temp table is the same in each run. This leads to the generation of a new plan for every execution of the stored procedure, even if the code is essentially the same.

Let’s consider the following example:

CREATE PROC TempTableTest2
AS
BEGIN
    SELECT * 
    FROM #temptable
    JOIN sys.databases ON col1 = database_id
END

When multiple users execute this stored procedure simultaneously, SQL Server is forced to compile a new plan for each execution, resulting in high CPU usage and potential performance degradation.

So, what can we do to address this issue? One solution is to create a physical table instead of using a temp table. By using a physical table, SQL Server can determine the schema and reuse the plan. However, this approach may introduce concurrency issues if multiple users are executing the procedure simultaneously.

Another solution is to use table variables instead of temp tables. Table variables have their own considerations, but they can provide a way to reuse the plan. Here’s an example:

CREATE TYPE TempTableType AS TABLE (Col1 INT NOT NULL)

CREATE PROC TempTableTest3 @tablevariable TempTableType READONLY
AS
BEGIN
    SELECT * 
    FROM @tablevariable
    JOIN sys.databases ON col1 = database_id
END

By passing a table variable as a parameter to the stored procedure, SQL Server can determine the schema and reuse the plan. However, it’s important to note that table variables have their own limitations and should be thoroughly tested before implementation.

In conclusion, excessive compilations and recompilations in SQL Server can be caused by the use of temp tables declared outside of stored procedures. By understanding this issue and considering alternative approaches such as using physical tables or table variables, you can improve performance and reduce the impact on your SQL Server environment.