November 5, 2009 at 3:48 pm
Hello!
I have installed a Windows Server 2008 64bit with SQL Server 2008 Standard edition + Microsoft Business Solution Navision 5.0 SP.1
My boss ha launched a procedure directly on server which usually takes about 18 hours on our production server (Windows 2003 / SQL Server 2000). Today he tells me that the procedure is still running after two and half days.
How should I troubleshoot the SQL Server installation to find out the reason. I tried to do the following, however, I don't know how exaxtly interpret results:
[font="Courier New"]
1) DBCC PROCCACHE
--------------------------------------
num proc buffs 39384
num proc buffs used 288
num proc buffs active 399
proc cache size 2328
proc cache used 30
proc cache active 20
--------------------------------------
2) DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'spt_fallback_db' table...
Table: 'spt_fallback_db' (117575457); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_fallback_dev' table...
Table: 'spt_fallback_dev' (133575514); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_fallback_usg' table...
Table: 'spt_fallback_usg' (149575571); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_monitor' table...
Table: 'spt_monitor' (1115151018); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8024.0
- Avg. Page Density (full).....................: 0.86%
DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (1131151075); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 12
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 66.67% [2:3]
- Logical Scan Fragmentation ..................: 25.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 544.5
- Avg. Page Density (full).....................: 93.27%
DBCC SHOWCONTIG scanning 'MSreplication_options' table...
Table: 'MSreplication_options' (1163151189); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7900.0
- Avg. Page Density (full).....................: 2.40%
DBCC SHOWCONTIG scanning '$ndo$srvproperty' table...
Table: '$ndo$srvproperty' (1259151531); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8053.0
- Avg. Page Density (full).....................: 0.51%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
3) DBCC PERFMON
Statistic Value
-------------------------------- -------------
Reads Outstanding 0
Writes Outstanding 0
(2 row(s) affected)
Statistic Value
-------------------------------- -------------
Network Reads 4,93287E+07
Network Writes 3,748093E+07
Network Bytes Read -1,693009E+09
Network Bytes Written -5,630639E+08
Command Queue Length 0
Max Command Queue Length 0
Worker Threads 0
Max Worker Threads 0
Network Threads 0
Max Network Threads 0
(10 row(s) affected)
Statistic Value
-------------------------------- -------------
RA Pages Found in Cache 0
RA Pages Placed in Cache 0
RA Physical IO 0
Used Slots 0
(4 row(s) affected)
Spinlock Name Collisions Spins Spins/Collision Sleep Time (ms) Backoffs
------------------------------ -------------------- -------------------- --------------- -------------------- -----------
ABR 0 0 0 0 0
GUARDIAN 0 0 0 0 0
MOP_COLL 0 0 0 0 0
MOP 0 0 0 0 0
CONNECTS 0 0 0 0 0
ASYNCSTATSLIST 0 0 0 0 0
HTTP_CONNCACHE 0 0 0 0 0
MUTEX 214 426158 1991,393 3 26
SRVPROC 9056 18530115 2046,17 160 1268
EXT_CACHE 0 0 0 0 0
FT_INIT 0 0 0 0 0
COM_INIT 0 0 0 0 0
LOGON_TRIGGER_CACHE 0 0 0 0 0
SOAPSESSIONS 0 0 0 0 0
QUERYSCAN 1 0 0 0 0
BACKUP_CTX 66 79234 1200,515 0 7
RESOURCE 0 0 0 0 0
CACHEOBJ_DBG 0 0 0 0 0
DBCC_CHECK 0 0 0 0 0
ADB_CACHE 0 0 0 0 0
X_PIPE_DEMAND 0 0 0 0 0
DINPBUF 0 0 0 0 0
SOS_OBJECT_POOL 0 0 0 0 0
SQL_MGR 0 0 0 0 0
MEM_MGR 0 0 0 0 0
BUF_HASH 130 228600 1758,462 1 11
DBT_HASH 0 0 0 0 0
HOBT_HASH 5 369 73,8 0 0
TXFRM_REPL 0 0 0 0 0
FSGHOST_STATUS 0 0 0 0 0
PAGECOPIER 0 0 0 0 0
DTT_LIST 0 0 0 0 0
ENDD_LIST 0 0 0 0 0
IDENTITY 0 0 0 0 0
BUF_LINK 0 0 0 0 0
DBTABLE 10242 2475046 241,6565 27 161
X_PORT 11 9119 829 0 0
XDESMGR 285 323973 1136,747 5 28
X_PIPE 0 0 0 0 0
LOGFLUSHQ 5 161068 32213,6 3 16
XCB_HASH 0 0 0 0 0
BASE_XACT_HASH 0 0 0 0 0
XDES_HASH 0 0 0 0 0
BUF_WRITE_LOG 56 288 5,142857 0 0
RFS_THREAD_QUEUE 0 0 0 0 0
SVC_BROKER_CTRL 1 441 441 0 0
XACT_WORKSPACE 16 2 0,125 0 0
XCB 0 0 0 0 0
X_PACKET_LIST 14 10793 770,9286 0 1
QUERYEXEC 15 3 0,2 0 0
SVC_BROKER_LIST 8 78473 9809,125 0 6
LOGCACHE_ACCESS 41630 503034263 12083,46 0 39728
LSLIST 0 0 0 0 0
SVC_BROKER_DEBUG_LIST 0 0 0 0 0
CURSOR 0 0 0 0 0
READ_AHEAD 0 0 0 0 0
DROP 0 0 0 0 0
BLOCKER_ENUM 25 0 0 0 0
XID_ARRAY 2 0 0 0 0
LOCK_HASH 1481 3977124 2685,431 3 596
LOGLC 0 0 0 0 0
QE_SHUTDOWN 0 0 0 0 0
LOGLFM 0 0 0 0 0
PERIODIC 0 0 0 0 0
GHOST_HASH 1 0 0 0 0
ISSRESOURCE 0 0 0 0 0
XVB_CSN 0 0 0 0 0
XVB_LIST 0 0 0 0 0
FCB_REPLICA_SYNC 0 0 0 0 0
SUBPDESC_LIST 0 0 0 0 0
DIAG_OBJECT 0 0 0 0 0
XACT_LOCK_INFO 0 0 0 0 0
MDB_REMOTE_SESSION_PROXY 0 0 0 0 0
MDB_REMOTE_SESSION_PROXY_MANAG 0 0 0 0 0
MATRIX_DATA_VIRTUALIZATION_MAN 0 0 0 0 0
REMOTEOP 0 0 0 0 0
MATRIX_DATA_VIRTUALIZATION_MAN 0 0 0 0 0
XACT_LOCKINFO_TASK 0 0 0 0 0
GHOST_FREE 0 0 0 0 0
BUF_FREE_LIST 100372 192016016 1913,044 602 9283
XCB_FREE_LIST 0 0 0 0 0
LOCK_FREE_LIST 0 0 0 0 0
LSID 9 0 0 0 0
XDES 269 65850 244,7955 0 4
DBT_IO_LIST 0 0 0 0 0
IOREQ 0 0 0 0 0
XCHNG_TRACE 0 0 0 0 0
DROP_TEMPO 0 0 0 0 0
INDEX_CREATE 0 0 0 0 0
XTS_MGR 0 0 0 0 0
DATASET_FREELIST 0 0 0 0 0
SHARABLE_SESSION_OBJECTS 0 0 0 0 0
PROGRESS_REPORT 0 0 0 0 0
DIAG_MANAGER 1 0 0 0 0
SUBPDESC 0 0 0 0 0
CLR_HOSTTASK 0 0 0 0 0
MGR_CACHE 0 0 0 0 0
SETRANGE_SYNC 0 0 0 0 0
ALLOC_CACHES_HASH 7 17196 2456,572 0 1
LOCK_NOTIFICATION 0 0 0 0 0
COMPPLAN_SKELETON 0 0 0 0 0
LPE_SESSION 0 0 0 0 0
LPE_BATCH 185 75625 408,7838 0 4
LPE_SXTP 0 0 0 0 0
CLR_SPIN_LOCK 0 0 0 0 0
CURSQL 0 0 0 0 0
TSQL_DEBUG 0 0 0 0 0
LOCK_RESOURCE_ID 1 0 0 0 0
SEQUEUE_SIZED_THREADSAFE 0 0 0 0 0
DROPPED_ALLOC_UNIT 0 0 0 0 0
APPENDONLY_STORAGE 0 0 0 0 0
SNI 0 0 0 0 0
SESSION_MANAGER 23 5054 219,7391 0 0
CONNECTION_MANAGER 0 0 0 0 0
SESSION_SEC_CONTEXT 0 0 0 0 0
TSQL_NICE_SHUTDOWN 0 0 0 0 0
QUERY_EXEC_STATS 1 0 0 0 0
SUBLATCH 0 0 0 0 0
MCI 0 0 0 0 0
SPL_DISPATCHER_QUEUE 0 0 0 0 0
MATRIXDB_MTM_AGENT 0 0 0 0 0
MATRIXDB_MTM_TABLE 0 0 0 0 0
RPCDISPATCH 0 0 0 0 0
RPCRESPONDERCONTEXT 0 0 0 0 0
SPL_SOS_DISPATCHER 0 0 0 0 0
MATRIXDB_MTM_TXN 0 0 0 0 0
MATRIXDB_PRISM_STATE 0 0 0 0 0
SOS_TASK 287 2760435 9618,24 54 255
SOS_VM_LOW 0 0 0 0 0
TMP_SESS_KEY 0 0 0 0 0
WRITE_PAGE_RECORDER 0 0 0 0 0
OPT_IDX_MISS_KEY 0 0 0 0 0
FS_DELETED_FOLDER_CLEANUP 0 0 0 0 0
REPL_LOGREADER_HISTORY_CACHE 0 0 0 0 0
RPCPACKAGE 0 0 0 0 0
OPT_IDX_MISS_ID 0 0 0 0 0
SOS_NODE 0 0 0 0 0
SOS_SCHEDULER 3477 2330441 670,2448 9 140
SOS_TLIST 125 629482 5035,856 5 52
SOS_SELIST_SIZED_SLOCK 0 0 0 0 0
SOS_CACHESTORE 166 434476 2617,325 1 23
RESMANAGER 0 0 0 0 0
SOS_RW 791 11654608 14734,02 5 78
SOS_WAITABLE_ADDRESS_HASHBUCKE 1 0 0 0 0
SOS_ABORT_TASK 0 0 0 0 0
SOS_SYSTHREAD_DISPATCHER 5 10886 2177,2 0 1
SOS_OBJECT_STORE 390 141046 361,6564 0 5
SOS_SYSTHREAD 0 0 0 0 0
SOS_MINITHREAD 0 0 0 0 0
SOS_SUSPEND_QUEUE 43812 12446353 284,0855 37 645
RANGE_GENERATION 0 0 0 0 0
SOS_RINGBUFFER_RECORD 0 0 0 0 0
SOS_LARGEPAGE_ALLOCATOR 0 0 0 0 0
KTM_ENLISTMENT 0 0 0 0 0
ONDEMAND_TASK 0 0 0 0 0
REMOTE_SESSION_CACHE 0 0 0 0 0
RESQUEUE 3712 1664404 448,3847 9 110
OPT_INFO_MGR 0 0 0 0 0
OPT_IDX_STATS 0 0 0 0 0
FGCB_PRP_FILL 0 0 0 0 0
PARTITIONED_HEAP_FREE_LIST 0 0 0 0 0
DELAYED_PARTITIONED_STACK 8 17257 2157,125 0 0
FS_CONTAINER_LIST_WITH_DELETE 0 0 0 0 0
RPCCHANNELPOOL 0 0 0 0 0
RPCREQUESTORCONTEXT 0 0 0 0 0
CHANNELFORCECLOSEMANAGER 0 0 0 0 0
SOS_CLOCKALG_INTERNODE_SYNC 0 0 0 0 0
REPL_LOGREADER_PERDB_HISTORY_C 0 0 0 0 0
XE_SESSION_STORAGE 0 0 0 0 0
SPL_XE_DISPATCHER_QUEUE 0 0 0 0 0
SPL_XE_BUFFER_MGR 0 0 0 0 0
SPL_XE_SESSION_MGR 0 0 0 0 0
SPL_XE_SESSION_EVENT_MGR 0 0 0 0 0
SPL_XE_SESSION_TARGET_MGR 0 0 0 0 0
SPL_DISPATCHER_LIST 0 0 0 0 0
SPL_NONYIELD_ANALYSIS 0 0 0 0 0
MATRIXDB_MEMTABLE 0 0 0 0 0
CM_ROSTER 0 0 0 0 0
CM_ENLISTMENT 0 0 0 0 0
CM_FMPRISM 0 0 0 0 0
CMA_FMPRISM 0 0 0 0 0
CM_FMCONFIG 0 0 0 0 0
SOS_DEBUG_HOOK 0 0 0 0 0
TESTTEAM 0 0 0 0 0
TESTTEAMTASTAS 0 0 0 0 0
TESTTEAMEXPONENTIAL 0 0 0 0 0
TESTTEAMEXPONENTIALTASTAS 0 0 0 0 0
STACK_HASHER 0 0 0 0 0
(188 row(s) affected)
Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------- ------------- ----------------
MISCELLANEOUS 0 0 0
LCK_M_SCH_S 0 0 0
LCK_M_SCH_M 12 39 0
LCK_M_S 11 6754 60
LCK_M_U 2 2541 0
LCK_M_X 18 6240 1
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 391 656 423
LATCH_UP 0 0 0
LATCH_EX 615 10019 313
LATCH_DT 0 0 0
PAGELATCH_NL 0 0 0
PAGELATCH_KP 0 0 0
PAGELATCH_SH 21043 7409 729
PAGELATCH_UP 28 147 3
PAGELATCH_EX 18799 3535 386
PAGELATCH_DT 0 0 0
PAGEIOLATCH_NL 0 0 0
PAGEIOLATCH_KP 0 0 0
PAGEIOLATCH_SH 421527 920666 16404
PAGEIOLATCH_UP 3843 67204 42
PAGEIOLATCH_EX 133161 315374 16535
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 813381 5,965507E+08 274933
IO_COMPLETION 200936 123988 4061
ASYNC_IO_COMPLETION 14 18249 0
ASYNC_NETWORK_IO 1,22028E+07 2665460 615309
SLEEP_BPOOL_FLUSH 127396 311057 22500
CHKPT 1 1315 2
SLEEP_DBSTARTUP 36 3509 22
SLEEP_TEMPDBSTARTUP 0 0 0
SLEEP_DCOMSTARTUP 1 51 0
SLEEP_TASK 1392683 2,995382E+08 154864
SLEEP_SYSTEMTASK 1 1430 2
RESOURCE_SEMAPHORE 0 0 0
DTC 0 0 0
OLEDB 49256 433 0
FAILPOINT 0 0 0
RESOURCE_QUEUE 0 0 0
ASYNC_DISKPOOL_LOCK 0 0 0
THREADPOOL 279 200 0
DEBUG 0 0 0
REPLICA_WRITES 0 0 0
BROKER_RECEIVE_WAITFOR 18 3793968 46
DBMIRRORING_CMD 0 0 0
WAIT_FOR_RESULTS 0 0 0
SOS_SCHEDULER_YIELD 4,707928E+07 718599 651083
SOS_VIRTUALMEMORY_LOW 0 0 0
SOS_RESERVEDMEMBLOCKLIST 0 0 0
SOS_LOCALALLOCATORLIST 0 0 0
SOS_CALLBACK_REMOVAL 0 0 0
ONDEMAND_TASK_QUEUE 1 0 0
LOGMGR_QUEUE 8811 5,987272E+08 1927
REQUEST_FOR_DEADLOCK_SEARCH 119757 5,987846E+08 5,987846E+08
CHECKPOINT_QUEUE 763343 5,980449E+08 622807
PARALLEL_BACKUP_QUEUE 0 0 0
DUMP_LOG_COORDINATOR_QUEUE 0 0 0
LOWFAIL_MEMMGR_QUEUE 0 0 0
BACKUP 0 0 0
BACKUPBUFFER 1163 10058 376
BACKUPIO 1854 11674 15
BACKUPTHREAD 154 7087 7
DBMIRROR_DBM_MUTEX 0 0 0
DBMIRROR_DBM_EVENT 0 0 0
DBMIRROR_SEND 0 0 0
DBMIRROR_EVENTS_QUEUE 0 0 0
DBMIRROR_WORKER_QUEUE 0 0 0
HTTP_START 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
DEADLOCK_TASK_SEARCH 0 0 0
REPL_SCHEMA_ACCESS 0 0 0
REPL_CACHE_ACCESS 0 0 0
KSOURCE_WAKEUP 1 0 0
SQLSORT_SORTMUTEX 0 0 0
SQLSORT_NORMMUTEX 0 0 0
SQLTRACE_WAIT_ENTRIES 0 0 0
SQLTRACE_LOCK 60 384 0
SQLTRACE_BUFFER_FLUSH 149651 5,987764E+08 17577
SQLTRACE_SHUTDOWN 0 0 0
QUERY_TRACEOUT 0 0 0
DTC_STATE 0 0 0
BROKER_TRANSMITTER 2 0 0
BROKER_SERVICE 0 0 0
BROKER_SHUTDOWN 0 0 0
BROKER_MASTERSTART 1 0 0
BROKER_REGISTERALLENDPOINTS 0 0 0
BROKER_EVENTHANDLER 54 5,882839E+08 140
FCB_REPLICA_WRITE 0 0 0
FCB_REPLICA_READ 0 0 0
WRITELOG 7753 24119 648
EXCHANGE 0 0 0
EC 0 0 0
TEMPOBJ 0 0 0
XACTLOCKINFO 0 0 0
LOGMGR 0 0 0
CMEMTHREAD 241 179 116
CXPACKET 2625 20077 1937
SHUTDOWN 0 0 0
WAITFOR 0 0 0
EXECSYNC 8 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 1319 34591 0
MSQL_DQ 0 0 0
LOGBUFFER 21 4137 1
TRANSACTION_MUTEX 0 0 0
SLEEP_MSDBSTARTUP 0 0 0
MSSEARCH 0 0 0
XACTWORKSPACE_MUTEX 0 0 0
TRACEWRITE 0 0 0
WAITSTAT_MUTEX 0 0 0
WAITFOR_TASKSHUTDOWN 0 0 0
MISCELLANEOUS 0 0 0
GUARDIAN 0 0 0
CLR_TASK_START 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
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
BROKER_INIT 0 0 0
BROKER_CONNECTION_RECEIVE_TASK 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
SNI_HTTP_WAITFOR_0_DISCON 0 0 0
UTIL_PAGE_ALLOC 0 0 0
SERVER_IDLE_CHECK 0 0 0
DEADLOCK_ENUM_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
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
SNI_CRITICAL_SECTION 6 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
QUERY_OPTIMIZER_PRINT_MUTEX 0 0 0
DLL_LOADING_MUTEX 0 0 0
RESOURCE_SEMAPHORE_QUERY_COMPILE 0 0 0
RESOURCE_SEMAPHORE_SMALL_QUERY 0 0 0
BROKER_ENDPOINT_STATE_MUTEX 0 0 0
QUERY_EXECUTION_INDEX_SORT_EVENT 0 0 0
ERROR_REPORTING_MANAGER 0 0 0
EE_SPECPROC_MAP_INIT 0 0 0
FULLTEXT GATHERER 0 0 0
SEQUENTIAL_GUID 1 0 0
BROKER_TASK_STOP 2061 1,025267E+07 3354
SNI_TASK_COMPLETION 1 9 0
SNI_LISTENER_ACCESS 0 0 0
EXECUTION_PIPE_EVENT_INTERNAL 0 0 0
CLR_MEMORY_SPY 0 0 0
CLRHOST_STATE_ACCESS 0 0 0
DAC_INIT 1 3 0
ASSEMBLY_LOAD 0 0 0
VIA_ACCEPT 0 0 0
CHECK_PRINT_RECORD 0 0 0
INTERNAL_TESTING 0 0 0
FS_GARBAGE_COLLECTOR_SHUTDOWN 0 0 0
FSAGENT 0 0 0
ABR 0 0 0
WCC 0 0 0
DUMPTRIGGER 0 0 0
QUERY_WAIT_ERRHDL_SERVICE 0 0 0
QUERY_ERRHDL_SERVICE_DONE 0 0 0
TIMEPRIV_TIMEPERIOD 0 0 0
DISPATCHER_QUEUE_SEMAPHORE 0 0 0
XE_MODULEMGR_SYNC 0 0 0
XE_STM_CREATE 0 0 0
XE_SESSION_SYNC 0 0 0
XE_SESSION_CREATE_SYNC 0 0 0
XE_SERVICES_MUTEX 0 0 0
XE_SERVICES_RWLOCK 0 0 0
XE_SERVICES_EVENTMANUAL 0 0 0
XE_OLS_LOCK 0 0 0
SOS_DISPATCHER_MUTEX 0 0 0
XE_BUFFERMGR_FREEBUF_EVENT 0 0 0
XE_BUFFERMGR_ALLPROCESSED_EVENT 0 0 0
XE_DISPATCHER_JOIN 0 0 0
XE_TIMER_MUTEX 0 0 0
XE_TIMER_EVENT 19961 5,987709E+08 5,987706E+08
XE_TIMER_TASK_DONE 0 0 0
XE_DISPATCHER_WAIT 5 7,044048E+07 0
XE_DISPATCHER_CONFIG_SESSION_LIS 0 0 0
XE_SESSION_FLUSH 0 0 0
XE_PACKAGE_LOCK_BACKOFF 0 0 0
BROKER_TO_FLUSH 291943 2,993879E+08 121450
NODE_CACHE_MUTEX 0 0 0
RG_RECONFIG 0 0 0
RESMGR_THROTTLED 0 0 0
SOS_MEMORY_USAGE_ADJUSTMENT 0 0 0
SECURITY_MUTEX 0 0 0
FS_HEADER_RWLOCK 0 0 0
FS_LOGTRUNC_RWLOCK 0 0 0
FS_FC_RWLOCK 0 0 0
FSTR_CONFIG_RWLOCK 0 0 0
FSTR_CONFIG_MUTEX 0 0 0
FSA_FORCE_OWN_XACT 0 0 0
COMMIT_TABLE 0 0 0
CXROWSET_SYNC 0 0 0
PREEMPTIVE_OS_GENERICOPS 15 1393 0
PREEMPTIVE_OS_AUTHENTICATIONOPS 12269 5750 0
PREEMPTIVE_OS_ACCEPTSECURITYCONT 0 0 0
PREEMPTIVE_OS_ACQUIRECREDENTIALS 0 0 0
PREEMPTIVE_OS_COMPLETEAUTHTOKEN 0 0 0
PREEMPTIVE_OS_DECRYPTMESSAGE 2206 187 0
PREEMPTIVE_OS_DELETESECURITYCONT 2055 1121 0
PREEMPTIVE_OS_ENCRYPTMESSAGE 206 41 0
PREEMPTIVE_OS_FREECREDENTIALSHAN 0 0 0
PREEMPTIVE_OS_INITIALIZESECURITY 0 0 0
PREEMPTIVE_OS_LOGONUSER 0 0 0
PREEMPTIVE_OS_QUERYSECURITYCONTE 0 0 0
PREEMPTIVE_OS_VERIFYSIGNATURE 0 0 0
PREEMPTIVE_OS_AUTHORIZATIONOPS 2448 18491 0
PREEMPTIVE_OS_AUTHZGETINFORMATIO 10 0 0
PREEMPTIVE_OS_AUTHZINITIALIZECON 5 461 0
PREEMPTIVE_OS_AUTHZINITIALIZERES 5 3 0
PREEMPTIVE_OS_LOOKUPACCOUNTSID 4437 4010 0
PREEMPTIVE_OS_REVERTTOSELF 2163 170 0
PREEMPTIVE_OS_SETNAMEDSECURITYIN 0 0 0
PREEMPTIVE_OS_CLUSTEROPS 0 0 0
PREEMPTIVE_CLUSAPI_CLUSTERRESOUR 0 0 0
PREEMPTIVE_OS_COMOPS 54 164 0
PREEMPTIVE_COM_COCREATEINSTANCE 0 0 0
PREEMPTIVE_COM_COGETCLASSOBJECT 0 0 0
PREEMPTIVE_COM_CREATEACCESSOR 78 8 0
PREEMPTIVE_COM_DELETEROWS 0 0 0
PREEMPTIVE_COM_GETCOMMANDTEXT 0 0 0
PREEMPTIVE_COM_GETDATA 87551 109 0
PREEMPTIVE_COM_GETNEXTROWS 0 0 0
PREEMPTIVE_COM_GETRESULT 0 0 0
PREEMPTIVE_COM_GETROWSBYBOOKMARK 0 0 0
PREEMPTIVE_COM_LBFLUSH 0 0 0
PREEMPTIVE_COM_LBLOCKREGION 0 0 0
PREEMPTIVE_COM_LBREADAT 0 0 0
PREEMPTIVE_COM_LBSETSIZE 0 0 0
PREEMPTIVE_COM_LBSTAT 0 0 0
PREEMPTIVE_COM_LBUNLOCKREGION 0 0 0
PREEMPTIVE_COM_LBWRITEAT 0 0 0
PREEMPTIVE_COM_QUERYINTERFACE 55 10 0
PREEMPTIVE_COM_RELEASE 32 0 0
PREEMPTIVE_COM_RELEASEACCESSOR 74 2 0
PREEMPTIVE_COM_RELEASEROWS 49201 10 0
PREEMPTIVE_COM_RELEASESESSION 0 0 0
PREEMPTIVE_COM_RESTARTPOSITION 0 0 0
PREEMPTIVE_COM_SEQSTRMREAD 0 0 0
PREEMPTIVE_COM_SEQSTRMREADANDWRI 0 0 0
PREEMPTIVE_COM_SETDATAFAILURE 0 0 0
PREEMPTIVE_COM_SETPARAMETERINFO 0 0 0
PREEMPTIVE_COM_SETPARAMETERPROPE 0 0 0
PREEMPTIVE_COM_STRMLOCKREGION 0 0 0
PREEMPTIVE_COM_STRMSEEKANDREAD 0 0 0
PREEMPTIVE_COM_STRMSEEKANDWRITE 0 0 0
PREEMPTIVE_COM_STRMSETSIZE 0 0 0
PREEMPTIVE_COM_STRMSTAT 0 0 0
PREEMPTIVE_COM_STRMUNLOCKREGION 0 0 0
PREEMPTIVE_OS_CRYPTOPS 2 1190 0
PREEMPTIVE_OS_CRYPTACQUIRECONTEX 279 193 0
PREEMPTIVE_OS_CRYPTIMPORTKEY 197 137 0
PREEMPTIVE_OS_DEVICEOPS 0 0 0
PREEMPTIVE_OS_RSFXDEVICEOPS 0 0 0
PREEMPTIVE_OS_DIRSVC_NETWORKOPS 0 0 0
PREEMPTIVE_OS_DSGETDCNAME 0 0 0
PREEMPTIVE_OS_NETGROUPGETUSERS 0 0 0
PREEMPTIVE_OS_NETLOCALGROUPGETME 0 0 0
PREEMPTIVE_OS_NETUSERGETGROUPS 0 0 0
PREEMPTIVE_OS_NETUSERGETLOCALGRO 0 0 0
PREEMPTIVE_OS_NETUSERMODALSGET 0 0 0
PREEMPTIVE_OS_NETVALIDATEPASSWOR 18 89 0
PREEMPTIVE_OS_NETVALIDATEPASSWOR 18 0 0
PREEMPTIVE_OS_DOMAINSERVICESOPS 1 46 0
PREEMPTIVE_OS_DTCOPS 0 0 0
PREEMPTIVE_DTC_ABORT 0 0 0
PREEMPTIVE_DTC_ABORTREQUESTDONE 0 0 0
PREEMPTIVE_DTC_BEGINTRANSACTION 0 0 0
PREEMPTIVE_DTC_COMMITREQUESTDONE 0 0 0
PREEMPTIVE_DTC_ENLIST 0 0 0
PREEMPTIVE_DTC_PREPAREREQUESTDON 0 0 0
PREEMPTIVE_OS_FILEOPS 487 4421 0
PREEMPTIVE_OS_CLOSEHANDLE 1 0 0
PREEMPTIVE_OS_COPYFILE 0 0 0
PREEMPTIVE_OS_CREATEDIRECTORY 0 0 0
PREEMPTIVE_OS_CREATEFILE 133 214 0
PREEMPTIVE_OS_DELETEFILE 7 724 0
PREEMPTIVE_OS_DEVICEIOCONTROL 0 0 0
PREEMPTIVE_OS_FINDFILE 0 0 0
PREEMPTIVE_FILESIZEGET 25 8 0
PREEMPTIVE_OS_FLUSHFILEBUFFERS 264 72 0
PREEMPTIVE_OS_GETCOMPRESSEDFILES 0 0 0
PREEMPTIVE_OS_GETDISKFREESPACE 259 139 0
PREEMPTIVE_OS_GETFILEATTRIBUTES 139 98 0
PREEMPTIVE_OS_GETFILESIZE 0 0 0
PREEMPTIVE_OS_GETLONGPATHNAME 0 0 0
PREEMPTIVE_OS_GETVOLUMEPATHNAME 18 10 0
PREEMPTIVE_OS_GETVOLUMENAMEFORVO 21 3 0
PREEMPTIVE_OS_MOVEFILE 0 0 0
PREEMPTIVE_OS_OPENDIRECTORY 0 0 0
PREEMPTIVE_OS_REMOVEDIRECTORY 0 0 0
PREEMPTIVE_OS_SETENDOFFILE 0 0 0
PREEMPTIVE_OS_SETFILEPOINTER 0 0 0
PREEMPTIVE_OS_SETFILEVALIDDATA 0 0 0
PREEMPTIVE_OS_WRITEFILE 0 0 0
PREEMPTIVE_OS_WRITEFILEGATHER 260 1376324 0
PREEMPTIVE_OS_LIBRARYOPS 79 1737 0
PREEMPTIVE_OS_FREELIBRARY 0 0 0
PREEMPTIVE_OS_GETPROCADDRESS 1319 34240 0
PREEMPTIVE_OS_LOADLIBRARY 6 271 0
PREEMPTIVE_OS_MESSAGEQUEUEOPS 0 0 0
PREEMPTIVE_ODBCOPS 0 0 0
PREEMPTIVE_OLEDBOPS 388387 728 0
PREEMPTIVE_OLEDB_ABORTTRAN 0 0 0
PREEMPTIVE_OLEDB_ABORTORCOMMITTR 0 0 0
PREEMPTIVE_OLEDB_GETDATASOURCE 0 0 0
PREEMPTIVE_OLEDB_GETLITERALINFO 0 0 0
PREEMPTIVE_OLEDB_GETPROPERTIES 0 0 0
PREEMPTIVE_OLEDB_GETPROPERTYINFO 0 0 0
PREEMPTIVE_OLEDB_GETSCHEMALOCK 0 0 0
PREEMPTIVE_OLEDB_JOINTRANSACTION 0 0 0
PREEMPTIVE_OLEDB_RELEASE 0 0 0
PREEMPTIVE_OLEDB_SETPROPERTIES 0 0 0
PREEMPTIVE_OS_PIPEOPS 1 1206 0
PREEMPTIVE_OS_DISCONNECTNAMEDPIP 1691 600 0
PREEMPTIVE_OS_PROCESSOPS 0 0 0
PREEMPTIVE_OS_SECURITYOPS 0 0 0
PREEMPTIVE_OS_SERVICEOPS 0 0 0
PREEMPTIVE_OS_SQLCLROPS 0 0 0
PREEMPTIVE_OS_WINSOCKOPS 0 0 0
PREEMPTIVE_OS_GETADDRINFO 0 0 0
PREEMPTIVE_OS_WSASETLASTERROR 0 0 0
PREEMPTIVE_OS_FORMATMESSAGE 0 0 0
PREEMPTIVE_OS_REPORTEVENT 175 439 0
PREEMPTIVE_OS_BACKUPREAD 0 0 0
PREEMPTIVE_OS_WAITFORSINGLEOBJEC 1633 14385 0
PREEMPTIVE_OS_QUERYREGISTRY 9973 16409 0
PREEMPTIVE_CLOSEBACKUPMEDIA 0 0 0
PREEMPTIVE_CLOSEBACKUPTAPE 0 0 0
PREEMPTIVE_CLOSEBACKUPVDIDEVICE 0 0 0
PREEMPTIVE_OS_VSSOPS 0 0 0
PREEMPTIVE_VSS_CREATESNAPSHOT 0 0 0
PREEMPTIVE_VSS_CREATEVOLUMESNAPS 0 0 0
PREEMPTIVE_DFSADDLINK 0 0 0
PREEMPTIVE_DFSLINKEXISTCHECK 0 0 0
PREEMPTIVE_DFSLINKHEALTHCHECK 0 0 0
PREEMPTIVE_DFSREMOVELINK 0 0 0
PREEMPTIVE_DFSREMOVEROOT 0 0 0
PREEMPTIVE_DFSROOTFOLDERCHECK 0 0 0
PREEMPTIVE_DFSROOTINIT 0 0 0
PREEMPTIVE_DFSROOTSHARECHECK 0 0 0
PREEMPTIVE_OLE_UNINIT 0 0 0
PREEMPTIVE_FSAOLEDB_ABORTTRANSAC 0 0 0
PREEMPTIVE_FSAOLEDB_COMMITTRANSA 0 0 0
PREEMPTIVE_FSAOLEDB_STARTTRANSAC 0 0 0
PREEMPTIVE_FSRECOVER_UNCONDITION 0 0 0
PREEMPTIVE_SERVER_STARTUP 0 0 0
PREEMPTIVE_SHAREDMEM_GETDATA 0 0 0
PREEMPTIVE_CONSOLEWRITE 0 0 0
PREEMPTIVE_OS_SQMLAUNCH 8 4 0
PREEMPTIVE_TESTING 0 0 0
PREEMPTIVE_SOSHOST 0 0 0
PREEMPTIVE_SOSTESTING 0 0 0
PREEMPTIVE_XETESTING 0 0 0
PREEMPTIVE_SB_STOPENDPOINT 0 0 0
PREEMPTIVE_STARTRM 0 0 0
PREEMPTIVE_GETRMINFO 0 0 0
PREEMPTIVE_SETRMINFO 0 0 0
PREEMPTIVE_ROLLFORWARDREDO 0 0 0
PREEMPTIVE_ROLLFORWARDUNDO 0 0 0
PREEMPTIVE_RESIZELOG 0 0 0
PREEMPTIVE_REENLIST 0 0 0
PREEMPTIVE_TRANSIMPORT 0 0 0
PREEMPTIVE_UNMARSHALPROPAGATIONT 0 0 0
PREEMPTIVE_CREATEPARAM 0 0 0
PREEMPTIVE_STREAMFCB_RECOVER 0 0 0
PREEMPTIVE_STREAMFCB_CHECKPOINT 0 0 0
PREEMPTIVE_XE_CALLBACKEXECUTE 233390 374 0
PREEMPTIVE_XE_DISPATCHER 1 0 0
PREEMPTIVE_XE_ENGINEINIT 0 0 0
PREEMPTIVE_XE_GETTARGETSTATE 0 0 0
PREEMPTIVE_XE_SESSIONCOMMIT 1 0 0
PREEMPTIVE_XE_TARGETFINALIZE 0 0 0
PREEMPTIVE_XE_TARGETINIT 1 0 0
PREEMPTIVE_XE_TIMERRUN 1 0 0
PREEMPTIVE_SNIOPEN 0 0 0
PREEMPTIVE_DEBUG 0 0 0
PREEMPTIVE_MSS_RELEASE 0 0 0
PREEMPTIVE_LOCKMONITOR 1 0 0
PREEMPTIVE_STRESSDRIVER 0 0 0
CLEAR_DB 0 0 0
PREEMPTIVE_ABR 0 0 0
LOGGENERATION 0 0 0
IO_RETRY 0 0 0
WRITE_COMPLETION 2956 4274 30
AUDIT_XE_SESSION_MGR 0 0 0
AUDIT_ON_DEMAND_TARGET_LOCK 0 0 0
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG 0 0 0
PREEMPTIVE_AUDIT_ACCESS_SECLOG 0 0 0
AUDIT_LOGINCACHE_LOCK 0 0 0
AUDIT_GROUPCACHE_LOCK 0 0 0
FT_METADATA_MUTEX 0 0 0
FT_IFTSHC_MUTEX 1 1315 1
FT_IFTSISM_MUTEX 0 0 0
FT_IFTS_RWLOCK 0 0 0
FT_COMPROWSET_RWLOCK 0 0 0
FT_MASTER_MERGE 0 0 0
TRACE_EVTNOTIF 0 0 0
SOS_SMALL_PAGE_ALLOC 0 0 0
METADATA_LAZYCACHE_RWLOCK 0 0 0
IOAFF_RANGE_QUEUE 0 0 0
FT_IFTS_SCHEDULER_IDLE_WAIT 9898 5,938335E+08 2931
REPL_HISTORYCACHE_ACCESS 0 0 0
REPL_TRANHASHTABLE_ACCESS 0 0 0
PERFORMANCE_COUNTERS_RWLOCK 2 13 0
Total 6,465084E+07 5,461975E+09 1,200086E+09
(486 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4) DBCC SQLPERF(NETSTATS)
Statistic Value
-------------------------------- -------------
Network Reads 4,936478E+07
Network Writes 3,751799E+07
Network Bytes Read -1,686942E+09
Network Bytes Written -5,52061E+08
Command Queue Length 0
Max Command Queue Length 0
Worker Threads 0
Max Worker Threads 0
Network Threads 0
Max Network Threads 0
[/font]
///////////////////////////////////////////////////////////////////////
How should I interpret results of the above commands? What are the best ways of identifying the reason of the low performance?
Thank you!
January 19, 2010 at 1:06 am
Most of that information I'm not familiar with, but I would recommend checking your indexes and associated information , and possibly your disk drives, as some raid setups will still function at a reduced speed after a drive failure
January 19, 2010 at 7:41 am
We did an upgrade from SQL 2000 to SQL Server 2008 64 bit. We do not have any problems with our performance. What you need to keep in mind is that in 2008 the usage of tempDB is very high when compared to SQL 2000. So the drives should be configured properly keeping this in mind.
But we did notice some strange behavior on couple of stored procs. Some stored procs had a much higher parallel processing than when it was running in SQL 2000.
-Roy
January 19, 2010 at 7:46 am
I would start by gathering fresh performance statistics on affected tables and indexes ... it is not unseen that new RDBMS engines does not like performance stats gathered with previous or lower versions.
If that doesn't help I would trace the same query in both the old and the new RDBMS engines and look for differences 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply