SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Plan Cache


SQL Plan Cache

Author
Message
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
Hi All

I've been using the below script to check my Plan Cache for Ad-hoc plans

SELECT objtype 
, usecounts
, COUNT(*) AS [no_of_plans]
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND usecounts = '1'
GROUP BY objtype , usecounts
ORDER BY objtype , usecounts



How do I know what I high number is for my system?

I've also used the below script to check Compilations/sec
select * from sys.dm_os_performance_counters
where counter_name like '%compilations%'



My system reports an amount around 622364035 - Is this normal?, it seems high

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94145 Visits: 33010
That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
Grant Fritchey (8/15/2012)
That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.


Thanks

To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

Thanks
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
Grant Fritchey (8/15/2012)
That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.


How do I know when my Cache is bloated? And cannot take anymore

Does it depend on my memory available or is it dependant on CPU?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212813 Visits: 46259
SQLSACT (8/15/2012)
To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

Thanks


No. Ad-hoc SQL statements have their plans cached and reused just like stored procedures Only difference is the matching is on statement text not object id.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
GilaMonster (8/16/2012)
SQLSACT (8/15/2012)
To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

Thanks


No. Ad-hoc SQL statements have their plans cached and reused just like stored procedures Only difference is the matching is on statement text not object id.


Thanks

Regarding the 3 select statements below, would SQL use the same plan for the first 2 and a different plan for the 3rd one?


select Col1 from Table1 where Col1 = '10';
select Col1 from Table1 where Col1 = '10';
select Col1 from Table1 where Col1 = '12';


GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212813 Visits: 46259
In that trivial case, if they are submitted in 3 separate batches, there will be one plan (autoparameterised) used 3 times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
GilaMonster (8/16/2012)
In that trivial case, if they are submitted in 3 separate batches, there will be one plan (autoparameterised) used 3 times.


Thanks

What would cause SQL Server to generate a seperate plan for the 3rd select statement?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212813 Visits: 46259
A query complex enough that it doesn't qualify for autoparameterisation (and this is the point where you go to BoL to see what autoparam is)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 2969
GilaMonster (8/16/2012)
A query complex enough that it doesn't qualify for autoparameterisation (and this is the point where you go to BoL to see what autoparam is)


Will do, thanks

Please help me understand something

I've executed those select scripts seperately. Then I ran the below script to check the cache:
SELECT TOP ( 100 ) [text] 
, cp.size_in_bytes
, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
WHERE cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc'
ORDER BY cp.size_in_bytes DESC



Results attached

Then I ran this script to check Query Stats:
SELECT 
SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset ) / 2 ) + 1)
AS statement_text,
last_execution_time,
execution_count ,
statement_start_offset AS stmt_start_offset ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads ,
t.text,
qp.query_plan
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp
where SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset ) / 2 ) + 1) like '%Col1%'
ORDER BY avg_physical_reads DESC



Results attached

The cached_plan script shows 2 plans were generated, one plan shows a usecount of 2 and the other plan shows a usecount of 1

The Query_stats shows one plan with an execution count of 3

Which one is correct here or am I missing something?

Thanks
Attachments
Cache Result.JPG (7 views, 17.00 KB)
Query Stats Result.JPG (13 views, 18.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search