Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Low performance on SQL Server 2008 Std 64 bit

340 views

Skip to first unread message

Zaur Bahramov

unread,

Nov 5, 2009, 9:31:07 AM11/5/09

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!

Erland Sommarskog

unread,

Nov 7, 2009, 3:02:09 AM11/7/09

Zaur Bahramov (zbakh...@msn.com) writes:
> 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:

When you restored the old database from SQL 2000 to SQL 2008, did you run
sp_updatestats, or even better UPDATE STATISTICS WITH FULLSCAN on all
tables? All statistics are invalidated when you upgrade the database.

Even if you did that, there are still plenty of changes in the optimizer
from SQl 2000, and while they often are to the better, they may sometimes
backfire, partcularly for a query which is poorly written, or which does
not have indexes.

To troubleshoot it, the best is to run Profiler to see where the procedure
is stuck. If you see no activity at all, it may be stuck at a single
statement all the time. In that case, you can use my beta_lockinfo to
see which statment that it executes for the moment.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0 new messages