Most of the query plans recreated in the past 4 hours

Question

I have got a problem with my SQL Server database's performance. I have found this tool sp_BlitzCache. After command's executing, I got this statement:

You have 92.00% plans created in the past 24 hours, and 92.00% created in the past 4 hours.

While I identified the problem (using SQL Server Profiler, I have checked StmtRecompile event occurrences), I was able to find only a few full-text search queries that are often rebuilt. However, full-text search queries represent just about 5% of all queries.

Do you have any suggestions what might cause the recreation of the remaining 87% plans?

I have got SQL Server 2012 (version 11.0.6567.0).

Edit: I have added my performance counters

+---------------------------+--------------------------------+--------------+
|        object_name        |          counter_name          |  cntr_value  |
+---------------------------+--------------------------------+--------------+
| SQLServer:Buffer Manager  | Background writer pages/sec    |            0 |
| SQLServer:Buffer Manager  | Buffer cache hit ratio         |        28436 |
| SQLServer:Buffer Manager  | Buffer cache hit ratio base    |        28436 |
| SQLServer:Buffer Manager  | Checkpoint pages/sec           |      8259452 |
| SQLServer:Buffer Manager  | Database pages                 |      4434337 |
| SQLServer:Buffer Manager  | Free list stalls/sec           |            9 |
| SQLServer:Buffer Manager  | Integral Controller Slope      |            0 |
| SQLServer:Buffer Manager  | Lazy writes/sec                |         5608 |
| SQLServer:Buffer Manager  | Page life expectancy           |       438901 |
| SQLServer:Buffer Manager  | Page lookups/sec               | 122694703703 |
| SQLServer:Buffer Manager  | Page reads/sec                 |     60994608 |
| SQLServer:Buffer Manager  | Page writes/sec                |    126076564 |
| SQLServer:Buffer Manager  | Readahead pages/sec            |     45305420 |
| SQLServer:Buffer Manager  | Target pages                   |    130990080 |
| SQLServer:Buffer Node     | Database pages                 |      4434337 |
| SQLServer:Buffer Node     | Page life expectancy           |       438901 |
| SQLServer:Buffer Node     | Local node page lookups/sec    |            0 |
| SQLServer:Buffer Node     | Remote node page lookups/sec   |            0 |
| SQLServer:Memory Manager  | External benefit of memory     |            0 |
| SQLServer:Memory Manager  | Connection Memory (KB)         |         3304 |
| SQLServer:Memory Manager  | Database Cache Memory (KB)     |     35474784 |
| SQLServer:Memory Manager  | Free Memory (KB)               |     13229808 |
| SQLServer:Memory Manager  | Granted Workspace Memory (KB)  |            0 |
| SQLServer:Memory Manager  | Lock Memory (KB)               |       455928 |
| SQLServer:Memory Manager  | Lock Blocks Allocated          |      1798154 |
| SQLServer:Memory Manager  | Lock Owner Blocks Allocated    |      3568588 |
| SQLServer:Memory Manager  | Lock Blocks                    |        10562 |
| SQLServer:Memory Manager  | Lock Owner Blocks              |        10617 |
| SQLServer:Memory Manager  | Maximum Workspace Memory (KB)  |     43368000 |
| SQLServer:Memory Manager  | Memory Grants Outstanding      |            0 |
| SQLServer:Memory Manager  | Memory Grants Pending          |            0 |
| SQLServer:Memory Manager  | Optimizer Memory (KB)          |         1400 |
| SQLServer:Memory Manager  | Reserved Server Memory (KB)    |            0 |
| SQLServer:Memory Manager  | SQL Cache Memory (KB)          |       229112 |
| SQLServer:Memory Manager  | Stolen Server Memory (KB)      |      8063232 |
| SQLServer:Memory Manager  | Log Pool Memory (KB)           |         4192 |
| SQLServer:Memory Manager  | Target Server Memory (KB)      |     56934400 |
| SQLServer:Memory Manager  | Total Server Memory (KB)       |     56767824 |
| SQLServer:Memory Node     | Database Node Memory (KB)      |     35474784 |
| SQLServer:Memory Node     | Free Node Memory (KB)          |     13229808 |
| SQLServer:Memory Node     | Foreign Node Memory (KB)       |            0 |
| SQLServer:Memory Node     | Stolen Node Memory (KB)        |      8063208 |
| SQLServer:Memory Node     | Target Node Memory (KB)        |     56934376 |
| SQLServer:Memory Node     | Total Node Memory (KB)         |     56767800 |
+---------------------------+--------------------------------+--------------+

Answer 1

The query used to test plan creation time is this

WITH x AS (
SELECT SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 24 THEN 1 ELSE 0 END) AS [plans_24],
       SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 4 THEN 1 ELSE 0 END) AS [plans_4],
       SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 1 THEN 1 ELSE 0 END) AS [plans_1],
       COUNT(deqs.creation_time) AS [total_plans]
FROM sys.dm_exec_query_stats AS deqs
)
SELECT CONVERT(DECIMAL(3,2), NULLIF(x.plans_24, 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_24],
       CONVERT(DECIMAL(3,2), NULLIF(x.plans_4 , 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_4],
       CONVERT(DECIMAL(3,2), NULLIF(x.plans_1 , 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_1],
       @@SPID AS SPID
INTO #plan_creation
FROM x
OPTION (RECOMPILE) ;

also the SP provides some clues as well on where to start your further research

If these percentages are high, it may be a sign of memory pressure or plan cache instability

Other than above clues,check if your server has been restarted .

if your server is not restarted,then below is the approach i would take

• check if your facing memory pressure

First see ,if your memory settings are configured optimally.if so ,you can use below counters to see if you are facing memory pressure

Memory: Available MB
SQL Buffer: Free Pages
SQL Buffer: Page Life
SQL Buffer: Lazy Writes

if you are facing memory pressure,then you can see and tune the queries which are using more memory or try adding more memory

you might have ran queries that causes recompilation .some of them include

• Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).

• Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).

• Changes to any indexes used by the execution plan

• Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.

• Dropping an index used by the execution plan.

you can also see this white paper for more details on Plan caching

https://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

Answer 2

To add what @TheGameiswar said you can also run this query to see the details of plans that are not obtained from cache.

;with
    xmlnamespaces (N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' as DYN)
select
    db_name(st.dbid) as DBName
    , object_schema_name(st.objectid, st.dbid) as SchemaName
    , object_name(st.objectid, st.dbid) as ObjectName
    , ecp.objtype
    , st.text
    , qp.query_plan.value('(/DYN:ShowPlanXML/DYN:BatchSequence/DYN:Batch/DYN:Statements/DYN:StmtSimple/@RetrievedFromCache)[1]', 'varchar(100)') as RetrievedFromCache
    , qp.query_plan
into #temp
from sys.dm_exec_cached_plans ecp
    outer apply sys.dm_exec_query_plan(ecp.plan_handle) qp
    outer apply sys.dm_exec_sql_text(ecp.plan_handle) st

select
    *
from #temp t
where t.RetrievedFromCache is null
    and t.DBName is not null
order by t.DBName, t.SchemaName, t.ObjectName;