PVI_COPY

11_1_7_SQLServer_Производительность

Apr 12th, 2021
221
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- 11_1_7_SQLServer_Производительность
  3.  
  4. --Набор скриптов для знакомства с SQL Server
  5. --https://infostart.ru/1c/articles/1128594/
  6.  
  7. -- статистика по ожиданиям, с помощью которой можно понять что же не так со SQL Server.
  8.  
  9. WITH [Waits] AS
  10.     (SELECT
  11.         [wait_type],
  12.         [wait_time_ms] / 1000.0 AS [WaitS],
  13.         ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
  14.         [signal_wait_time_ms] / 1000.0 AS [SignalS],
  15.         [waiting_tasks_count] AS [WaitCount],
  16.        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
  17.         ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
  18.     FROM sys.dm_os_wait_stats
  19.     WHERE [wait_type] NOT IN (
  20.         N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
  21.         N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
  22.         N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
  23.         N'CHKPT', N'CLR_AUTO_EVENT',
  24.         N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
  25.  
  26.         -- Maybe uncomment these four if you have mirroring issues
  27.         N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
  28.         N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
  29.  
  30.         N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
  31.         N'EXECSYNC', N'FSAGENT',
  32.         N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
  33.  
  34.         -- Maybe uncomment these six if you have AG issues
  35.         N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
  36.         N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
  37.         N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
  38.  
  39.         N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
  40.         N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
  41.         N'ONDEMAND_TASK_QUEUE',
  42.         N'PREEMPTIVE_XE_GETTARGETSTATE',
  43.         N'PWAIT_ALL_COMPONENTS_INITIALIZED',
  44.         N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
  45.         N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
  46.         N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
  47.         N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
  48.         N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
  49.         N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
  50.         N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
  51.         N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
  52.         N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
  53.         N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
  54.         N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
  55.         N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
  56.         N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
  57.         N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
  58.         N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
  59.         N'WAIT_XTP_RECOVERY',
  60.         N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
  61.         N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
  62.         N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
  63.     AND [waiting_tasks_count] > 0
  64.     )
  65. SELECT
  66.     MAX ([W1].[wait_type]) AS [WaitType],
  67.     CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
  68.     CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
  69.     CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
  70.     MAX ([W1].[WaitCount]) AS [WaitCount],
  71.     CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
  72.     CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
  73.     CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
  74.     CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
  75.     CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
  76. FROM [Waits] AS [W1]
  77. INNER JOIN [Waits] AS [W2]
  78.     ON [W2].[RowNum] <= [W1].[RowNum]
  79. GROUP BY [W1].[RowNum]
  80. HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
  81.  
  82.  
  83. -- Запрос покажет что именно ожидает SQL Server и даст ссылку на информацию о данном типе ожидания.
  84. -- Отличная статья на эту тему (там же и был взят скрипт) "SQL Server Wait Statistics (or please tell me where it hurts…)" от Paul Randal.
  85.  
  86. /*
  87. WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S    Help/Info URL
  88. SOS_SCHEDULER_YIELD 248216.65   136.36  248080.29   21852943    79.69   0.0114  0.0000  0.0114  https://www.sqlskills.com/help/waits/SOS_SCHEDULER_YIELD
  89. PREEMPTIVE_OS_CRYPTOPS  17053.21    17053.21    0.00    6084458 5.47    0.0028  0.0028  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CRYPTOPS
  90. PREEMPTIVE_OS_REPORTEVENT   7526.40 7526.40 0.00    2008704 2.42    0.0037  0.0037  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_REPORTEVENT
  91. PREEMPTIVE_OS_CRYPTACQUIRECONTEXT   6173.63 6173.63 0.00    2041855 1.98    0.0030  0.0030  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
  92. PREEMPTIVE_OS_CRYPTIMPORTKEY    5896.65 5896.65 0.00    2039731 1.89    0.0029  0.0029  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CRYPTIMPORTKEY
  93. PREEMPTIVE_OS_AUTHORIZATIONOPS  5342.54 5342.54 0.00    2050222 1.72    0.0026  0.0026  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_AUTHORIZATIONOPS
  94. PREEMPTIVE_OS_DELETESECURITYCONTEXT 4618.72 4618.72 0.00    2056831 1.48    0.0022  0.0022  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_DELETESECURITYCONTEXT
  95. WRITELOG    3527.76 3349.98 177.78  328120  1.13    0.0108  0.0102  0.0005  https://www.sqlskills.com/help/waits/WRITELOG
  96.  
  97. */
  98.  
  99.  
  100. -- посмотреть на статистику использования процессорных ресурсов и дисковой подсистемы по базам.
  101.  
  102. -- Для CPU
  103.  
  104. WITH
  105.     DB_CPU_Stats
  106.     AS
  107.     (
  108.         SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
  109.         FROM sys.dm_exec_query_stats AS qs
  110. CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
  111.             FROM sys.dm_exec_plan_attributes(qs.plan_handle)
  112.             WHERE attribute = N'dbid') AS F_DB
  113.         GROUP BY DatabaseID
  114.     )
  115. SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
  116.     DatabaseName, [CPU_Time_Ms],
  117.     CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
  118. FROM DB_CPU_Stats
  119. WHERE DatabaseID > 4 -- system databases
  120.     AND DatabaseID <> 32767
  121. -- ResourceDB
  122. ORDER BY row_num
  123. OPTION
  124. (RECOMPILE);
  125.  
  126.  
  127. -- Для дисков ниже.
  128.  
  129. WITH DB_Disk_Reads_Stats
  130.  
  131. AS
  132.  
  133. (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]
  134.  
  135.  FROM sys.dm_exec_query_stats AS qs
  136.  
  137.  CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
  138.  
  139.               FROM sys.dm_exec_plan_attributes(qs.plan_handle)
  140.  
  141.               WHERE attribute = N'dbid') AS F_DB
  142.  
  143.  GROUP BY DatabaseID)
  144.  
  145. SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],
  146.  
  147.        DatabaseName, [physical_reads],
  148.  
  149.        CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]
  150.  
  151. FROM DB_Disk_Reads_Stats
  152.  
  153. WHERE DatabaseID > 4 -- system databases
  154.  
  155. AND DatabaseID <> 32767 -- ResourceDB
  156.  
  157. ORDER BY row_num OPTION (RECOMPILE);
  158.  
  159. -- Примерное представление о "тяжелых" системах это позволяет получить.
  160.  
  161. -- Дальнейшее расследование и действия сильно зависит от полученной информации на данном этапе. Далее можно:
  162. -- Настроить мониторинг SQL Server'а (как внутренних показателей СУБД, так и счетчиков производительности).
  163.  
  164.  
  165. --Другие ссылки
  166. --Мониторинг SQL Server с помощью Extended Events (и не только) для 1С. Как держать руку на пульсе?
RAW Paste Data