SQL Server 2008 : Common performance problems (part 1) - Procedure cache bloating

5/9/2013 9:50:13 PM

The rapid advance of technology together with falling component prices has meant a lot of database design and administration problems can be buried beneath a pile of memory and fast multicore CPUs. In some cases, this may be a valid option; however, throwing hardware at a problem is usually the least effective means of improving performance, with the greatest performance gains usually coming from good design and maintenance strategies.

In this section, we'll address a number of common performance problems and the wait types and performance counters applicable to each. We'll start with procedure cache bloating before moving on to CPU pressure, index-related memory pressure, disk bottlenecks, and blocking.

1. Procedure cache bloating

Each time a query is submitted for processing, SQL Server assesses the contents of the procedure cache for an existing plan that can be reused. If none is found, SQL Server generates a new plan, storing it in the procedure cache for (possible) later reuse.

Reusing existing plans is a key performance-tuning goal for two reasons: first, we reduce the CPU overhead in compiling new plans, and second, the size of the procedure cache is kept as small as possible. A smaller procedure cache enables a larger data cache, effectively boosting RAM and reducing disk I/O.

The most effective way of controlling the growth of the procedure cache is reducing the incidence of unparameterized ad hoc queries.

Ad hoc queries

A common attribute among poorly performing SQL Server systems with a large procedure cache is the volume of ad hoc SQL that's submitted for execution. For example, consider the following queries:[]

[] In the following examples, we'll run DBCC FREEPROCCACHE before each set of queries to clear the contents of the procedure cache, enabling the cache usage to be easily understood.

    SELECT * FROM Production.Product WHERE ProductNumber = 'FW-5160'
    SELECT * FROM Production.Product WHERE ProductNumber = 'HN-1220'
    SELECT * FROM Production.Product WHERE ProductNumber = 'BE-2908'

All three of these queries are exactly the same with the exception of the ProductNumber parameter. Using the sys.dm_exec_cached_plans and sys.dm_exec_sql_text views and functions, let's inspect the procedure cache to see how SQL Server has cached these queries. Figure 1 displays the results.

Figure 1. Unparameterized SQL results in multiple ad hoc compiled plans.

As per figure 1, SQL Server has stored one ad hoc compiled plan for each query, each of which is 24K in size, totaling ~73K. No real problems there, but let's imagine these queries were executed from a point-of-sales system used by a thousand users, each of which executed a thousand such queries a day. That's a million executions, each of which would have a 24K compiled plan totaling about 24GB of procedure cache!

There are a few things to point out here. First,  the procedure cache in a 32-bit system is limited to the 2-3GB address space, with AWE-mapped memory above 4GB accessible only to the data cache. This places a natural limit on the amount of space consumed by the procedure cache in a 32-bit system; however, a 64-bit system has no such limitations (which is both good and bad, and we'll come back to this shortly). Further, SQL Server ages plans out of cache to free up memory when required, based on the number of times the plan is reused and the compilation cost; that is, frequently used expensive plans will remain in cache longer than single-use low-cost plans.

The other point to note about figure 17.7 is the fourth row, which lists a Prepared plan with three usecounts. This is an example of SQL Server's simple parameterization, a topic we'll come back to shortly.

Arguably the most effective way to address the problem we just examined is through the use of stored procedures.

Stored procedures

Consider the code in listing 1, which creates a stored procedure used to search products and then executes it three times for the same product numbers as used earlier.

Example 1. Stored procedure for parameterized product search
-- Parameterized Stored Procedure to control cache bloat
CREATE PROCEDURE dbo.uspSearchProducts
   @ProductNumber nvarchar(25)

   FROM Production.Product
   WHERE ProductNumber = @ProductNumber

EXEC dbo.uspSearchProducts 'FW-5160'
EXEC dbo.uspSearchProducts 'HN-1220'
EXEC dbo.uspSearchProducts 'BE-2908'

Figure 2 shows the results of reexamining the procedure cache. We have a single plan for the stored procedure with three usecounts and, more importantly, no ad hoc plans. Compare this with the results in figure 1, where we ended up with three ad hoc plans, each of which has a single usecount. Each execution of the stored procedure effectively saves 24K of procedure cache; one can imagine the accumulated memory saving if this was executed thousands of times each day.

Figure 2. Parameterized stored procedure executions avoid ad hoc plans, resulting in a smaller procedure cache.

Depending on the parameter values passed into a stored procedure, caching the execution plan for subsequent reuse may be undesirable. Consider a stored procedure for a surname search. If the first execution of the procedure received a parameter for SMITH, the compiled plan would more than likely use a table scan. A subsequent execution using ZATORSKY would therefore also use a table scan, but an index seek would probably be preferred. Similar issues occur in reverse, and this is frequently called the parameter-sniffing problem.

In cases where stored procedures receive a wide variety of parameter values, and therefore plan reuse may not be desirable, one of the options for avoiding this issue is to create the procedure with the WITH RECOMPILE option, which will ensure the plan is not cached for reuse. That is, each execution will recompile for the supplied parameter value(s), incurring additional compilation costs in order to derive the best possible plan for each execution. Alternatively, the procedure can be defined without this option, but an individual execution can supply it, for example, EXEC dbo.uspSearchProducts 'AB-123' WITH RECOMPILE.


The SET STATISTICS XML ON command,  is ideal in diagnosing undesirable parameter-sniffing problems. For a given stored procedure execution, the <ParameterList> element includes both the compiled parameter(s) and the runtime parameter(s). If a given procedure appears to be executing slower than expected, you can compare the compiled and runtime parameters, which may reveal significant differences resulting in inappropriate index usage.

In dealing with ad hoc/parameterization problems such as we've just covered, the nature of the application usually determines the available options for improvement. For example, while an in-house-developed application could be modified to use stored procedures, an off-the-shelf vendor-supplied application cannot. In dealing with these cases, we have a number of options including Forced Parameterization and Optimize for Ad Hoc Workloads.

Forced Parameterization

In figure 1, shown earlier, we inspected the procedure cache after executing three SQL statements and found that three ad hoc plans with single usecounts were created in addition to a single prepared plan with three usecounts. This is an example of SQL Server's simple parameterization mechanism. This mechanism has detected that the three statements are essentially the same, with the only difference being the product number parameters, and therefore share the prepared plan. The three ad hoc plans are referred to as shell plans and point to the full prepared plan. The shells are saved for later executions of exactly the same statement, which reuses the same prepared plan. The key word here is exactly; a single character difference, for example, an extra space, is enough to cause a new plan to be created.

Simple parameterization is exactly that. There are many conditions in which simple parameterization cannot be used. For example, consider the three queries in listing 2.

Example 2. Queries that cannot be parameterized with simple parameterization
SELECT * FROM Production.Product WHERE left(ProductNumber, 3) = 'FW-'
SELECT * FROM Production.Product WHERE left(ProductNumber, 3) = 'HN-'
SELECT * FROM Production.Product WHERE left(ProductNumber, 3) = 'BE-'

The use of the left function means these three commands cannot be parameterized using simple parameterization. Let's inspect the procedure cache to see how SQL Server has cached these queries. Figure 3 displays the results. In this case, there is no prepared plan, with each ad hoc compiled plan containing the full plan; notice that the size of each plan is larger than the shell plans from the earlier simple parameterization example in figure 17.7. If queries such as these were executed many times per day, we would have an even worse problem than the one described earlier.

In SQL Server 2005, an option called Forced Parameterization was introduced, which is more aggressive in its parameterization. Each database contains a Parameterization property, which by default is set to Simple. Setting this value to Forced, through either Management Studio or using the ALTER DATABASE [dbname] SET PARAMETERIZATION FORCED command, will parameterize queries more frequently. For example, after enabling this property, rerunning the three queries from listing 2 will reveal a procedure cache, as shown in figure 4.

Figure 3. Queries of moderate complexity cannot be parameterized using simple parameterization.

Figure 4. After we enabled the Forced Parameterization option, the same three queries are parameterized.

In this case, the three ad hoc plans are created as shell plans witha link to the full prepared plan. Subsequent queries of the same form will also benefit from reusing the prepared plan, thereby reducing compilation and plan cache size, which in turn enables more efficient use of both RAM and CPU resources.

Despite the (possible) plan reuse, both forced and simple parameterization still cache the ad hoc plans, which for systems containing a very large number of single-use ad hoc queries presents a real problem in containing procedure cache size. In addressing this issue, SQL Server 2008 introduces Optimize for Ad Hoc Workloads.

Optimize for Ad Hoc Workloads

In the worst examples, single-use unparameterized ad hoc plans can consume a very significant percentage of memory. For 64-bit instances, this is a particular problem, as the procedure cache has full access to all of the instance's memory. As a result, a very large procedure cache directly impacts the size of the data cache, leading to more and more disk I/O and significant cache churn.

Perhaps the most frustrating part of this for a DBA is that the ad hoc plans may never be used more than once. Forced parameterization may help in this regard but comes with some downsides, as we just covered.


In order to prevent ad hoc plans from bloating the procedure cache, some DBAs manually execute (or schedule) the DBCC FREEPROCCACHE command, which empties the procedure cache. Often seen as a hack, not only does this throw out the "bad" plans, it also throws out the good (and possibly expensive) plans with high reuse from all databases in the instance. Two alternatives to this method are running DBCC FLUSHPROCINDB (which removes plans only for a specified database) and DBCC FREESYSTEMCACHE('SQL Plans'), which clears out the ad hoc and prepared plans, leaving stored procedure plans in place.

The Optimize for Ad Hoc Workloads option is designed for exactly these situations. Enabled at a server level, this option detects ad hoc SQL and stores a simple stub in place of a plan. Should the same query be run a second time, the stub is upgraded to a full plan. As a result, the memory footprint of single-use ad hoc SQL is dramatically reduced.

After executing sp_configure 'Optimize for ad hoc workloads', 1 and putting our database back into the simple parameterization mode, we reran our three queries from listing 2. After executing these queries, we inspected the procedure cache, the results of which are shown in figure 5.

Figure 5. After we enabled the Optimize for Ad Hoc Workloads option, ad hoc queries are stubbed and have no saved query plan.

There are a few things to point out here. First, note the cacheobjtype column for the three queries. Instead of Compiled Plan, we have Compiled Plan Stub. Second, note the size_in_bytes value (336 bytes vs. ~ 57,000/82,000 bytes in figure 17.9). Third, the join to the sys.dm_exec_query_plan function reveals the absence of a stored query plan.

What's happening here is that SQL Server detects these queries as ad hoc and not parameterized and therefore does not store a plan; however, it stores the stub in order to detect subsequent executions of the queries. For example, let's reexecute the first query from listing 17.4 and take another look at the procedure cache. The results are shown in figure 6.

Note the difference in the size_in_bytes, cacheobjtype, usecounts, and query_plan columns for the first query that we reexecuted. By saving the stub from the first execution, SQL Server is able to detect subsequent executions as duplicates of the first. Thus, it upgrades the plan from a stub to a full plan on the second execution, with the third (and subsequent) executions able to reuse the saved plan.

For environments containing large amounts of single-use ad hoc SQL, the Optimize for Ad Hoc Workloads option is a very significant new feature. Not only does it dramatically reduce the size of the procedure cache, but it still enables plan reuse in cases where identical ad hoc queries are executed many times, therefore also reducing CPU-related compilation pressure.

In closing our section on procedure cache usage, let's look at a number of techniques for measuring the cache contents and plan reuse.

Measuring procedure cache usage

The T-SQL code in listing 3 summarizes the contents of the sys.dm_exec_ cached_plans DMV. It lists the number of plans for each object type (ad hoc, prepared, proc, and so forth) along with the number of megabytes consumed by such plans and the average plan reuse count.

Example 3. Summary of procedure cache
-- Summary of Procedure Cache Contents
        objtype as [Object Type]
        , count(*) as [Plan Count]

Figure 6. Rerunning an ad hoc query converts the stub into a full plan.

, sum(cast(size_in_bytes as bigint))/1024/1024 as [Total Size (mb)]
        , avg(usecounts) as [Avg. Use Count]
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype

Procedure caches suffering from a high volume of ad hoc SQL typically have a disproportionate volume of ad hoc/prepared plans with a low average use count. Listing 4 determines the size in megabytes of such queries with a single-use count.

Example 4. Size of single-use ad hoc plans
-- Procedure Cache space consumed by AhHoc Plans
    SELECT SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS
         [Size of single use adhoc sql plans]
    FROM sys.dm_exec_cached_plans
        objtype IN ('Prepared', 'Adhoc')
        AND usecounts = 1

From a waits perspective, the RESOURCE_SEMAPHORE_QUERY_COMPILE wait type is a good indication of the presence of query compilation pressure. SQL Server 2005 introduced a throttling limit to the number of concurrent query compilations that can occur at any given moment. By doing so, it avoids situations where a sudden (and large) amount of memory is consumed for compilation purposes. A high incidence of this wait type may indicate that query plans are not being reused, a common problem with frequently executed unparameterized SQL.

Another method for determining plan reuse is measuring the following Performance Monitor counters:

  • SQL Server SQL Statistics:SQL Compilations/Sec

  • SQL Server SQL Statistics:SQL Re-Compilations/Sec

  • SQL Server SQL Statistics:Batch Requests/Sec

With these counter values, we can measure plan reuse as follows:

Initial Compilations = SQL Compilations/Sec - SQL Recompilation/Sec
Plan Reuse = (Batch Req/sec - Initial Compilations) / Batch Req/sec

In other words, of the batch requests coming in per second, how many of them are resulting in query compilations? Ideally, in an OLTP system, this should be less than 10 percent, that is, a 90 percent or greater plan reuse. A value significantly less than this may indicate a high degree of compilations, and when observed in conjunction with significant RESOURCE_SEMAPHORE_QUERY_COMPILE waits, it's a reasonable sign that query parameterization may well be an issue, resulting in higher CPU and memory consumption.

Poor plan reuse not only has a direct impact on available RAM, but it also affects CPU usage courtesy of higher amounts of compilations. In the next section, we'll address CPU pressure from a general perspective.

Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer