Saturday, July 7, 2007 6:50 AM tonyrogerson

Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Two of the biggest memory eaters of SQL Server cache are Data and Procedure Cache, Data cache offers us the advantage of hopefully not having to go to the disks to get our data because we already did that and its cached. One of the problems on real systems is that the procedure cache can grow really big and take data cache away resulting in you having to go to the disk more, one example is a client of mine I do support for, they are on SQL 2000 standard so that limits our memory to 1.7GB, the procedure cache alone was taking in the region of 700Mbytes. You can’t directly control how big the Procedure Cache grows but by good application design we can influence it.

How do we influence Procedure Cache growth? The answer is simple; we use Parameterisation and/or using Stored Procedures.

Let’s look at two examples; they need to be run on a SQL Server that nobody else is using so as not to influence the results.

dbcc freeproccache

go


declare
@t datetime

set @t = getdate()

 

set nocount on

 

declare @i int

declare @f int

declare @sql nvarchar(max)

set @i = 0

 

while @i <= 100000

begin

    set @sql = 'select @f = count(*) from sys.objects where object_id = ' + cast( @i as varchar(10) )

    exec sp_executesql @sql, N'@f int output', @f output

 

    set @i = @i + 1

 

end

 

print datediff( second, @t, current_timestamp )

Essentially every query I execute is different because I’m building my SQL string using constants rather than using parameters.

Let’s take our memory readings:

dbcc memorystatus

CACHESTORE_SQLCP (Total)     KB

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

SinglePage Allocator         1178608

 

Procedure Cache                Value

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

TotalProcs                     18286

TotalPages                     148794

InUsePages                     41

Buffer Distribution            Buffers

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

Stolen                         2592
Free                           7191
Cached                         150151
Database (clean)               101300
Database (dirty)               910
I/O                            0
Latched                        0

In summary, the script took 371 seconds to run, the procedure cache is 1.1GBytes, there are 18,286 plans in cache, and there are 7191 pages (57Mbytes) free for the data cache.

Let’s do the same test (logically) but parameterise it.

dbcc freeproccache

go

 

declare @t datetime

set @t = getdate()

 

set nocount on

 

declare @i int

declare @f int

declare @sql nvarchar(max)

set @i = 0

 

set @sql = 'select @f = count(*) from sys.objects where object_id = @i'

 

while @i <= 100000

begin

    exec sp_executesql @sql, N'@f int output, @i int', @f output, @i

 

    set @i = @i + 1

 

end

 

print datediff( second, @t, current_timestamp )

go

Use DBCC MEMORYSTATUS to take the readings again...

CACHESTORE_SQLCP (Total) KB

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

SinglePage Allocator     768

 

Buffer Distribution            Buffers

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

Stolen                         938

Free                           157450

Cached                         1546

Database (clean)               101300

Database (dirty)               910

I/O                            0

Latched                        0

 

Procedure Cache                Value

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

TotalProcs                     15

TotalPages                     183

InUsePages                     13

In summary, the script took 5 seconds to run, the procedure cache is 768Kbytes, there are 15 plans in cache, and there are 157450 pages (1.2GBytes) free for the data cache.

Surprised? It’s one of the reasons DBA’s keep bleating on about using stored procedures, it forces the mindset to use the procedure cache more effectively. By parameterising, but preferably using stored procedures we get plan reuse which means a) less compiles thereby reducing CPU load, b) more available pages for the data cache thereby reducing physical disk IO and c) DBA’s are always right ;).

Check out your own procedure cache size and see how big it is in proportion to the data cache.

How do you find these non-parameterised queries? You can look in master..syscacheobjects.

select *

from master..syscacheobjects

 

For our last test, the parameterised one you will find a single Compiled Plan that has a usecounts of 100,001; whereas you check the first version you will see thousands of Compiled Adhoc Plans with a usecounts of 1.

Another method is to use profiler and see what queries are embedded or dynamically built and not parameterised.

We are stuck with this design decision at the moment, you can’t control the size of the procedure cache, you can only influence it with application design. In a real system it's not going to be clear cut like my example, in reality the size of the data cache and procedure cache will depend on what is going on; for instance, a lot of those one off plans would have been kicked out of cache and their pages reused for data, but, that all takes resource!

 

Filed under:

Comments

# Interesting Finds: July 7, 2007

Saturday, July 7, 2007 3:31 PM by Jason Haley

# re: Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Monday, July 9, 2007 9:49 AM by Colin Leversuch-Roberts

as I'm actually working with an app which doesn't use stored procs this is an issue I'm examining - my proc cache varies between 800mb and 2.1gb, always a problem with production systems in that it can be difficult to reproduce the same in test and then work to resolve it. It's a shame so many applications, and there are some big ones out there, are so rubbish in their code generation.

I figure 64 bit and lots of memory is the easiest way solve this in the short term - what do you think?

# re: Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Monday, July 9, 2007 9:56 AM by tonyrogerson

That or DBCC FREEPROCCACHE on a scheduled job ;).

I wonder if that is a workable solution; a) would it be supported by MS and b) the extra resource required to recompile the plans, but if all your plans are usecounts 1 then that isn't a problem anyway.

The sizing issue is actually very complex, its down to page life and all that jaz; really, they should be being kicked out of procedure cache as more data pages are needed, but data pages and proc cache pages will be at odds with one another.

Interesting dilema.

Tony.

# re: Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Monday, July 9, 2007 12:11 PM by Colin Leversuch-Roberts

oh you wouldn't believe how this application works,  we're using plan guides to force recompilation of a large number of queries but I've still got a large number of plans - trouble is when the proc cache gets under presssure then it takes space away from the data cache which in turn increases physical io and gives me a page life expectancy of almost zero. Actually have quite high re-use as the app does it's best to parameterise all the sql - it's an interesting area made all the more detailed in 2005 with all the dmv's - it's just sorting through the documentation or lack of it.

# SQL Server Performance Bugbears

Wednesday, July 11, 2007 3:48 PM by Tony Davis

Whilst trying out Robyn and Phil's excellent TSQL Newsfeed, some of the first articles that I saw was...

# Query ad-hoc con parametri vs query ad-hoc senza parametri

Thursday, July 26, 2007 12:52 PM by Impedance Mismatch

Tony Rogerson questo mese ha fatto una serie di post davvero interessanti (come già vi sarete accorti

# re: Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Friday, September 28, 2007 5:59 AM by masri999

I would like to know how to parmeterize in in adhoc statements (not SPs) , where  statement  was formed dynamically

For example , statements like

select account_id,amount from account where account_id  in (1)

select account_id,amount from account where account_id  in (100,105)

select account_id,amount from account where account_id  in (500,504,505,508,550,1125)

select account_id,amount from account where account_id  in (2000,2010,505,508,550,1125)

Values in IN clause are sent from client page . Currently I see different plans  using sp_executesql

# re: Procedure Cache Tuning/Sizing - From 1GByte to 768KBytes - Increase the size of usable Data Cache

Friday, September 28, 2007 8:32 AM by tonyrogerson

See my other blog post about passing array's (CSV) to a stored procedure...

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx

You can't parameterise the IN clause unless you use a parameter per value which isn't a very good idea.

Tony.

# Clearing your ad-hoc SQL plans while keeping your SP plans intact

Wednesday, September 24, 2008 3:56 AM by Maciej Pilecki - SQL Server lessons from the field

The problem of the procedure cache being inflated with ad-hoc SQL plans that are almost never reused

# In-Memory Caching: Why We Can't Just Trust the Database to get it Right

Sunday, November 9, 2008 12:46 PM by Dare Obasanjo aka Carnage4Life