Category: Buffer Pool


T-SQL Tuesday is a rotating blog series where a topic is chosen by the host, and all the participants write an article on that topic.  This month, T-SQL Tuesday is being hosted by Bob Pusateri (blog | twitter) and the topic is Common Table Expressions.

Common Table Expressions

I must admit that I am a fairly recent convert to using CTEs, or Common Table Expressions.  I learned about CTEs while preparing for Exam 70-433, SQL Server 2008 Database Development.

In fact, when I first studied the topic, it went right over my head.  Looking back on it, I think they were so sublime that I didn’t get it at first.  The way I look at it is, a CTE is basically a mini-view that you can embed directly in your queries.

Once I grokked what CTEs were, I have been applying them in numerous situations.  After I worked out a few simple examples to learn the ins and outs of CTEs, I dove right in and developed one that I use on an almost daily basis.

Buffer Pool

As a production DBA, I am constantly keeping tabs on the internals of my SQL Servers.  One area that I am particularly interested in is the Buffer Pool.  How big is it?  How much is each database using?  How many Dirty Pages do I have?  You get the idea.

I have seen various scripts from numerous sources that broke down the Buffer Pool by Database, Clean Pages, and Dirty Pages; but always as separate queries.  It bugged me to have to run multiple scripts to get my answers, so I set about to see if I could solve my problem using CTEs.  I figured if I could write a useful script using CTEs, then I should be able to answer any questions on the exam regarding this topic.

I must admit, the inspiration for the base SELECT statements for this script came from my readings of Professional SQL Server 2008 Internals and Troubleshooting.

So, without further ado, here is my script…


-- T-SQL Tuesday #18
-- CTEs - Common Table Expressions

-- define the data cache CTE
with dc as (
	select case database_id
		when 32767 then 'ResourceDB'
			else db_name(database_id)
		end as 'Database',
		count(*) * 8 / 1024 as 'Cached Size (MB)'
	from sys.dm_os_buffer_descriptors
	group by db_name(database_id), database_id
),

-- define the dirty pages CTE
dp as (
	select db_name(database_id) as 'Database',
		count(page_id) * 8 /1024 as 'Dirty Pages (MB)'
	from sys.dm_os_buffer_descriptors
	where is_modified = 1
	group by DB_NAME(database_id)
)

-- link the data cache and dirty pages CTEs together
select dc.[Database],
	dc.[Cached Size (MB)],
	coalesce(dp.[Dirty Pages (MB)], 0) as 'Dirty Pages (MB)'
from dc left join dp
	on dc.[Database] = dp.[Database]
order by dc.[Cached Size (MB)] desc;
go

Nothing too fancy here, just a basic everyday script that lets me know how much buffer pool each database is taking up, and how many pages are dirty.  While I realize it would be much cooler if I were calculating the coefficient of thermal expansion with my CTE, but being as I wrote this script on Valentine’s Day, I saved the thermal expansion for later in the day.  🙂

Plan Cache

After my initial success, I went on to write several more CTE queries for various administrative tasks.  Additionally, I started using CTEs as a way to stitch together queries in a horizontal fashion, instead of vertically by using UNIONs.

Along with the Buffer Pool, another area that I am constantly examining is the Plan Cache.  One particular slice that I like to analyze is how much memory is being used wasted on Single-Use Adhoc Query Plans.

One way I have done this is to run several queries and UNION them together.  This way I can glue my intermediate results together into one results set.  From here, I often import the results into Excel for Executive Formatting.


-- use UNIONs to query one server
-- vertical formatting

-- all plans
select 'All Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans

union

-- adhoc plans
select 'Adhoc Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans
where objtype = 'Adhoc'

union

-- single-use adhoc plans
select 'Single-Use Adhoc Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans
where usecounts = 1
and objtype = 'Adhoc';
go

What I did next, was to rewrite the queries using CTEs, which allows me to format the data Horizonally instead of Vertically.  Also, it made it easier for me to query across multiple servers and have the results formatted much closer to my final Excel format.

You may see my CTE version of this code by viewing yesterday’s blog post.

Conclusion

So there you have it.  Using Common Table Expressions to stitch and glue T-SQL together.  Now head on down to Hobby Lobby and ask them where the CTEs are.

My original plan was to publish this article on April 18th in celebration of Tax Filing Day.  However, life has a way of happening, so I filed IRSQL Form 1433 and applied for an extension.

Say Brother, can you spare me some Cache?

Have you used up all of your Plan Cache?  Would you like a refund?  Are ad-hoc queries bloating your Plan Cache?  How can you tell?  What are ad-hoc queries?  What’s a Plan Cache?

Ad-hoc Queries

Ad-hoc queries are TSQL queries that are issued directly against the database.  Typically, the query text is created directly in the application, then sent directly to the database for the data.  This is very common in web-based applications such as ASP or PHP.  However, you also see this with traditional, desktop application written in C/C++ or Java.

Contrast this with Stored Procedures.  Similar to procedures in other programming languages, Stored Procedures can be thought of as functions, methods, or procedures that are internal to SQL Server.  They are written in TSQL but stored within SQL Server.  The first time a Stored Procedure is executed, SQL Server parses and compiles it, and stores the resultant Execution Plan in the Plan Cache.  The Execution Plan tells SQL Server how it is going to get the data to satisfy the request.

The reason you want to cache Query Plans is that if you run a query once, chances are you will run it again.  Some queries may run thousands of times during the course of a day.  It all depends upon the application.

Plan Cache

The Plan Cache is the area of memory where SQL Server stores the Execution Plans for any queries or stored procedures.  Compiling and caching the execution plan does take a few extra CPU cycles away from the server, so why would you do it?  The next time that query or stored procedure is run, SQL Server already knows how to get the data in an efficient manner.  As your application scales, and your query or stored procedure is being run hundreds or thousands of time, this helps the execution to be a fast as possible.

Single-Use Plans

The goal with caching Query Plans is for the execution count be as high as possible.  This means we are getting the most bang for our buck.  However, if you have lots of single-use plans, then you are wasting CPU cycles calculating the plans, and you are wasting memory by storing them.

Every CPU cycle that is wasted compiling and caching a single-use Query Plan is a CPU cycle that could have been doing some work for your application.  Every byte of memory that is wasted storing a single-use Query Plan is memory that could have been used somewhere else.

If you have a high number of single-use plans in your cache, this could be contributing to high CPU utilization, excessive IO, or low memory on your server.

How to Find Ad-hoc Queries and Single-Use Plans

One way to find out if you have ad-hoc queries hitting your database is to ask the development teams if they have any ad-hoc queries in their code.  There are a couple of problems with this approach.

For one, they may not be aware if they are using ad-hoc queries.  If you have a large application, each developer may only familiar with a small part of it.  Also, if you have inherited an existing application, it may be difficult or impossible to wrap your head around the entire code base.

Another scenario is that the developers may be aware of some of their ad-hoc queries, but may underestimate the number of times they are being called, or they may not realize the impact they have upon the system.

As better approach is to ask SQL Server to tell you what sort of plans it has cached.

sys.dm_exec_cached_plans

SQL Server provides us with a Dynamic Management View, sys.dm_exec_cached_plans which lists out every Execution Plan that is currently stored in the Query Cache.  At the most basic level, you can query this DMV to see the details on every Query Plan in your system.


-- basic query
select * from sys.dm_exec_cached_plans

By itself, this is not very useful. Of course, like all SQL queries you can add various WHERE clauses or aggregates in order to make it more meaningful for you.

So, how much are you wasting?

The way I approached this problem was to calculate how much memory the entire Plan Cache was using, then compare that to how much the Single-Use Adhoc Plans were using.  Once I had those numbers, I did some quick math to figure out what percentage of my Plan Cache was going to Single-Use Adhoc Plans.


-- check percentage of the plan cache that is wasted on single-use adhoc plans

-- use a CTE to check across multiple servers
-- horizontal formatting
-- all plans
;with all_plans as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'All Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average KB'
	from sys.dm_exec_cached_plans
),

-- adhoc plans
adhoc_plans as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'Adhoc Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average Bytes'
	from sys.dm_exec_cached_plans
	where objtype = 'Adhoc'
),

-- single-use adhoc plans
	single_adhoc as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'Single-Use Adhoc Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average Bytes'
	from sys.dm_exec_cached_plans
	where usecounts = 1
		and objtype = 'Adhoc'
)

-- query the CTEs

-- geek version, show all the data
select ap.[All Plans Count], ap.[Total MB], ap.[Average KB],
	ah.[Adhoc Plans Count], ah.[Total MB], ah.[Average Bytes],
	sa.[Single-Use Adhoc Plans Count], sa.[Total MB], sa.[Average Bytes],
	cast(cast(sa.[Total MB] as numeric) / cast(ap.[Total MB] as numeric) * 100 as numeric(5,2)) as 'Percent Wasted on Single-Use Adhoc Plans'
from all_plans ap
join adhoc_plans ah
on ap.ServerName = ah.ServerName
join single_adhoc sa
on ah.ServerName = sa.ServerName

I also have an Executive Version, which shows the Percentage Wasted without adding in all those confusing details.  Replace the Geek Version with this one when you query the CTEs.


-- executive summary, show how much memory is being wasted on single-use adhoc plans
select ap.[Total MB] as 'All Plans MB',
	sa.[Total MB] as 'Single-Use Adhoc MB',
	cast(cast(sa.[Total MB] as numeric) / cast(ap.[Total MB] as numeric) * 100 as numeric(5,2)) as 'Percent Wasted on Single-Use Adhoc Plans'
from all_plans ap
join adhoc_plans ah
on ap.ServerName = ah.ServerName
join single_adhoc sa
on ah.ServerName = sa.ServerName

Getting Buy In

If you encounter resistance to changing this setting, run this query against multiple servers and save the results in Excel.  Bosses love Excel; it helps them think.  🙂

Even better, write up a simple Word Document or PowerPoint Presentation and tie it back to the business or the budget.  That will make an impression.

How much did you spend on memory when you built the server?  Do you see evidence of memory pressure on your server?  Are some of your key business processes running slow because they are not able to get enough memory.

One technique I have used is to put your conclusion first.  Then, lead them along the path that led you to your conclusion.  Put in a few key graphs and screenshots, and voila!  Many managers will not read beyond the first slide or paragraph so you need to work on getting your core message conveyed in a very brief space.

How to fix it

The fix is quite simple: optimize for adhoc workloads.


-- show advanced options - enables all options to be seen / set
use [master]
go
exec sp_configure 'show advanced options', 1
go
reconfigure
go

-- check to see if it is turned on
sp_configure 'optimize for ad hoc workloads'
go

-- optimize for ad hoc workloads
sp_configure 'optimize for ad hoc workloads', 1
go
reconfigure
go

-- verify that it has been set correctly
sp_configure 'optimize for ad hoc workloads'
go

This is an Instance Level setting that tells SQL Server not to compile and cache the Query Plan the first time it encounters an Adhoc Query.  Instead, it compiles a Plan Stub.

The Plan Stub is a small identifier that allows SQL Server to recognize that a particular plan has been looked at before.  This way, if it does end up getting multiple uses down the road, SQL Server is able to compile and cache the plan as normal.

For many of my servers, the Plan Stub was an Order of Magnitude smaller than the size of the full Query Plan; 320 bytes versus 25,000k-35,000 bytes for the average Single-Use Adhoc Query Plan.  Of course, your mileage may vary.

Make it so, Number One!

Something to keep in mind, is that you will not be able to predict or control what SQL Server will decide to use its newfound memory on.  Kimberly Tripp (blog | twitter) has an excellent article which discusses some of the behaviors you may observe.

Conclusion

Taking a look inside your Plan Cache can be an illuminating experience.  While you may not get any money back, it is worth looking into.