The SQL Server Procedure Cache

There’s a lot to think about while fishing, and if you’re not giving it your full attention, you’re going to miss some fish. Muskie fishing in particular adds some additional things to focus on that you may not have to think of while fishing for other species of fish. The first is the “figure eight”. At the end of each retrieve you should smoothly transition into the figure eight by reeling until the beginning of your leader is almost to the end of your rod, then start pulling the lure in circles next to the boat. This gives any muskies that are following another chance to decide to bite. You want to make sure you transition into the figure eight in a smooth motion so the movement of the lure appears natural to the following fish. Many muskies are caught on figure eights each year. Another thing to focus on is lure speed. It’s easy to get in a “cruise control” mode and just crank away at a comfortable pace, but lure speed is a key component. Often times the muskie are lethargic and you need to slow down your presentation to elicit a strike. At other times you’ll want to really burn that lure through the water to try and provoke a reaction strike from the fish. In addition to these two details there are dozens of other things that you should be concentrating on if you want to be the most successful fisherman that you can be.

SQL Server has a lot of things to concentrate on too. One way that it tries to work more efficiently is through the use of the Procedure Cache. This is an area in memory where SQL Server holds query execution plans. This prevents SQL Server from having to build a new execution plan every time a query is run. Think of stored procedures that are run several times a minute – not having to create a new execution plan each time saves SQL Server a LOT of overhead.

There are two parts of an execution plan – the query plan and the execution context. The query plan is the data structure used by the query. This would contian the instructions with the objects that are being used in the query, such as tables, views, etc. There can be up to two copies of a query plan – one for serial executions and one for parallel executions. No matter the degree of parallelism, their is only one parallel execution plan. The exeuction context stores the variables or parameters used in the execution of a query.

Aside from the Procedure Cache, the other main component being stored in memory is the Data Buffer. SQL Server pulls data from the disk into memory for faster read and write operations. Knowing this, let’s think about the lifespan of these execution plans. We want to make sure SQL Server has memory available for incoming data requests and incoming query plans. This means there needs to be a mechanism to remove plans that no longer need to be kept in the Procedure Cache. SQL Server has an interesting way to keep more expensive and often used plans in the Procedure Cache while pushing out plans that are cheap or infrequently used.

When an execution plan is generated and inserted into the Procedure Cache, a cost is assigned to the plan based on the cost to compile the execution plan. Every time the execution plan is used the cost in the Procedure Cache is reset to the original cost needed to compile the plan. If SQL Server runs out of free memory, it realizes it needs to push some of the execution plans out of the Procedure Cache. To do this is examines each plan one at a time. If the cost of the plan is greater than zero, the cost is decrimented. If the cost of the plan reaches zero, it is eliminated from the Procedure Cache. Once enough plans have been pushed out of the cache, the process stops and the new execution plan is inserted into the Procedure Cache. Additionally, any invalid plans are removed from the cache. I’ll describe some different ways a plan can become invalid a bit later. One exception to this process is Ad-hoc plans. No matter the cost to compile an Ad-hoc plan, it is always inserted into the Procedure Cache with a cost of zero. This means the plans will always be eliminated from the Procedure Cache first, and the plans will usually not be reused.
We can use this query to view the plans and details about them in the Procedure Cache:

        PC.cacheobjtype ,
        PC.objtype ,
        MCE.original_cost ,
        MCE.current_cost ,
        QS.creation_time ,
        QS.last_execution_time ,
        QS.execution_count ,
        DB_NAME(ST.dbid) ,
       ST.TEXT ,
FROM    sys.dm_exec_cached_plans PC
        INNER JOIN sys.dm_os_memory_cache_entries MCE ON MCE.memory_object_address = PC.memory_object_address
        INNER JOIN sys.dm_exec_query_stats QS ON QS.plan_handle = PC.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
        CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP

As a warning, the Procedure Cache can contain a LOT of records. It is a good idea to limit your results with the TOP operator.
20150331 Proc Cache Query View
From this screenshot you can see the original cost and current cost are the same. This is because of the very low amount of activity on my Sandbox environment, I don’t have any memory pressure.
To demonstrate some of the ways an execution plan can become invalid, let’s create some examples. First I’ll build a little table and fill it with some data:

CREATE TABLE dbo.FavoriteLakes
      LakeName VARCHAR(50) ,
      Acreage DECIMAL(8, 2) ,
      Rating INT ,
      PrimarySpecies VARCHAR(50) ,
      SecondarySpecies VARCHAR(50)
INSERT  INTO dbo.FavoriteLakes
        ( LakeName, Acreage, Rating, PrimarySpecies, SecondarySpecies )
VALUES  ( 'Lake Tomahawk', 3462, 4, 'Walleye', 'Smallmouth Bass' ),
        ( 'Big Arbor Vitae', 1070, 3, 'Walleye', 'Muskie' ),
        ( 'Pewaukee', 2437, 3, 'Muskie', 'Largemouth Bass' ),
        ( 'Okauchee', 1210, 4, 'Largemouth Bass', 'Muskie' ),
        ( 'Random Lake', 212, 1, 'Largemouth Bass', 'Muskie' ),
        ( 'Two Sisters Lake', 719, 3, 'Walleye', 'Muskie' )

Next I’ll create a stored procedure to get data from that table.

    @FishSought VARCHAR(50)
        SELECT  *
        FROM    dbo.FavoriteLakes
        WHERE   PrimarySpecies = @FishSought
                OR SecondarySpecies = @FishSought
        ORDER BY Rating DESC

I’ll also run the SELECT statement outside of the stored procedure to create an Ad-Hoc plan.

FROM    dbo.FavoriteLakes
WHERE   PrimarySpecies = 'Muskie'
        OR SecondarySpecies = 'Muskie'

After executing a few times with a few different values, here’s what I see in my plan cache:
20150331 Proc Cache SP versus adhoc
To see the stored procedure get invalidated, I’m going to use Profiler and choose only the SQL:StmtRecompile event.
20150331 Proc Cache Profiler Event
The event subclass will contain a number that corresponds to these recompile reasons in this chart from Microsoft:
20150331 Proc Cache recompile reasons
I ran a few statements to cause the stored procedure to recompile:

--Add column to change the Schema
EXEC dbo.GetMyFavoriteLakes @FishSought = 'Largemouth Bass'

--Add index to change the schema
CREATE INDEX IX_Fish ON dbo.FavoriteLakes (PrimarySpecies ASC, SecondarySpecies ASC)
EXEC dbo.GetMyFavoriteLakes @FishSought = 'Muskie'

--Update the Statistics on the table
INSERT INTO dbo.FavoriteLakes
        ( LakeName ,
          Acreage ,
          Rating ,
          PrimarySpecies ,
VALUES  ( 'Bearskin' , 403 , 3 , 'Muskie' , 'Smallmouth Bass' ),('Boom Lake',437,4,'Muskie','Walleye'),('Crescent Lake',626,3,'Muskie','Largemouth Bass'),
       ('Moen Lake',460,4,'Muskie','Largemouth Bass'),('Hancock Lake',259,3,'Muskie','Yellow Perch'),('Lake Thompson',382,4,'Walleye','Muskie')
UPDATE STATISTICS dbo.FavoriteLakes 
EXEC dbo.GetMyFavoriteLakes @FishSought = 'Smallmouth Bass'

I was able to see the recompilations in Profiler:
20150331 Proc Cache Recompiles in Profiler
That is an overview of the Procedure Cache in SQL Server