Tuesday, May 10, 2011

T-SQL Tuesday #18

T-SQL Tuesday #18

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;


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


-- 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'


-- 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';


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.


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.


  1. Don't get me wrong, I like CTEs too, but for simple stuff like this, I perfer the elegance of one query with sub queries.

  2. Thanks so much for the great query examples and for contributing to T-SQL Tuesday!

  3. [...] Ormrod (blog | @SQLAvenger) – Steven has some queries that use CTEs to provide administrative data, including info about the buffer pool and plan [...]

  4. Hi Eric,

    Thanks for the feedback! I agree, these are simple examples. I wrote these when I first learned CTEs so they are going to be a bit more basic.

  5. Hi Bob,

    You're welcome. It was cool meeting you in class. Best of luck!

  6. [...] I am lamenting the fact that it has been almost two years since I posted on T-SQL Tuesday. In fact, the last time I did it, he was the host. I guess there must be something about [...]