Kalen Delaney : plan cache

Geek City: Plan Caching Internals

Kalen Delaney — Mon, 23 Aug 2010 17:04:00 GMT

This Friday is the last of my summer seminars. On August 27, I am presenting on plan caching, plan reuse and recompile and plan cache metadata. This is one of my favorite topics to talk about, because a lot of the information is not very widely known,...(read more)

Did You Know? I'm doing 3 more online seminars with SSWUG!

Kalen Delaney — Fri, 11 Jun 2010 22:46:00 GMT

As I told you in April , I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops...(read more)

Geek City: Clearing Plans for a Single Database

Kalen Delaney — Sat, 22 May 2010 00:46:00 GMT

I know Friday afternoon isn't the best time for blogging, as everyone is going home now, and by Monday morning, this post will be old news. But I'm not shutting down just yet, and a something came up this week that I just realized not everybody knew about,...(read more)

Did You Know? More online seminars!

Kalen Delaney — Thu, 22 Apr 2010 23:20:00 GMT

I am in Tucson again, having just recorded two more online workshops to be broadcast by SSWUG. We haven't set the dates yet, but we are thinking about offering a special package deal for the two of them. The topics really are related and I think they...(read more)

Geek City: Clearing a Single Plan From Cache in SQL Server 2008

Kalen Delaney — Fri, 31 Oct 2008 23:43:00 GMT

I just today found out about something that was in plain sight in the Books Online... but the documentation is so vast, I haven't discovered everything yet.

I blogged about clearing a single plan from cache in SQL Server 2005 a while back, and mentioned a rumor that there would be a new way to do this in SQL Server 2008. And then just today, someone posted a comment on that blog post, asking about the new 2008 method.

One of my contacts on the SQL Server team at Microsoft just sent me an email telling me about new features related to plan caching, as I am starting to work on updating that chapter for my 2008 book. He mentioned that DBCC now takes parameters, and one of the parameters is a plan handle!

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

There is full example in the Books Online, so I won't repeat it here. If you don't have a local copy of BOL handy, you can read about the new option to DBCC FREEPROCCACHE online here:

http://msdn.microsoft.com/en-us/library/ms174283.aspx

Have fun!

~Kalen

Geek City: Reducing Cache Bloat and a Metadata Bug

Kalen Delaney — Wed, 29 Oct 2008 23:43:00 GMT

I've been running some tests with the new SQL Server 2008 configuration option 'optimize for ad hoc workloads' which is intended to reduce cache bloat due to holding onto plans for single use ad hoc queries. This option will allow a new type of cached object to be stored, which isn't the only plan, but merely a stub. There will be a corresponding new cacheobjtype value called "Compiled Plan Stub".

There are several ways of looking at the contents of cache. In SQL 2000, we had a pseudo table called master.dbo.syscacheobjects, and we can still access this object in SQL 2005 and 2008 by referencing the compatibility view sys.syscacheobjects (from any database). However, when I look at this view, I don't see "Compiled Plan Stub", but rather "Compiled Plan Stu ".

So I checked the definition of syscacheobjects, using the function object_definition:

USE master;
SELECT OBJECT_DEFINITION(object_id('syscacheobjects'));

and I saw that syscacheobjects was derived from a column in sys.dm_exec_cached_plans, and converted to an nvarchar(17). Unfortunately, "Compiled Plan Stub" is 18 characters long.

I'll be talking about this new cached object type in my 3-day seminar in London next week, and I'll post more information about using the 'optimize for ad hoc workloads' at a later time.

But normally, I wouldn't have run into this problem, because I have created my own view based on the new SQL 2005 metadata, including sys.dm_exec_cached_plans. Usually, I use my own view, which converts to a longer string, so I wouldn't have seen the string truncation. But I was testing this on a new instance, where I hadn't created my own view yet.

I am including my view definition, which is what I have been using since the SQL Server 2005 came out, to inspect the plan cache. In fact, I usually add a where clause to restrict the output only to Compiled Plans. Note that the view name starting with sp_ means we can access the view from any database:

-- Create a view to show most of the same information as
--   SQL Server 2000's syscacheobjects
USE master
GO
CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec,
             uid, refcounts, usecounts, pagesused, setopts, langid, dateformat, status, lasttime,
             maxexectime, avgexectime, lastreads,lastwrites, sqlbytes, sql)
AS
SELECT
    pvt.bucketid,
    CONVERT(nvarchar(20), pvt.cacheobjtype) as cacheobjtype, pvt.objtype,
    CONVERT(int, pvt.objectid)as object_id, CONVERT(smallint, pvt.dbid) as dbid,
    CONVERT(smallint, pvt.dbid_execute) as execute_dbid,
    CONVERT(smallint, pvt.user_id) as user_id, pvt.refcounts, pvt.usecounts,
    pvt.size_in_bytes / 8192 as size_in_bytes,
    CONVERT(int, pvt.set_options) as setopts, CONVERT(smallint, pvt.language_id) as langid,
    CONVERT(smallint, pvt.date_format) as date_format, CONVERT(int, pvt.status) as status,
    CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0),
    CONVERT(bigint, 0),CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2),
    CONVERT(nvarchar(3900), fgs.text)

    FROM (SELECT ecp.*, epa.attribute, epa.value
               FROM sys.dm_exec_cached_plans ecp
                    OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa
               PIVOT (MAX(ecpa.value) for ecpa.attribute
                   IN ("set_options", "objectid", "dbid", "dbid_execute",
                       "user_id", "language_id", "date_format", "status")) as pvt
                OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs

This view shows the full cached object type:

Have fun!

~Kalen

Geek City: Q & A on my Plan Cache Sizing article in SQL Server Magazine

Kalen Delaney — Sun, 16 Dec 2007 03:00:00 GMT

My December article in SQL Server Magazine, called Managing Plan Cache Size, expands on the information I talked about in a post last month on the limits of plan cache.

A reader of the article ask some questions on the SQL Server Magazine web site, and I have decided to use my blog to post the answers.

Q: Could you give me an illustration how to calculate target memory please? Let say there is a 64 bit system (64 bit hardware, windows 2003 64 bit enterprise edition, SQL Server 2005 Enterprise edition 64 bit, memory 30 GB), max server memory (in sp_configure) is between 16 (min value) - 30 GB (max value)).

A: Target memory refers to the maximum physical memory that can be committed to the buffer pool and ideally is the lesser of the values you’ve configured for “max server memory” and the total amount of visible physical memory available to the OS. On a 64bit system, all your memory is visible, and your max server memory is the same as your physical memory, so target ideally will be 30GB. You can see the target memory value in the metadata view sys.dm_os_sys_info (where it's called bpool_commit_target), along with the value for visible memory (called bpool_visible).
However, there are other factors that can affect the target value. The BOL entry for sys.dm_os_sys_info defines bpool_commit_target as follows:
"Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters."
The exact formula is not published. It's best to just use sys.dm_os_sys_info to see what your system's target memory value is.

Q: In your article, you give an example how to calculate plan-cache pressure limit, for example for SQL Server 2005 RTM and SP1, it calculates for on 28 target memory as follows : (.75 x 8 GB) + (.5 x 20 GB) = 6 GB. Then you add to 10 GB so pressure limit is 16 GB. Where does 10 GB come from?

A: You have rephrased the expression incorrectly. This is what the article says:
(.75 x 8 GB) + (.5 x 20 GB) = 6 GB + 10 GB = 16 GB
If you do the math, you’ll see that the first term in parentheses (.75 x 8 GB) is equal to 6 GB and the second term (.5 x 20 GB) is equal to 10 GB, so we add the 6 and the 10 together.

Q. You mention "plan-cache pressure limit". Does it mean that when , let's say pressure limit of 28 GB is 16 GB, 16 GB is reached then SQL Server couldn't remove plan cache to make it less than 16 GB, is my understanding right?

A: No, in the above example, we calculated the pressure limit to be 16 GB. That means when your SQL Server hits 16 GB of memory for plan cache, it will start removing plans from cache to keep cache from getting too big.

Q: You show how to remove a prepared query by creating a plan guide with recompile hint; is it possible to remove non-prepared query plan on the following sample:

EXEC sp_create_plan_guide @name = N'RemovePlan1', @stmt = N' SELECT * FROM Sales WHERE ContactID < 3', @type = N'SQL', @module_or_batch = NULL, @params = N', @hints = N'OPTION(RECOMPILE)';

and I issue query twice as below shown SELECT * FROM Sales WHERE ContactID < 3

When I check in syscacheobjects with the query:
SELECT usecounts as uses, sql FROM sys.syscacheobjects WHERE dbid = db_id('AdventureWorks');

It shows that the non-prepared query plan is reused with count equal to 2.

A: Althought it is possible to create a plan guide for an adhoc (non-prepared) query, your @stmt parameter must be absolutely exactly the same as the query you will run. In your plan guide creation, you have an extra space in front of the SELECT, and your @module_or_batch parameter is incorrect; it should be NULL. A correct plan guide can be used for adhoc queries, but it will NOT remove the adhoc plan from cache when you drop the plan guide, like a plan guide for a prepared query will. You will have to wait for the next version (SQL Server 2008) to get that behavior.

I hope this helps!

~Kalen

Did You Know? SP2 does NOT limit the amount of plan cache you can have

Kalen Delaney — Mon, 05 Nov 2007 02:39:00 GMT

As I am preparing my talk for the UK SQL Server Users Group on November 16, I was going through other presentations on plan cache management. At the PASS Conference in Denver in September there was a session on management of cache, and I was surprised to find a slide that indicated there was a hard upper limit for plan cache size, and that the limit was actually decreased as of SQL Server 2005 SP2. This is not true. There is no upper limit to plan cache size, other than any upper limit you might have on total SQL Server memory. But usually we don't want to use too much memory for query plans, because it will reduce the memory available for data cache. In fact, there is a situation called 'plan cache bloat' where memory gets filled with very cheap adhoc plans that aren't reused, but take up memory space that could be much more effectively used for other purposes. It is to reduce this plan cache bloat that the caching management algorithm changed in SP2.

The changes do NOT enforce an upper limit. The change actually computes a plan cache size at which SQL Server recognizes that there is memory pressure, and it will start removing plans from cache. The plans to be removed are the cheap plans that have not been reused, and this is a GOOD THING.

The following explanation is based on a section in Chapter 5 of Inside SQL Server 2005: Query Tuning and Optimization, used by permission of the author. :-)

---------------------------------------------------------------------

When discussing memory pressure, we refer to the term ‘visible’ memory. Visible memory is that which is directly addressable in the SQL Server process’s virtual address space. On a 32-bit SQL Server instance, the maximum value for the visible memory is either 2 GB or 3 GB, depending on whether you have the /3GB flag set in your boot.ini file. Memory with addresses greater than 2 GB or 3 GB is only available indirectly, through AWE-mapped-memory. On a 64-bit SQL Server instance, ‘visible’ memory has no special meaning, as all the memory is directly addressable. In any of the discussion below, if we refer to visible target memory greater than 3 GB, keep in mind that is only possible on a 64-bit SQL Server. The term ‘target’ memory refers to the maximum amount of memory that can be committed to the SQL Server process. Target memory is the lesser of the value you have configured for ‘max server memory’ and the total amount of physical memory available to the operating system. So ‘visible target memory’ is the visible portion of the target memory. Query plans can only be stored in the non-AWE-mapped memory, which is why the concept of ‘visible’ memory is important. You can see a value for visible memory, specified as the number of 8-KB buffers, when you run the DBCC MEMORYSTATUS command. The section called ‘Buffer Counts’ displays values for Visible memory as well as Target memory.

SQL Server defines a ‘plan cache pressure limit’ value which varies depending on the version you’re running, and the amount of visible target memory. The formula for determining the plan cache pressure limit changed in SQL Server 2005, Service Pack 2. The table below shows how to determine the plan cache pressure limit in SQL Server 2000 and 2005, and indicates the change in Service Pack 2, which reduced the pressure limit with higher amounts of memory. Be aware that these formulas are subject to change again in future service packs.

As an example, assume we are on SQL Server 2005, Service Pack 1, on a 64-bit SQL Server instance with 28 GB of target memory. The plan cache pressure limit would be 75% of 8GB plus 50% of the target memory over 8 GB (or 50% of 20GB), which is 6GB + 10GB or 16 GB.

On SQL Server 2005, Service Pack 2, on the 64-bit SQL Server instance with 28 GB of target memory, the plan cache pressure limit would be 75% of 4GB plus 10% of the target memory over 4 GB (or 10% of 24GB), which is 3GB + 2.4GB or 5.4 GB.

---------------------------------------------------------------

There are more details about different caches and global vs local pressure, that I'm not going to go into detail about here. But to simplify, when SQL Server detects that a cache has reached 75-80% of the cache pressure limit, it will consider the SQL Server instance to be under memory pressure. When memory pressure is detected, all zero cost plans will be removed from cache and the cost of all other plans is reduced by half.

For the rest of the details, you can read the Query Tuning and Optimization book. The point I wanted to make is that there is no hard limit and having memory pressure kick in is a good thing to prevent cache bloat. I have actually heard of people not upgrading to SP2 because they thought it would mean they would have less space for plans in cache. Even under memory pressure, plans that are regularly reused will NOT be removed from cache, so all you'll be doing is getting rid of the clutter, and have more room left for the important stuff.

Have fun!

~Kalen

Geek City: Clearing a Single Plan from Cache

Kalen Delaney — Sat, 29 Sep 2007 18:18:00 GMT

Caching and reusing query plans is both a good thing and a bad thing. Saving costs of recompilation for a query that is run repeatedly can be a good thing. Using a cached plan that is not appropriate can be a very bad thing.

What do you do if you have plans in cache that should not be there?

SQL Server 2000 and 2005 provide a way to clear all the plans from plan cache with DBCC FREEPROCCACHE. There is also a way to clear all plans from a single database using DBCC FLUSHPROCINDB(<db_id>)

Rumor has it that SQL Server 2008 will provide a way to clear a single plan from cache. The details of exactly how this will be accomplished, and how you can specify which plan to remove are not available yet. However, in SQL Server 2005 there is a way to remove a single plan using the new SQL Server 2005 Plan Guide feature. This trick only works for plans where the objtype value for the plan in sys.syscacheobjects is 'prepared' and is not 'adhoc'. It is not needed for 'proc' plans, because 'proc' plans can be easily removed by simply using the ALTER PROC command. Prepared plans, whether they are created through autoparameterization, by using sp_executesql, or by using prepare and execute methods in your application can be removed by creating a plan guide that matches the parameterized query. You can then drop the plan guide if you don't want to continue using it.

Here's an example:

First create a copy of the Sales.SalesOrderHeader table in the AdventureWorks database, and then build a nonclustered, nonunique index on the new table.

USE AdventureWorks
GO
SELECT * INTO Sales FROM Sales. SalesOrderHeader
GO
CREATE INDEX Contact_indx on Sales(ContactID)
GO

Next, create a prepared query which selects all rows less than a particular very restrictive value (one that only returns a few rows.)

DBCC FREEPROCCACHE
GO
EXEC sp_executesql
N'SELECT * FROM Sales WHERE ContactID < @ID',
N'@ID int',
@ID = 3
GO

A second query, which uses a much less restrictive value, will use the same plan.

EXEC sp_executesql
N'SELECT * FROM Sales WHERE ContactID < @ID',
N'@ID int',
@ID = 200
GO

The following query verifies that the same plan was used for both queries:

SELECT usecounts as uses, sql FROM sys.syscacheobjects
WHERE dbid = db_id('AdventureWorks')
AND objtype = 'Prepared'
GO

I get the following results:

uses sql
---- --------------------------------------------------
2 (@ID int)SELECT * FROM Sales WHERE ContactID < @ID

If you realize that you don’t want this plan in cache because you don’t want to always use the plan for the nonselective value, you can create the following plan guide

EXEC sp_create_plan_guide
@name = N'RemovePlan',
@stmt = N'SELECT * FROM Sales WHERE ContactID < @ID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ID int',
@hints = N'OPTION(RECOMPILE)';

As soon as the plan guide is created, it will remove the plan for this query from cache, and you can confirm that by running the query that inspects sys.syscacheobjects.

If you leave the plan guide in the database, the RECOMPILE hint will force SQL Server to always come up with a new plan for queries that have the same form as the query in the @stmt variable. If you just want to remove the existing plan, but not keep the plan guide around, you can remove the plan guide with the following statement:

EXEC sp_control_plan_guide N'drop', N'RemovePlan'

Have fun!

~Kalen