Differences in performance monitor between MSSQL 2000 and 2005/2008
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Until MSSQL 2000 and including MSSQL 2000, all the maintenance system came from Sybase SQL Server with the DBCC modules. Starting with SQL Server 2005 and even more with SQL Server 2008, Microsoft is moving toward the standard of doing things: placing all of these dynamic information is system views.
Waitstats with MSSQL 2000
- DBCC sqlperf('waitstats');
- go
Wait Type Requests Wait Time Signal Wait Time -------------------------------- ------------- ------------- ---------------- MISCELLANEOUS 0 0 0 LCK_M_SCH_S 0 0 0 LCK_M_SCH_M 0 0 0 LCK_M_S 3 796 0 LCK_M_U 0 0 0 LCK_M_X 0 0 0 LCK_M_IS 0 0 0 LCK_M_IU 0 0 0 LCK_M_IX 0 0 0 LCK_M_SIU 0 0 0 LCK_M_SIX 0 0 0 LCK_M_UIX 0 0 0 LCK_M_BU 0 0 0 LCK_M_RS_S 0 0 0 LCK_M_RS_U 0 0 0 LCK_M_RIn_NL 0 0 0 LCK_M_RIn_S 0 0 0 LCK_M_RIn_U 0 0 0 LCK_M_RIn_X 0 0 0 LCK_M_RX_S 0 0 0 LCK_M_RX_U 0 0 0 LCK_M_RX_X 0 0 0 LATCH_NL 0 0 0 LATCH_KP 0 0 0 LATCH_SH 2 0 0 LATCH_UP 0 0 0 LATCH_EX 0 0 0 LATCH_DT 0 0 0 PAGELATCH_NL 0 0 0 PAGELATCH_KP 0 0 0 PAGELATCH_SH 0 0 0 PAGELATCH_UP 0 0 0 PAGELATCH_EX 0 0 0 PAGELATCH_DT 0 0 0 PAGEIOLATCH_NL 0 0 0 PAGEIOLATCH_KP 0 0 0 PAGEIOLATCH_SH 480 3015 0 PAGEIOLATCH_UP 24 171 0 PAGEIOLATCH_EX 21 125 0 PAGEIOLATCH_DT 0 0 0 TRAN_MARKLATCH_NL 0 0 0 TRAN_MARKLATCH_KP 0 0 0 TRAN_MARKLATCH_SH 0 0 0 TRAN_MARKLATCH_UP 0 0 0 TRAN_MARKLATCH_EX 0 0 0 TRAN_MARKLATCH_DT 0 0 0 LAZYWRITER_SLEEP 2306 2304437 31 IO_COMPLETION 137 937 0 ASYNC_IO_COMPLETION 1 359 0 ASYNC_NETWORK_IO 15 328 0 SLEEP_BPOOL_FLUSH 0 0 0 CHKPT 1 500 0 SLEEP_TASK 4552 125 46 SLEEP_SYSTEMTASK 1 500 0 RESOURCE_SEMAPHORE 0 0 0 DTC 0 0 0 OLEDB 0 0 0 FAILPOINT 0 0 0 RESOURCE_QUEUE 0 0 0 ASYNC_DISKPOOL_LOCK 0 0 0 THREADPOOL 26 187 0 DEBUG 0 0 0 REPLICA_WRITES 0 0 0 BROKER_RECEIVE_WAITFOR 0 0 0 DBMIRRORING_CMD 0 0 0 WAIT_FOR_RESULTS 0 0 0 SOS_SCHEDULER_YIELD 62 0 0 SOS_VIRTUALMEMORY_LOW 0 0 0 SOS_RESERVEDMEMBLOCKLIST 0 0 0 SOS_LOCALALLOCATORLIST 0 0 0 SOS_CALLBACK_REMOVAL 0 0 0 LOWFAIL_MEMMGR_QUEUE 0 0 0 BACKUP 0 0 0 BACKUPBUFFER 0 0 0 BACKUPIO 0 0 0 BACKUPTHREAD 0 0 0 DBMIRROR_DBM_MUTEX 0 0 0 DBMIRROR_DBM_EVENT 0 0 0 DBMIRROR_SEND 0 0 0 CURSOR_ASYNC 0 0 0 HTTP_ENUMERATION 0 0 0 SOAP_READ 0 0 0 SOAP_WRITE 0 0 0 DUMP_LOG_COORDINATOR 0 0 0 DISKIO_SUSPEND 0 0 0 IMPPROV_IOWAIT 0 0 0 QNMANAGER_ACQUIRE 0 0 0 DEADLOCK_TASK_SEARCH 0 0 0 REPL_SCHEMA_ACCESS 0 0 0 REPL_CACHE_ACCESS 0 0 0 SQLSORT_SORTMUTEX 0 0 0 SQLSORT_NORMMUTEX 0 0 0 SQLTRACE_WAIT_ENTRIES 0 0 0 SQLTRACE_LOCK 0 0 0 SQLTRACE_BUFFER_FLUSH 577 2304000 0 SQLTRACE_SHUTDOWN 0 0 0 MSQL_SYNC_PIPE 0 0 0 QUERY_TRACEOUT 0 0 0 DTC_STATE 0 0 0 FCB_REPLICA_WRITE 0 0 0 FCB_REPLICA_READ 0 0 0 WRITELOG 49 531 0 HTTP_ENDPOINT_COLLCREATE 0 0 0 EXCHANGE 0 0 0 DBTABLE 0 0 0 EC 0 0 0 TEMPOBJ 0 0 0 XACTLOCKINFO 0 0 0 LOGMGR 0 0 0 CMEMTHREAD 0 0 0 CXPACKET 0 0 0 WAITFOR 0 0 0 CURSOR 0 0 0 EXECSYNC 0 0 0 SOSHOST_INTERNAL 0 0 0 SOSHOST_SLEEP 0 0 0 SOSHOST_WAITFORDONE 0 0 0 SOSHOST_MUTEX 0 0 0 SOSHOST_EVENT 0 0 0 SOSHOST_SEMAPHORE 0 0 0 SOSHOST_RWLOCK 0 0 0 SOSHOST_TRACELOCK 0 0 0 MSQL_XP 28 250 0 MSQL_DQ 0 0 0 LOGBUFFER 5 31 0 TRANSACTION_MUTEX 0 0 0 MSSEARCH 12 31 0 XACTWORKSPACE_MUTEX 0 0 0 CLR_JOIN 0 0 0 CLR_CRST 0 0 0 CLR_SEMAPHORE 0 0 0 CLR_MANUAL_EVENT 0 0 0 CLR_AUTO_EVENT 0 0 0 CLR_MONITOR 0 0 0 CLR_RWLOCK_READER 0 0 0 CLR_RWLOCK_WRITER 0 0 0 SQLCLR_QUANTUM_PUNISHMENT 0 0 0 SQLCLR_APPDOMAIN 0 0 0 SQLCLR_ASSEMBLY 0 0 0 KTM_ENLISTMENT 0 0 0 KTM_RECOVERY_RESOLUTION 0 0 0 KTM_RECOVERY_MANAGER 0 0 0 SQLCLR_DEADLOCK_DETECTION 0 0 0 QPJOB_WAITFOR_ABORT 0 0 0 QPJOB_KILL 0 0 0 BAD_PAGE_PROCESS 0 0 0 BACKUP_OPERATOR 0 0 0 PRINT_ROLLBACK_PROGRESS 0 0 0 ENABLE_VERSIONING 0 0 0 DISABLE_VERSIONING 0 0 0 REQUEST_DISPENSER_PAUSE 0 0 0 DROPTEMP 0 0 0 FT_RESTART_CRAWL 0 0 0 FT_RESUME_CRAWL 0 0 0 LOGMGR_RESERVE_APPEND 0 0 0 LOGMGR_FLUSH 0 0 0 XACT_OWN_TRANSACTION 0 0 0 XACT_RECLAIM_SESSION 0 0 0 DTC_WAITFOR_OUTCOME 0 0 0 DTC_RESOLVE 0 0 0 SEC_DROP_TEMP_KEY 0 0 0 SRVPROC_SHUTDOWN 0 0 0 NET_WAITFOR_PACKET 0 0 0 DTC_ABORT_REQUEST 0 0 0 DTC_TMDOWN_REQUEST 0 0 0 RECOVER_CHANGEDB 0 0 0 WORKTBL_DROP 0 0 0 MIRROR_SEND_MESSAGE 0 0 0 SNI_HTTP_ACCEPT 0 0 0 SNI_HTTP_WAITFOR_0_DISCON 0 0 0 UTIL_PAGE_ALLOC 0 0 0 SERVER_IDLE_CHECK 0 0 0 BACKUP_CLIENTLOCK 0 0 0 DEADLOCK_ENUM_MUTEX 0 0 0 INDEX_USAGE_STATS_MUTEX 0 0 0 VIEW_DEFINITION_MUTEX 0 0 0 QUERY_NOTIFICATION_MGR_MUTEX 0 0 0 QUERY_NOTIFICATION_TABLE_MGR_MUT 0 0 0 QUERY_NOTIFICATION_SUBSCRIPTION_ 0 0 0 QUERY_NOTIFICATION_UNITTEST_MUTE 0 0 0 IMP_IMPORT_MUTEX 0 0 0 RESOURCE_SEMAPHORE_MUTEX 0 0 0 IO_AUDIT_MUTEX 0 0 0 BUILTIN_HASHKEY_MUTEX 0 0 0 SOS_PROCESS_AFFINITY_MUTEX 0 0 0 MSQL_XACT_MGR_MUTEX 0 0 0 MSQL_XACT_MUTEX 0 0 0 QRY_MEM_GRANT_INFO_MUTEX 0 0 0 SOS_STACKSTORE_INIT_MUTEX 0 0 0 SOS_SYNC_TASK_ENQUEUE_EVENT 0 0 0 SOS_OBJECT_STORE_DESTROY_MUTEX 0 0 0 EE_PMOLOCK 0 0 0 RESOURCE_SEMAPHORE_QUERY_COMPILE 0 0 0 RESOURCE_SEMAPHORE_SMALL_QUERY 0 0 0 FULLTEXT GATHERER 0 0 0 SEQUENTIAL_GUID 0 0 0 BROKER_TASK_STOP 1 10000 0 SNI_LISTENER_ACCESS 0 0 0 EXECUTION_PIPE_EVENT_INTERNAL 0 0 0 ASSEMBLY_LOAD 0 0 0 INTERNAL_TESTING 0 0 0 Total 8303 4626328 78 (202 row(s) affected)
This still works with SQL Server 2005, but the SQL Server 2005/2008 has a better way of monitoring it.
These are the waitstats that personally watch and monitor, they pretty much give a state of the SQL Server:
Wait Type Requests Wait Time Signal Wait Time -------------------------------- ------------- ------------- ---------------- IO_COMPLETION 137 937 0 ASYNC_NETWORK_IO 15 328 0 WAITFOR 0 0 0 LAZYWRITER_SLEEP 2306 2304437 31 IO_COMPLETION 137 937 0 ASYNC_IO_COMPLETION 1 359 0 ASYNC_NETWORK_IO 15 328 0 REPLICA_WRITES 0 0 0 WRITELOG 49 531 0
Waitstats with MSSQL 2005 / MSSQL 2008
- SELECT * FROM sys.dm_os_wait_stats;
- go
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- MISCELLANEOUS 0 0 0 0 LCK_M_SCH_S 0 0 0 0 LCK_M_SCH_M 0 0 0 0 LCK_M_S 3 796 296 0 LCK_M_U 0 0 0 0 LCK_M_X 0 0 0 0 LCK_M_IS 0 0 0 0 LCK_M_IU 0 0 0 0 LCK_M_IX 0 0 0 0 LCK_M_SIU 0 0 0 0 LCK_M_SIX 0 0 0 0 LCK_M_UIX 0 0 0 0 LCK_M_BU 0 0 0 0 LCK_M_RS_S 0 0 0 0 LCK_M_RS_U 0 0 0 0 LCK_M_RIn_NL 0 0 0 0 LCK_M_RIn_S 0 0 0 0 LCK_M_RIn_U 0 0 0 0 LCK_M_RIn_X 0 0 0 0 LCK_M_RX_S 0 0 0 0 LCK_M_RX_U 0 0 0 0 LCK_M_RX_X 0 0 0 0 LATCH_NL 0 0 0 0 LATCH_KP 0 0 0 0 LATCH_SH 2 0 0 0 LATCH_UP 0 0 0 0 LATCH_EX 0 0 0 0 LATCH_DT 0 0 0 0 PAGELATCH_NL 0 0 0 0 PAGELATCH_KP 0 0 0 0 PAGELATCH_SH 0 0 0 0 PAGELATCH_UP 0 0 0 0 PAGELATCH_EX 0 0 0 0 PAGELATCH_DT 0 0 0 0 PAGEIOLATCH_NL 0 0 0 0 PAGEIOLATCH_KP 0 0 0 0 PAGEIOLATCH_SH 480 3015 62 0 PAGEIOLATCH_UP 24 171 31 0 PAGEIOLATCH_EX 21 125 46 0 PAGEIOLATCH_DT 0 0 0 0 TRAN_MARKLATCH_NL 0 0 0 0 TRAN_MARKLATCH_KP 0 0 0 0 TRAN_MARKLATCH_SH 0 0 0 0 TRAN_MARKLATCH_UP 0 0 0 0 TRAN_MARKLATCH_EX 0 0 0 0 TRAN_MARKLATCH_DT 0 0 0 0 LAZYWRITER_SLEEP 2911 2909437 1093 31 IO_COMPLETION 137 937 62 0 ASYNC_IO_COMPLETION 1 359 359 0 ASYNC_NETWORK_IO 17 343 156 0 SLEEP_BPOOL_FLUSH 0 0 0 0 CHKPT 1 500 500 0 SLEEP_TASK 5762 125 78 46 SLEEP_SYSTEMTASK 1 500 500 0 RESOURCE_SEMAPHORE 0 0 0 0 DTC 0 0 0 0 OLEDB 0 0 0 0 FAILPOINT 0 0 0 0 RESOURCE_QUEUE 0 0 0 0 ASYNC_DISKPOOL_LOCK 0 0 0 0 THREADPOOL 26 187 93 0 DEBUG 0 0 0 0 REPLICA_WRITES 0 0 0 0 BROKER_RECEIVE_WAITFOR 0 0 0 0 DBMIRRORING_CMD 0 0 0 0 WAIT_FOR_RESULTS 0 0 0 0 SOS_SCHEDULER_YIELD 63 0 0 0 SOS_VIRTUALMEMORY_LOW 0 0 0 0 SOS_RESERVEDMEMBLOCKLIST 0 0 0 0 SOS_LOCALALLOCATORLIST 0 0 0 0 SOS_CALLBACK_REMOVAL 0 0 0 0 LOWFAIL_MEMMGR_QUEUE 0 0 0 0 BACKUP 0 0 0 0 BACKUPBUFFER 0 0 0 0 BACKUPIO 0 0 0 0 BACKUPTHREAD 0 0 0 0 DBMIRROR_DBM_MUTEX 0 0 0 0 DBMIRROR_DBM_EVENT 0 0 0 0 DBMIRROR_SEND 0 0 0 0 CURSOR_ASYNC 0 0 0 0 HTTP_ENUMERATION 0 0 0 0 SOAP_READ 0 0 0 0 SOAP_WRITE 0 0 0 0 DUMP_LOG_COORDINATOR 0 0 0 0 DISKIO_SUSPEND 0 0 0 0 IMPPROV_IOWAIT 0 0 0 0 QNMANAGER_ACQUIRE 0 0 0 0 DEADLOCK_TASK_SEARCH 0 0 0 0 REPL_SCHEMA_ACCESS 0 0 0 0 REPL_CACHE_ACCESS 0 0 0 0 SQLSORT_SORTMUTEX 0 0 0 0 SQLSORT_NORMMUTEX 0 0 0 0 SQLTRACE_WAIT_ENTRIES 0 0 0 0 SQLTRACE_LOCK 0 0 0 0 SQLTRACE_BUFFER_FLUSH 728 2908015 4015 0 SQLTRACE_SHUTDOWN 0 0 0 0 MSQL_SYNC_PIPE 0 0 0 0 QUERY_TRACEOUT 0 0 0 0 DTC_STATE 0 0 0 0 FCB_REPLICA_WRITE 0 0 0 0 FCB_REPLICA_READ 0 0 0 0 WRITELOG 49 531 46 0 HTTP_ENDPOINT_COLLCREATE 0 0 0 0 EXCHANGE 0 0 0 0 DBTABLE 0 0 0 0 EC 0 0 0 0 TEMPOBJ 0 0 0 0 XACTLOCKINFO 0 0 0 0 LOGMGR 0 0 0 0 CMEMTHREAD 0 0 0 0 CXPACKET 0 0 0 0 WAITFOR 0 0 0 0 CURSOR 0 0 0 0 EXECSYNC 0 0 0 0 SOSHOST_INTERNAL 0 0 0 0 SOSHOST_SLEEP 0 0 0 0 SOSHOST_WAITFORDONE 0 0 0 0 SOSHOST_MUTEX 0 0 0 0 SOSHOST_EVENT 0 0 0 0 SOSHOST_SEMAPHORE 0 0 0 0 SOSHOST_RWLOCK 0 0 0 0 SOSHOST_TRACELOCK 0 0 0 0 MSQL_XP 28 250 218 0 MSQL_DQ 0 0 0 0 LOGBUFFER 5 31 15 0 TRANSACTION_MUTEX 0 0 0 0 MSSEARCH 12 31 31 0 XACTWORKSPACE_MUTEX 0 0 0 0 CLR_JOIN 0 0 0 0 CLR_CRST 0 0 0 0 CLR_SEMAPHORE 0 0 0 0 CLR_MANUAL_EVENT 0 0 0 0 CLR_AUTO_EVENT 0 0 0 0 CLR_MONITOR 0 0 0 0 CLR_RWLOCK_READER 0 0 0 0 CLR_RWLOCK_WRITER 0 0 0 0 SQLCLR_QUANTUM_PUNISHMENT 0 0 0 0 SQLCLR_APPDOMAIN 0 0 0 0 SQLCLR_ASSEMBLY 0 0 0 0 KTM_ENLISTMENT 0 0 0 0 KTM_RECOVERY_RESOLUTION 0 0 0 0 KTM_RECOVERY_MANAGER 0 0 0 0 SQLCLR_DEADLOCK_DETECTION 0 0 0 0 QPJOB_WAITFOR_ABORT 0 0 0 0 QPJOB_KILL 0 0 0 0 BAD_PAGE_PROCESS 0 0 0 0 BACKUP_OPERATOR 0 0 0 0 PRINT_ROLLBACK_PROGRESS 0 0 0 0 ENABLE_VERSIONING 0 0 0 0 DISABLE_VERSIONING 0 0 0 0 REQUEST_DISPENSER_PAUSE 0 0 0 0 DROPTEMP 0 0 0 0 FT_RESTART_CRAWL 0 0 0 0 FT_RESUME_CRAWL 0 0 0 0 LOGMGR_RESERVE_APPEND 0 0 0 0 LOGMGR_FLUSH 0 0 0 0 XACT_OWN_TRANSACTION 0 0 0 0 XACT_RECLAIM_SESSION 0 0 0 0 DTC_WAITFOR_OUTCOME 0 0 0 0 DTC_RESOLVE 0 0 0 0 SEC_DROP_TEMP_KEY 0 0 0 0 SRVPROC_SHUTDOWN 0 0 0 0 NET_WAITFOR_PACKET 0 0 0 0 DTC_ABORT_REQUEST 0 0 0 0 DTC_TMDOWN_REQUEST 0 0 0 0 RECOVER_CHANGEDB 0 0 0 0 WORKTBL_DROP 0 0 0 0 MIRROR_SEND_MESSAGE 0 0 0 0 SNI_HTTP_ACCEPT 0 0 0 0 SNI_HTTP_WAITFOR_0_DISCON 0 0 0 0 UTIL_PAGE_ALLOC 0 0 0 0 SERVER_IDLE_CHECK 0 0 0 0 BACKUP_CLIENTLOCK 0 0 0 0 DEADLOCK_ENUM_MUTEX 0 0 0 0 INDEX_USAGE_STATS_MUTEX 0 0 0 0 VIEW_DEFINITION_MUTEX 0 0 0 0 QUERY_NOTIFICATION_MGR_MUTEX 0 0 0 0 QUERY_NOTIFICATION_TABLE_MGR_MUTEX 0 0 0 0 QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX 0 0 0 0 QUERY_NOTIFICATION_UNITTEST_MUTEX 0 0 0 0 IMP_IMPORT_MUTEX 0 0 0 0 RESOURCE_SEMAPHORE_MUTEX 0 0 0 0 IO_AUDIT_MUTEX 0 0 0 0 BUILTIN_HASHKEY_MUTEX 0 0 0 0 SOS_PROCESS_AFFINITY_MUTEX 0 0 0 0 MSQL_XACT_MGR_MUTEX 0 0 0 0 MSQL_XACT_MUTEX 0 0 0 0 QRY_MEM_GRANT_INFO_MUTEX 0 0 0 0 SOS_STACKSTORE_INIT_MUTEX 0 0 0 0 SOS_SYNC_TASK_ENQUEUE_EVENT 0 0 0 0 SOS_OBJECT_STORE_DESTROY_MUTEX 0 0 0 0 EE_PMOLOCK 0 0 0 0 RESOURCE_SEMAPHORE_QUERY_COMPILE 0 0 0 0 RESOURCE_SEMAPHORE_SMALL_QUERY 0 0 0 0 FULLTEXT GATHERER 0 0 0 0 SEQUENTIAL_GUID 0 0 0 0 BROKER_TASK_STOP 1 10000 10000 0 SNI_LISTENER_ACCESS 0 0 0 0 EXECUTION_PIPE_EVENT_INTERNAL 0 0 0 0 ASSEMBLY_LOAD 0 0 0 0 INTERNAL_TESTING 0 0 0 0 (201 row(s) affected)
Both ways give you the same information, but!...
- DBCC is on the way out. Not all DBCC commands have been deprecated, but Microsoft has started. So far about half of them, including all the indexing and backups...
- The earlier you phase out DBCC the simpler the conversion to next version of SQL Server.
