THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

Viewing how much memory is used by not reused query plan

One interesting observation that may help developers get convinced that they should parametrize query and that they must check that the ORM they use does it correctly is show how much memory can be wasted by plans that cannot be effectively reused.

The following query can help on this:

with cte as (
    select
        reused = case when usecounts > 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end,
        size_in_bytes,
        cacheobjtype,
        objtype
    from
        sys.dm_exec_cached_plans
), cte2 as
(
    select
        reused,
        objtype,
        cacheobjtype,
        size_in_mb = sum(size_in_bytes / 1024. / 1024.)
    from
        cte
    group by
        reused, cacheobjtype, objtype
), cte3 as
(
    select
        *
    from
        cte2 c
    pivot
        ( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p
)
select
    objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb])
from
    cte3
group by
    objtype, cacheobjtype
with rollup
having
    (objtype is null and cacheobjtype is null) or (objtype is not null and cacheobjtype is not null)

The result is something like this:

image

as you can see this server (SQL Server 2005 SP2) is using near 1.8GB of memory for Plan Caching and one third is memory that contains plans that are never reused (column usecount = 1 as reported by DMV sys.dm_exec_cached_plans)

Monitoring the result of this query can also show how much impact the usage of the new (SQL Server 2008) “Optimize for ad-hoc workload” option can have on a system.

PS

I’ve also updated my sys2dmv project on CodePlex with this new script:

http://sys2dmvs.codeplex.com/

Published Friday, July 23, 2010 11:48 AM by Davide Mauri
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Dubois said:

Thanks Davide, ... Is that gigabytes or megabytes?

July 24, 2010 6:09 AM
 

Uri Dimant said:

July 25, 2010 2:56 AM
 

Uri Dimant said:

July 25, 2010 2:56 AM
 

Davide Mauri said:

@David Dubois

Megabytes

@Uri

Thanks URI I wasn't aware of Kimeberly's article. Thanks!

July 25, 2010 5:14 AM
 

AaronBertrand said:

On my system where we use a lot of ad hoc sql due to requirements, I've switched to optimize for ad hoc workloads and parameterization forced.  I have 4.2 GB reused and 395 MB not reused; not as bad as I expected.  I imagine it was swinging the other way a year ago.

http://sqlblog.com/files/folders/27294/download.aspx

July 26, 2010 9:57 AM
 

Davide Mauri said:

@Aaron

Interesting observations! 4.2Gb of reused plan sounds pretty good! How much memory has your server?

July 26, 2010 12:18 PM
 

AaronBertrand said:

This server is x64, 16 cores with 32 GB RAM.  Memory is pegged but CPU hovers around 10%.

July 26, 2010 1:06 PM
 

TheSQLGuru said:

I have a client with REALLY bad old ADOc looping code and have a job set up that runs every 30 minutes to flush the plan cache to avoid serious bloating (still on early version of SQL 2005 so plan cache can take up a very large fraction of RAM).  

August 2, 2010 11:55 AM
 

Davide Mauri said:

!?!?!?!?!? Arght!!!!!!!! They should switch to SQL 2008 immediatly (or try to fix the application, but I guess the migrating to 2008 is less expensive :))

August 2, 2010 12:11 PM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement