The Meaning of Stored Procedures. Part 1 — Plan Caching and Reuse is a Good Thing.

Everybody knows that use of stored procedures offers a number of benefits over issuing T-SQL code directly from an application. One of the most common reasons for using stored procedures is as a security boundary — a user can be given access to execute a stored procedure without having permissions directly on the underlying objects. If you are interested in how ownership chains work in SQL Server, please take a look at this tutorial: Tutorial: Ownership Chains and Context Switching.

Stored procedures are also a convenient container for code reuse — they can be called many times and the query plans can be reused, saving the time, CPU and memory. Unfortunately, the vast part of publicly available information about how stored procedures work, what you should and what you should not do with them, come from SQL 2000 times. Meantime SQL Server has changed a lot and some “good old tricks” are not so good any more.

In this blog series I am going to explain to you:

1. Why plan caching and reuse is a good thing.

2. How to avoid unnecessary recompilations for plan stability-related reasons.

3. How to avoid unnecessary recompilations for plan optimality-related reasons.

4. Why you should always put SET NOCOUNT ON options at the beginning of all stored procedures and why you should be careful with other session settings, like ARITHABORT.

5. Why you should evade conditional logic inside stored procedures that do data access.

6. When plan reusing is not a good thing and how you can deal with this “Parameter Sniffing” problem.

7. What “sp_” prefix really means and if it so fatal to use it on SQL Server 2005 onwards as it was before.

Preface

Let me start by providing a few fundamental facts about compilations and recompilations.

Stored procedures are optimized and compiled during their first execution. Optimization is based on parameters supplied during this execution, and future executions will reuse the query plan stored in plan cache.

Query plans are read-only reentrant structures that are shared by multiple users. There are at most two instances of a query plan at any time in plan cache:

1. One for all of the serial executions.

2. One for all of the parallel executions. This means that the copy for parallel executions is common for all of the degrees of parallelism.

From a query plan, an execution context is derived. Execution contexts hold the values needed for a specific execution of a query plan, and they are also cached and reused. Even if execution contexts are reused, they are not reentrant, which means that at any point of time, an execution context can be executing only one batch submitted by a session.

Finally, every query plan and execution context have a cost associated with them. This cost partially controls how long the plan or context will live in the plan cache (more costly plans are more likely to be kept in procedure cache). The cost of an ad-hoc query is always zero, but for other kind of queries the cost is the measure of the amount of resources required to produce it. The formula used by SQL Server goes as follows:

Cost = I/O cost (two I/Os cost 1 tick, with a maximum of 19)

+ context switch cost (two context switches cost 1 tick, with a maximum of 8)

+ memory cost (sixteen memory pages cost 1 tick, with a maximum of 4)

Does compilation/recompilation really matter?

To answer this question, let me create a simple stored procedure (in all examples I am using AdventureWorks2008R2 database which can be downloaded from CodePlex website):

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
USE AdventureWorks2008R2;
GO
 
CREATE PROCEDURE [dbo].[usp_Orders]
(@CustID INT)
AS
SELECT h.[SalesOrderID], COUNT(h.[SalesOrderID]), SUM([TotalDue])
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d
ON h.SalesOrderID = d.SalesOrderId
WHERE h.[CustomerID] = @CustID
GROUP BY h.[SalesOrderID];
GO

To call this stored procedure multiple times with different @CustID values I created a really simple c# console application. To use my solution all you need to do is:

1. Create (using any edition of Visual Studio) new c# console application project.

2. Replace all code automatically added to Program.cs file with the code below.

3. By default, local default instance of SQL Server will be used. Please, change the connection string if needed.

4. Hit F5.

5. Copy a compiled file ConsoleApplication1.exe from binDebug Visual Studio project subfolder to a more convenient localization and rename it to ExecSP.exe.

As you can see, all what this code does is executing the stored procedure usp_Orders 5 000 times with @CustID values from 20 000 to 25 000. By using ExecuteNonQuery method I am discarding the actual results from the client to avoid additional overheads which are not related to the topic being discussed:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace ExecSP
{
    class ExecSP
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("Server=(local);DataBase=AdventureWorks2008R2;Integrated Security=SSPI"))
            using (var command = new SqlCommand("usp_Orders", conn) { 
                CommandType = CommandType.StoredProcedure }) {
                    SqlParameter param = new SqlParameter();
                    param = command.Parameters.Add("@CustID", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    conn.Open();
                        for (int i = 20000; i < 25000; i = i + 1)
                        {
                        param.Value = i;
                        command.ExecuteNonQuery();
                        }
                conn.Close();
                }
             }    
        }
    }

OK, we are almost ready to go. Before we start our test we also have to:

1. Set the xp_cmdshell server option.

2. Turn on time statistics.

3. Clear the buffer cache (never do this on a production server) and procedure cache as well (under no circumstances do this on a production server):

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
GO
 
SET STATISTICS TIME ON
GO
 
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO

To test our stored procedure, execute the following statement (where ‘k:\ExecSP.exe’ is the full name of the compiled and renamed file):

Selec All Code:
1
2
xp_cmdshell 'k:\ExecSP.exe'
GO

On my machine the results look like this:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1917 ms.

Looks like it took less than 2 second to execute this procedure 5 000 times.

Now it is time to compare these results with the time needed to execute exactly the same 5 000 queries but as ad-hoc ones. To do this, create another c# console application project and replace the default code with this one:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace ExecAdHoc
{
    class ExecAdHoc
    {
        static void Main(string[] args)
        {
            string connString = @"Server=(local);DataBase=AdventureWorks2008R2;Integrated Security=SSPI";
            SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                for (int i = 20000; i < 25000; i = i + 1)
                {
                    string query = "SELECT h.[SalesOrderID], COUNT(h.[SalesOrderID]), SUM([TotalDue]) FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderId WHERE h.[CustomerID] = "+ i + " GROUP BY h.[SalesOrderID]";
                    SqlCommand command = new SqlCommand(query, conn);
                    command.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }

Please note, that I had to deliberately create this ugly and really unsafe code that concatenates parameter values with the query string. Otherwise, it will be treated by SQL Server as prepared query and the execution plan will be automatically reused — we will look at this shortly.

After compiling the project and renaming the execution file, I clear both caches once more and execute this version of the query. This time it took almost 20 second, which means 10 times more:

Selec All Code:
1
2
3
4
5
6
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
 
xp_cmdshell 'k:\ExecAdHoc.exe'
GO

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 19869 ms.

Lesson learned — compilation is really expensive and CPU-heavy process, that takes long time even on otherwise idle machines.

What SQL Server can do to avoid these costly compilations?

Inside the Procedure Cache

Compiled execution plans are stored in procedure caches — yes, there are 4 of them:

1. CACHESTORE_OBJCP contains object plans.

2. CACHESTORE_SQLCP is for SQL plans.

3. CACHESTORE_PHDR where „bound trees” are stored.

4. CACHESTORE_XPROC contains external procedures plans.

Unfortunately, we have no direct control over the size of the procedure cache, and as a result of storing large number of execution plans the procedure cache “steals” memory from the Buffer Pool. This problem was particularly severe in SQL 2005 RTM and SP1 times when procedure cache size limits was calculated using the following formula: 75% 0-8GB + 50% 8-64GB + 25% >64GB. This means that on 16 GB box, 10 GB of RAM might have been used for procedure cache. The currently used formula looks like this: 75% 0-4GB + 10% 4-64GB + 5% >64GB, which means that on the same 16GB box only 4.2 GB may be used for the procedure cache.

Better, but still there is no point in wasting your valuable memory for storing plans that will never be reused. To see what I mean, please execute again (this time without clearing the procedure cache) ExecSP.exe program and then run the following query:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
	WHEN -1 THEN DATALENGTH(qt.TEXT)
	ELSE qs.statement_end_offset
	END - qs.statement_start_offset)/2)+1) AS [Statement],
 qs.execution_count,
 qs.max_worker_time,
 qs.max_elapsed_time,
 qs.max_logical_reads,
 qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
ORDER BY execution_count DESC;
GO

The result contains one row for each execution plan stored in the procedure cache:

Execution plan stored in the procedure cache

As you can see, the first one was used 5 000 times (that is the plan of our stored procedure) and the rest of them were used only once. To have a look at the specific execution plan, click this <ShowPlanXML … value — a graphical execution plan will be displayed. Please, check that even though there are small differences in CPU times and numbers of reads, all plans look exactly the same.

Ok, now we know that SQL Server stores all 5 001 execution plans. The next query will give us summary information regarding the CPU time and elapsed time (NULL in objected column means that these plans are associated with ad-hoc queries):

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
SELECT qt.objectid,
 SUM(qs.execution_count) AS execution_count,
 SUM(qs.max_worker_time) AS worker_time,
 SUM(qs.max_elapsed_time) AS elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
GROUP BY qt.objectid;
GO

image

But there is more — the problem with ad-hoc queries is not only the slow execution, but also the huge amount of wasted memory:

Selec All Code:
1
2
3
4
5
6
7
SELECT cp.usecounts, cp.size_in_bytes, objtype, qt.text, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT like '%dm_exec_sql_text%';
GO

Please, note that a single execution plan, even for a fairly simple query, can be about 100 KB in size:

image

To see how serious this problem can be, run the next query:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
SELECT objtype AS [CacheType],
	COUNT_BIG(*) AS [Total Plans],
	SUM(CAST(size_in_bytes AS decimal(18,2)))/1024/1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count]
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
GROUP BY objtype;
GO

image

Please remember, that we are looking at plans for queries executed for about 20 seconds by a single client and already the Ad-hoc plans took over 350 MB.

Stored procedures are not the only option

SQL Server can cache query plans for different types of batches beside ad-hoc queries and stored procedures:

1. The first method useful for reusing query plans is based on query preparation. ODBC and OLE DB expose this functionality via SQLPrepare/SQLExecute and ICommandPrepare interfaces. Very similar result can also be achieved by using sp_executesql system stored procedure.

2. For certain queries, SQL Server automatically replaces constant literal values with variables before the query plan will be compiled. This process is called simple parameterization and, as a result, if a subsequent query differs only in the values of the constants, it will match against the auto-parameterized query. In addition, SQL Server 2008 allows us to forced parameterization of almost all types of queries.

3. SQL Server can even cache strings submitted via EXEC for execution, which means that to some degree dynamic SQL query plans are also cached.

Let’s have a look at the first two options — the last one doesn’t look promising at all.

Prepared queries

To see the real difference between a stored procedure and a well-written ad-hoc query (which means a prepared query in this context), create another c# console app and replace Program.cs with the following code:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace ExecAdHoc
{
    class ExecAdHoc
    {
        static void Main(string[] args)
        {
            string connString = @"Server=(local);DataBase=AdventureWorks2008R2;Integrated Security=SSPI";
            SqlConnection conn = new SqlConnection(connString);
            SqlParameter param = new SqlParameter();
            param.Direction = ParameterDirection.Input;
            conn.Open();
            for (int i = 20000; i < 25000; i = i + 1)
            {
                string query = "SELECT h.[SalesOrderID], COUNT(h.[SalesOrderID]), SUM([TotalDue]) FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderId WHERE h.[CustomerID] = @CustID GROUP BY h.[SalesOrderID]";
                SqlCommand command = new SqlCommand(query, conn);
                param = command.Parameters.Add("@CustID", SqlDbType.Int);
                param.Value = i;
                command.ExecuteNonQuery();
            }
            conn.Close();
        }
    }
}

Then, as previously, compile the solution and rename the ConsoleApplication1.exe this time to ExecPrepared.exe.

The test itself is exactly the same — after cleaning both caches, execute ExecPrepared.exe and measure Execution time:

Selec All Code:
1
2
3
4
5
6
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
 
xp_cmdshell 'k:\ExecPrepared.exe'
GO

On my machine (Intel Core i7-3770K and 16 GB of RAM) this solution always wins (please remember, that all program were executed locally):

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 455 ms.

To check how many plans are stored, execute this query:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
SELECT objtype AS [CacheType],
	COUNT_BIG(*) AS [Total Plans],
	SUM(CAST(size_in_bytes AS decimal(18,2)))/1024/1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count]
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
GROUP BY objtype;
GO

image

As expected, the query plan was compiled once and used for all subsequent executions.

Lesson learned — there is nothing wrong (purely from performance perspective) in using prepared queries instead of stored procedures.

Auto-parameterized queries

If you have no other choice, and have to live with some badly-written client applications, the first thing to do, is to switch on server-level option ‘optimize for ad hoc workloads’:

Selec All Code:
1
2
3
EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

From now on, execution plans for ad-hoc queries will not be cached during their first execution, but upon the second one. To see an effect, please clear the procedure cache and execute the ad-hoc version of our program one more time:

Selec All Code:
1
2
3
4
5
6
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
 
xp_cmdshell 'k:\ExecAdHoc.exe'
GO

Yes, it is still slow:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 20107 ms.

But much less memory was used:

Selec All Code:
1
2
3
4
5
6
7
SELECT cp.usecounts, cp.size_in_bytes, objtype, qt.text, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT like '%dm_exec_sql_text%';
GO

image

Please note, that from now on only small “stubs” are stored in procedure cache — that is why the last column returns NULL instead of query plans.

If we check the total amount of memory taken by this stubs, it will be around a megabyte, not over 350 MB, as previously:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
SELECT objtype AS [CacheType],
	COUNT_BIG(*) AS [Total Plans],
	SUM(CAST(size_in_bytes AS decimal(18,2)))/1024/1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count]
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
WHERE qt.text LIKE '%Sales.SalesOrderHeader%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
GROUP BY objtype;
GO

image

Lesson learned — setting ‘optimize for ad hoc workloads’ can safe a huge amount of otherwise wasted memory and therefore can improve SQL Server overall performance. And you get this for almost nothing.

The ultimate option, but this time a risky one, is switching a database into forced parameterization mode. You can do this by executing ALTER DATABASE statement:

Selec All Code:
1
2
3
4
ALTER DATABASE [AdventureWorks2008R2] 
SET PARAMETERIZATION FORCED 
WITH NO_WAIT;
GO

That means SQL Server will auto-parameterize all except of few queries mentioned here: Forced Parameterization. Let me execute our sample ad-hoc queries for the last time:

Selec All Code:
1
2
3
4
5
6
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
 
xp_cmdshell 'k:\ExecAdHoc.exe'
GO

As a result of auto-parameterization, only one execution plan was compiled, stored, and subsequently reused. Please note, that by setting this option ad-hoc queries are treated similarly to stored procedure, and, in my case, run blazing fast:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 738 ms.

Final words — plan caching and reuse is a good thing, and setting ‘optimize for ad hoc workloads’ option is pretty safe way to avoid memory problems. Conversely, forcing auto-parameterization for all ad-hoc queries is quite risky and before you go this way, please read the remaining posts from this series.

The following diagram compares all results discussed in this article:

image

Clean up

Please, remember to switch off xp_cmdshell server-level option and to switch back AdventureWorks2008R2 to simple parameterization:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
ALTER DATABASE [AdventureWorks2008R2] 
SET PARAMETERIZATION SIMPLE 
WITH NO_WAIT
GO
 
EXEC sp_configure 'xp_cmdshell',0
EXEC sp_configure 'optimize for ad hoc workloads', 0
EXEC sp_configure 'show advanced options',0
RECONFIGURE
GO

Cheers

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



5 thoughts on “The Meaning of Stored Procedures. Part 1 — Plan Caching and Reuse is a Good Thing.

  1. very handful of internet websites that come about to be in depth below, from our point of view are undoubtedly very well worth checking out

  2. Good day! This is my 1st comment here so I just wanted to give a quick shout out and say I truly enjoy reading your blog posts. Can you recommend any other blogs/websites/forums that go over the same topics? Thanks for your time!