PVI_COPY

_01_СЕРВЕР_КЕШ_планов_1_1

Apr 24th, 2021
171
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- _01_СЕРВЕР_КЕШ_планов_1_1
  2.  
  3. -- http://skproj.ru/pochemu-dbcc-freeproccache-pomogaet/?doing_wp_cron=1618856089.8024010658264160156250
  4. -- Почему dbcc freeproccache помогает?
  5. SELECT text, cp.objtype, cp.size_in_bytes
  6.     FROM sys.dm_exec_cached_plans AS cp
  7.     CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
  8.     WHERE cp.cacheobjtype = N'Compiled Plan'
  9.     AND cp.objtype IN(N'Adhoc', N'Prepared')
  10.     AND cp.usecounts = 1
  11.     ORDER BY cp.size_in_bytes DESC
  12.     OPTION (RECOMPILE);
  13.  
  14. -- Adhoc Значительно больше Prepared
  15.  
  16. -- Раздуло кэш SQL? Лечимся народными средствами.
  17. -- http://eazybi.blogspot.com/2015/07/sql.html
  18.  
  19. --Чтобы понять есть ли проблема с Adhoc запросами, достаточно запустить следующий запрос
  20.  
  21. SELECT T.*, cast(T.[Total Plans - USE Count 1]*1.0/nullif(t.[Total Plans],0) *100  AS decimal(5,2)) Percentage
  22. FROM
  23. (
  24.        SELECT objtype AS [CacheType]
  25.                     , count_big(*) AS [Total Plans]
  26.                     , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
  27.                     , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
  28.                     , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
  29.        FROM sys.dm_exec_cached_plans cp
  30.        WHERE cp.cacheobjtype = N'Compiled Plan'
  31.        AND cp.objtype IN (N'Adhoc', N'Prepared') -- adhoc (произвольный запрос), prepared (параметризованный)
  32.        GROUP BY objtype
  33. ) T
  34. ORDER BY [Total MBs - USE Count 1] DESC
  35. OPTION (RECOMPILE);  --план не будет сохраняться, эту опцию желательно использовать для adhoc запросов
  36.  
  37. --Из результатов запроса понятно что процент  Adhoc  запросов  которые выполнялись только 1 раз очень высокий 76.51%
  38. --CacheType Total Plans Total MBs   Total MBs - USE Count 1 Total Plans - USE Count 1   Percentage
  39. --Adhoc     562         65.914062   40.703125               430                         76.51
  40. --Prepared  55          23.656250   7.687500                20                          36.36
  41.  
  42.  
  43.  
  44. -- выдать 100 первых запросов раздувающих кэш по размеру
  45. SELECT top 100 *
  46. FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
  47. CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  48. WHERE cp.cacheobjtype = N'Compiled Plan'
  49. AND cp.objtype = N'Adhoc'
  50. AND cp.usecounts = 1
  51. ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
  52.  
  53.  
  54.  
  55.  
  56.  
  57.  
  58. --выдать 100 первых закешированных планов
  59. SELECT top 100 *
  60. FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
  61. CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  62. ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE)
RAW Paste Data