Większość planów zapytań odtworzono w ciągu ostatnich 4 godzin

Mam problem z wydajnością mojej bazy danych SQL Server. Znalazłem to narzędzie sp_BlitzCache . Po wykonaniu polecenia otrzymałem następującą instrukcję:

Masz 92,00% planów utworzonych w ciągu ostatnich 24 godzin i 92,00% utworzonych w ciągu ostatnich 4 godzin.

Podczas gdy zidentyfikowałem problem (używając SQL Server Profiler, sprawdziłem wystąpienia zdarzeń StmtRecompile), byłem w stanie znaleźć tylko kilka zapytań wyszukiwania pełnotekstowego, które są często przebudowywane. Jednak zapytania wyszukiwania pełnotekstowego stanowią zaledwie około 5% wszystkich zapytań.

Czy masz jakieś sugestie, co może spowodować odtworzenie pozostałych 87% planów?

Mam SQL Server 2012 (wersja 11.0.6567.0).

Edycja: Dodałem moje liczniki wydajności

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

Oto zapytanie użyte do przetestowania czasu utworzenia planu

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

SP również dostarcza wskazówek, jak rozpocząć dalsze badania

Jeśli te wartości procentowe są wysokie, może to oznaczać presję pamięci lub niestabilność pamięci podręcznej planu

Oprócz powyższych wskazówek sprawdź, czy serwer został zrestartowany.

jeśli twój serwer nie zostanie zrestartowany, to poniżej podjąłbym podejście

• sprawdź, czy napotykasz presję pamięci

Najpierw sprawdź, czy ustawienia pamięci są skonfigurowane optymalnie. Jeśli więc możesz użyć poniższych liczników, aby sprawdzić, czy napotykasz presję pamięci

Pamięć: Dostępny MB
Bufor SQL: Darmowe strony
Bufor SQL: Życie strony
Bufor SQL: Leniwe zapisy

jeśli masz do czynienia z presją pamięci, możesz zobaczyć i dostroić zapytania wykorzystujące więcej pamięci lub spróbuj dodać więcej pamięci

mogłeś uruchomić zapytania, które powodują rekompilację. niektóre z nich to

• Zmiany wprowadzone w tabeli lub widoku, do których odwołuje się zapytanie (ALTER TABLE i ALTER VIEW).

• Zmiany wprowadzone w pojedynczej procedurze, która spowoduje usunięcie wszystkich planów tej procedury z pamięci podręcznej (ALTER PROCEDURE).

• Zmiany w indeksach używanych przez plan wykonania

• Aktualizacje statystyk używanych przez plan wykonania, generowane jawnie z instrukcji, takich jak UPDATE STATISTICS, lub generowane automatycznie.

• Usunięcie indeksu używanego przez plan wykonania.

ten dokument można również znaleźć, aby uzyskać więcej informacji na temat buforowania planu

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

Aby dodać to, co powiedział @Gameiswar, możesz również uruchomić to zapytanie, aby zobaczyć szczegóły planów, które nie są uzyskiwane z pamięci podręcznej.

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