Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 11_1_7_SQLServer_Производительность
- --Набор скриптов для знакомства с SQL Server
- --https://infostart.ru/1c/articles/1128594/
- -- статистика по ожиданиям, с помощью которой можно понять что же не так со SQL Server.
- WITH [Waits] AS
- (SELECT
- [wait_type],
- [wait_time_ms] / 1000.0 AS [WaitS],
- ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
- [signal_wait_time_ms] / 1000.0 AS [SignalS],
- [waiting_tasks_count] AS [WaitCount],
- 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
- ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
- FROM sys.dm_os_wait_stats
- WHERE [wait_type] NOT IN (
- N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
- N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
- N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
- N'CHKPT', N'CLR_AUTO_EVENT',
- N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
- -- Maybe uncomment these four if you have mirroring issues
- N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
- N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
- N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
- N'EXECSYNC', N'FSAGENT',
- N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
- -- Maybe uncomment these six if you have AG issues
- N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
- N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
- N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
- N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
- N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
- N'ONDEMAND_TASK_QUEUE',
- N'PREEMPTIVE_XE_GETTARGETSTATE',
- N'PWAIT_ALL_COMPONENTS_INITIALIZED',
- N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
- N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
- N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
- N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
- N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
- N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
- N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
- N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
- N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
- N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
- N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
- N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
- N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
- N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
- N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
- N'WAIT_XTP_RECOVERY',
- N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
- N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
- N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
- AND [waiting_tasks_count] > 0
- )
- SELECT
- MAX ([W1].[wait_type]) AS [WaitType],
- CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
- CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
- CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
- MAX ([W1].[WaitCount]) AS [WaitCount],
- CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
- CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
- CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
- CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
- CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
- FROM [Waits] AS [W1]
- INNER JOIN [Waits] AS [W2]
- ON [W2].[RowNum] <= [W1].[RowNum]
- GROUP BY [W1].[RowNum]
- HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
- -- Запрос покажет что именно ожидает SQL Server и даст ссылку на информацию о данном типе ожидания.
- -- Отличная статья на эту тему (там же и был взят скрипт) "SQL Server Wait Statistics (or please tell me where it hurts…)" от Paul Randal.
- /*
- WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- WRITELOG 3527.76 3349.98 177.78 328120 1.13 0.0108 0.0102 0.0005 https://www.sqlskills.com/help/waits/WRITELOG
- */
- -- посмотреть на статистику использования процессорных ресурсов и дисковой подсистемы по базам.
- -- Для CPU
- WITH
- DB_CPU_Stats
- AS
- (
- SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
- FROM sys.dm_exec_query_stats AS qs
- CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
- FROM sys.dm_exec_plan_attributes(qs.plan_handle)
- WHERE attribute = N'dbid') AS F_DB
- GROUP BY DatabaseID
- )
- SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
- DatabaseName, [CPU_Time_Ms],
- CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
- FROM DB_CPU_Stats
- WHERE DatabaseID > 4 -- system databases
- AND DatabaseID <> 32767
- -- ResourceDB
- ORDER BY row_num
- OPTION
- (RECOMPILE);
- -- Для дисков ниже.
- WITH DB_Disk_Reads_Stats
- AS
- (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]
- FROM sys.dm_exec_query_stats AS qs
- CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
- FROM sys.dm_exec_plan_attributes(qs.plan_handle)
- WHERE attribute = N'dbid') AS F_DB
- GROUP BY DatabaseID)
- SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],
- DatabaseName, [physical_reads],
- CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]
- FROM DB_Disk_Reads_Stats
- WHERE DatabaseID > 4 -- system databases
- AND DatabaseID <> 32767 -- ResourceDB
- ORDER BY row_num OPTION (RECOMPILE);
- -- Примерное представление о "тяжелых" системах это позволяет получить.
- -- Дальнейшее расследование и действия сильно зависит от полученной информации на данном этапе. Далее можно:
- -- Настроить мониторинг SQL Server'а (как внутренних показателей СУБД, так и счетчиков производительности).
- --Другие ссылки
- --Мониторинг SQL Server с помощью Extended Events (и не только) для 1С. Как держать руку на пульсе?
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement