- Identify wait stats of given workload
- Set wait stats baseline
- Perform Operation
- Capture wait stats
- Identify the top wait type from the resultant list
- Capture queries corresponding to wait type
- Prepare extended event session for identified wait type
- Start extended event session
- Perform Operation
- Extract results from the session
- Stop extended events session
- Performance overhead on using extended events
Identify wait stats of given workload
Benchmark wait statistics
We use DMV: sys.dm_os_wait_stats to extract the wait stats in the sql server. This DMV provides wait times categorized by wait type as following result set:
The wait time retrieved here is the cumulative time from last services restart and last wait stats reset.
In order to extract the wait stats for the workload we have applied, We have to either
Reset wait stats
Wait stats can be reset using below DBCC command.
The wait time retrieved here is the cumulative time from last services restart and last wait stats reset.
In order to extract the wait stats for the workload we have applied, We have to either
- Reset wait stats (or)
- Baseline wait stats before applying workload.
Reset wait stats
Wait stats can be reset using below DBCC command.
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
If the database is production database, it might not be optimum solution to clear wait stats. Better, considering the benchmark rather than resetting.
Baseline wait statistics
Create baseline with existing statistics using benchmark wait stats query. This creates table WaitStatsBench as below which we use to extract wait times for given workload.
Perform Operation
Perform the actual business transaction or run workload.
Capture wait stats
Extract the wait stats for the workload using Get wait statistics query.
from the DMV sys.dm_os_wait_stats or use modified query to get aggregate wait stats. This results:
Wait statistics captured above are the aggregated result from the last services restart or clear wait stats. Run the difference from baseline query to get the differences from the base line.
Identify the top wait type from the resultant list
Identify the top wait type with highest wait time. Consider ASYNC_NETWORK_IO as the target wait type to be investigated.
Capture queries corresponding to wait type
Prepare extended event session for identified wait type
Create stored procedure with extended event using create resource waits query.
Create stored procedure to extract the captured wait stats using extract events query.
Start extended event session
Insert the corresponding wait type into the table to which query should be captured. following table covers most of the wait types:
wait_type in sys.dm_os_wait_stats
|
map_value in sys.dm_xe_map_values
|
| ASYNC_NETWORK_IO | NETWORK_IO |
| BROKER_TASK_STOP | SSB_TASK_STOP |
| CLR_JOIN | CLR_TASK_JOIN |
| CLR_MEMORY_SPY | CLR_MEMORY_SPY_ACCESS |
| CREATE_DATINISERVICE | GET_DATINISERVICE |
| DBCC_SCALE_OUT_EXPR_CACHE | CHECK_EXPRESSION_CACHE |
| DBSTATE | DB_STATE |
| DLL_LOADING_MUTEX | DLL_LOAD |
| ERROR_REPORTING_MANAGER | ERROR_REPORTING_MGR |
| EXECUTION_PIPE_EVENT_INTERNAL | TWO_THREAD_PIPE_EVENT |
| FS_FC_RWLOCK | FS_GC_RWLOCK |
| FT_IFTS_RWLOCK | FT_RWLOCK |
| FT_IFTS_SCHEDULER_IDLE_WAIT | FT_SCHEDULER_IDLE_WAIT |
| FULLTEXT GATHERER | FULLTEXT_GATHERER |
| HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST | HADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST |
| HADR_DATABASE_FLOW_CONTROL | HADR_PARTNER_FLOW |
| HADR_DATABASE_VERSIONING_STATE | HADR_VERSIONING_STATE |
| HADR_DATABASE_WAIT_FOR_RESTART | __indexMUTEX_HADR_DATABASE_WAIT_FOR_RESTART |
| HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING | HADR_WAIT_FOR_TRANSITION_TO_VERSIONING |
| HADR_FILESTREAM_BLOCK_FLUSH | HADRFS_BLOCK_FLUSH |
| HADR_FILESTREAM_FILE_CLOSE | HADRFS_FILE_CLOSE |
| HADR_FILESTREAM_FILE_REQUEST | HADRFS_FILE_REQUEST |
| HADR_FILESTREAM_IOMGR | HADRFS_IOMGR |
| HADR_FILESTREAM_IOMGR_IOCOMPLETION | HADRFS_IOMGR_IOCOMPLETION |
| HADR_FILESTREAM_MANAGER | HADRFS_MANAGER |
| HADR_RECOVERY_WAIT_FOR_CONNECTION | __indexMUTEX_HADR_RECOVERY_WAIT_FOR_CONNECTION |
| HADR_RECOVERY_WAIT_FOR_UNDO | __indexMUTEX_HADR_RECOVERY_WAIT_FOR_UNDO |
| HADR_TRANSPORT_FLOW_CONTROL | HADR_TRANSPORT_FLOW |
| HTBUILD | HASH_TABLE_BUILD |
| HTREPARTITION | HASH_TABLE_REPARTITION |
| INTERNAL_TESTING | |
| LAZYWRITER_SLEEP | LZW_SLEEP |
| MD_AGENT_YIELD | METADATA_AGENT_YIELD |
| MD_LAZYCACHE_RWLOCK | METADATA_LAZYCACHE_RWLOCK |
| MISCELLANEOUS | UNKNOWN |
| MSSEARCH | MSSEARCH_COM |
| PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO | PREEMPTIVE_FSRECOVER_CONDITIONALUNDO |
| PREEMPTIVE_OS_SQMLAUNCH | PREEMPTIVE_SQMLAUNCH |
| PWAIT_ALL_COMPONENTS_INITIALIZED | ALL_COMPONENTS_INITIALIZED |
| PWAIT_COOP_SCAN | COOP_SCAN |
| PWAIT_EVENT_SESSION_INIT_MUTEX | EVENT_SESSION_INIT_MUTEX |
| PWAIT_HADR_ACTION_COMPLETED | HADR_ACTION_COMPLETED |
| PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC | HADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST |
| PWAIT_HADR_CLUSTER_INTEGRATION | HADR_CHANGE_NOTIFIER_TERMINATION_SYNC |
| PWAIT_HADR_FAILOVER_COMPLETED | HADR_CLUSTER_INTEGRATION |
| PWAIT_HADR_OFFLINE_COMPLETED | HADR_FAILOVER_COMPLETED |
| PWAIT_HADR_ONLINE_COMPLETED | HADR_OFFLINE_COMPLETED |
| PWAIT_HADR_POST_ONLINE_COMPLETED | HADR_ONLINE_COMPLETED |
| PWAIT_HADR_SERVER_READY_CONNECTIONS | HADR_SERVER_READY_CONNECTIONS |
| PWAIT_HADR_WORKITEM_COMPLETED | HADR_WORKITEM_COMPLETED |
| PWAIT_MD_LOGIN_STATS | MD_LOGIN_STATS |
| PWAIT_MD_RELATION_CACHE | MD_RELATION_CACHE |
| PWAIT_MD_SERVER_CACHE | MD_SERVER_CACHE |
| PWAIT_MD_UPGRADE_CONFIG | MD_UPGRADE_CONFIG |
| PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG | PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG |
| PWAIT_QRY_BPMEMORY | QRY_BPMEMORY |
| PWAIT_REPLICA_ONLINE_INIT_MUTEX | REPLICA_ONLINE_INIT_MUTEX |
| PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC | RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC |
| PWAIT_SECURITY_CACHE_INVALIDATION | SECURITY_CACHE_INVALIDATION |
| QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN | QUERY_EXEC_INDEXSORT_OPEN |
| REDO_THREAD_PENDING_WORK | REDO_SIGNAL |
| REDO_THREAD_SYNC | REDO_SYNC |
| RESOURCE_GOVERNOR_IDLE | |
| SCAN_CHAR_HASH_ARRAY_INITIALIZATION | SCAN_CHAR_HASH_ARRAY_INIT |
| SERVER_IDLE_CHECK | SERVER_IDLE_LOCK |
| SNI_LISTENER_ACCESS | LISTENER_UPDATE |
| SNI_TASK_COMPLETION | SNI_WAIT_TASK_FINISH |
| SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEP | PREEMPTIVE_SP_SERVER_DIAGNOSTICS_SLEEP |
| THREADPOOL | SOS_WORKER |
| TRAN_MARKLATCH_DT | TRANMARKLATCH_DT |
| TRAN_MARKLATCH_EX | TRANMARKLATCH_EX |
| TRAN_MARKLATCH_KP | TRANMARKLATCH_KP |
| TRAN_MARKLATCH_NL | TRANMARKLATCH_NL |
| TRAN_MARKLATCH_SH | TRANMARKLATCH_SH |
| TRAN_MARKLATCH_UP | TRANMARKLATCH_UP |
| VIA_ACCEPT | VIA_ACCEPT_DONE |
| WAIT_XTP_GUEST | XTP_GUEST |
| WAIT_XTP_TASK_SHUTDOWN | XTP_TASK_SHUTDOWN |
| WAIT_XTP_TRAN_COMMIT | XTP_TRAN_COMMIT |
| WAITFOR_TASKSHUTDOWN | TASKSHUTDOWN |
| XE_CALLBACK_LIST | XE_CALLBACK |
| XTPPROC_PARTITIONED_STACK_CREATE | __indexXTPPROC_PARTITIONED_STACK_CREATE |
DECLARE @WaitTypeTVP AS WaitType;INSERT INTO @WaitTypeTVP (Name)VALUES ('NETWORK_IO'), ('XE_CALLBACK')EXEC Waits.CaptureStatementsCausingWaits @TVP = @WaitTypeTVP;GO
Perform operation
Wait until actual workload completes.
Extract results from the session
exec [Waits].[GetStatementsCausingWaits]
Stop extended events session
exec Waits.CaptureStatementsCausingWaits @StopXevent = 1
Performance overhead on using extended events
Ringbuffer
File
References
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/03/what-are-you-waiting-for-introducing-wait-stats-support-in-query-store/
https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-30-of-31-tracking-session-and-statement-level-waits/
https://www.sqlskills.com/blogs/jonathan/mapping-wait-types-in-dm_os_wait_stats-to-extended-events/
https://www.brentozar.com/extended-events/
https://johnsterrett.com/2013/10/08/benchmark-sql-server-wait-statistics/
No comments:
Post a Comment