Prev: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
Next: Windows/SQL Authentication...
From: Zaur Bahramov on 5 Nov 2009 17:55
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:

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


///////////////////////////////////////////////////////////////////////

How should I interpret results of the above commands? What are the best
ways of identifying the reason of the low performance?

Thank you!

From: David Hay on 6 Nov 2009 10:50

WOW..

Kinda hard without knowing what the procedure is doing. Are the disks
configured the same as the old SQL 2000 box?

Did you defrag all your indexes and update all the statistics once you
moved everything over to SQL 2005?

Do you have the same number of file groups? Is TempDB configured the
same as the old SQL 2000 box?

How is the memory configured? Is there anything else running on the
box? AD, applications etc...

Good luck,

David Hay
From: TheSQLGuru on 6 Nov 2009 16:25
Nothing you posted is useful unfortunately. you need to see the actual
query plan that is executing. If you are VERY lucky, you will have some
form of cursor in there (which I highly suspect given that 'best case'
runtimes are in the many hour range) AND that there are one or more indexes
missing you can apply 'between' cursor transaction batches. Otherwise you
are screwed at this point because if a lot of work has been done it would
potentially have to be 'unwound' if you cancel the operation which could
take as long as it has been running to this point. tlog size/percent full
can be evaluated as an indicator here.

you can investigate current activity using a number of DMVs and/or profiler.
query plan information is available as well.

Personally I recommend you get a tuning consultant to hook up to your system
to see if they can help.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Zaur Bahramov" <zbakhramov(a)msn.com> wrote in message
news:%23uDkRsmXKHA.4360(a)TK2MSFTNGP04.phx.gbl...
> 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:
>
> 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
>
>
> ///////////////////////////////////////////////////////////////////////
>
> How should I interpret results of the above commands? What are the best
> ways of identifying the reason of the low performance?
>
> Thank you!
>


From: Zaur Bahramov on 9 Nov 2009 05:40
TheSQLGuru wrote:
> Nothing you posted is useful unfortunately. you need to see the actual
> query plan that is executing. If you are VERY lucky, you will have some
> form of cursor in there (which I highly suspect given that 'best case'
> runtimes are in the many hour range) AND that there are one or more indexes
> missing you can apply 'between' cursor transaction batches. Otherwise you
> are screwed at this point because if a lot of work has been done it would
> potentially have to be 'unwound' if you cancel the operation which could
> take as long as it has been running to this point. tlog size/percent full
> can be evaluated as an indicator here.
>
> you can investigate current activity using a number of DMVs and/or profiler.
> query plan information is available as well.
>
> Personally I recommend you get a tuning consultant to hook up to your system
> to see if they can help.
>

Our systsem is Windows Server 2008 R2 x64 which in fact is a VM on ESX
3.5 . We are going to do an upgrade to vSphere soon, since ESX 3.5 has
only an experimental support for Windows Server 2008.
As for the database it's about 150 Gb, log file is on a separate disk.
RAM 8 Gb - I've configured Minimum memory on SQL as 0 and max as 6656.
This was I give 1.5Gb to OS and the rest 6.5 Gb to SQL.
After restoring an fbk to sql 2008 I created several maintenance tasks
as follows and ran them:
1) Update statistics
2) Rebuild and reorganize indexes
3) Integrity check

Compatibility level set to 100.

In general that's all. Also, in the very beginning I've configured the
paging file as about 12Gb both min and max size, but then noticed in the
Task Manager -> Performance that the Memory column was all green
(7.6Gb), i.e. using all RAM for some reason, even when server was idle.
I've set the paging file to be managed by windows, and ot it's between
2.6-3.2Gb even when procs are running.

I've asked my chief to re-launch his procedure, that was usually running
18 hrs on old server, and that didn't complete even after 4 days on a
new server, and will see what happens.

Can you advise smth else on server configuration? What counters should I
use in these scenario, which DMVs to run and which values to read/compare?

From: Zaur Bahramov on 9 Nov 2009 06:42
Zaur Bahramov wrote:
> TheSQLGuru wrote:
>> Nothing you posted is useful unfortunately. you need to see the
>> actual query plan that is executing. If you are VERY lucky, you will
>> have some form of cursor in there (which I highly suspect given that
>> 'best case' runtimes are in the many hour range) AND that there are
>> one or more indexes missing you can apply 'between' cursor transaction
>> batches. Otherwise you are screwed at this point because if a lot of
>> work has been done it would potentially have to be 'unwound' if you
>> cancel the operation which could take as long as it has been running
>> to this point. tlog size/percent full can be evaluated as an
>> indicator here.
>>
>> you can investigate current activity using a number of DMVs and/or
>> profiler. query plan information is available as well.
>>
>> Personally I recommend you get a tuning consultant to hook up to your
>> system to see if they can help.
>>
>
> Our systsem is Windows Server 2008 R2 x64 which in fact is a VM on ESX
> 3.5 . We are going to do an upgrade to vSphere soon, since ESX 3.5 has
> only an experimental support for Windows Server 2008.
> As for the database it's about 150 Gb, log file is on a separate disk.
> RAM 8 Gb - I've configured Minimum memory on SQL as 0 and max as 6656.
> This was I give 1.5Gb to OS and the rest 6.5 Gb to SQL.
> After restoring an fbk to sql 2008 I created several maintenance tasks
> as follows and ran them:
> 1) Update statistics
> 2) Rebuild and reorganize indexes
> 3) Integrity check
>
> Compatibility level set to 100.
>
> In general that's all. Also, in the very beginning I've configured the
> paging file as about 12Gb both min and max size, but then noticed in the
> Task Manager -> Performance that the Memory column was all green
> (7.6Gb), i.e. using all RAM for some reason, even when server was idle.
> I've set the paging file to be managed by windows, and ot it's between
> 2.6-3.2Gb even when procs are running.
>
> I've asked my chief to re-launch his procedure, that was usually running
> 18 hrs on old server, and that didn't complete even after 4 days on a
> new server, and will see what happens.
>
> Can you advise smth else on server configuration? What counters should I
> use in these scenario, which DMVs to run and which values to read/compare?
>

Also, it's very strange, how comes that DBCC PROCCACHE on old Windows
2000 server gives:
num proc buffs 38717
num proc buffs used 38717
num proc buffs active 13287
proc cache size 32866
proc cache used 32866
proc cache active 7436

while on a new server:
num proc buffs 6358
num proc buffs used 208
num proc buffs active 208
proc cache size 400
proc cache used 13
proc cache active 13

I mean for example, why on the old server 'num proc buffs' is equal to
'num proc buffs used' while on a new server there's such a great difference?

Which other DMVs can I use to troubleshoot the issue?
 |  Next  |  Last
Pages: 1 2
Prev: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
Next: Windows/SQL Authentication...