原帖由 lovexueer 于 2008-9-3 16:01 发表
请贴你存储过程的代码.
及运行SP后,再次把select结果贴出来
再把DBCC sqlperf (waitstats)结果贴出来
帮我看看我这个服务器有什么问题吧
select的结果
lastwaittype waitresource waittime
ONDEMAND_TASK_QUEUE 1369646109
BROKER_TRANSMITTER 1369638859
BROKER_TRANSMITTER 1369638671
KSOURCE_WAKEUP 1369493156
BROKER_EVENTHANDLER 73546578
CHECKPOINT_QUEUE 685734
LOGMGR_QUEUE 3843
REQUEST_FOR_DEADLOCK_SEARCH 1593
SQLTRACE_BUFFER_FLUSH 796
LAZYWRITER_SLEEP 703
SLEEP_TASK 0
MISCELLANEOUS 0
DBCC sqlperf (waitstats) 的结果
Wait Type Requests Wait Time Signal Wait Time
MISCELLANEOUS 0 0 0
LCK_M_SCH_S 1 171 0
LCK_M_SCH_M 3184 687750 47578
LCK_M_S 970907 1.124889E+07 1307765
LCK_M_U 179849 1112312 27046
LCK_M_X 133766 3362250 89359
LCK_M_IS 50 375 15
LCK_M_IU 0 0 0
LCK_M_IX 60661 632671 64234
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 686 16843 4421
LATCH_UP 0 0 0
LATCH_EX 5521 45937 12265
LATCH_DT 0 0 0
PAGELATCH_NL 0 0 0
PAGELATCH_KP 0 0 0
PAGELATCH_SH 433842 2180921 635140
PAGELATCH_UP 66 1343 140
PAGELATCH_EX 207281 370421 61890
PAGELATCH_DT 0 0 0
PAGEIOLATCH_NL 0 0 0
PAGEIOLATCH_KP 0 0 0
PAGEIOLATCH_SH 187869 8425328 265734
PAGEIOLATCH_UP 7094 32453 312
PAGEIOLATCH_EX 12134 369687 9937
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 2500929 1.367835E+09 5457093
IO_COMPLETION 9728 654578 281
ASYNC_IO_COMPLETION 7 3968 0
ASYNC_NETWORK_IO 367226 571843 58562
SLEEP_BPOOL_FLUSH 40202 385187 281484
CHKPT 1 3578 0
SLEEP_TASK 1.896073E+07 2321031 778953
SLEEP_SYSTEMTASK 1 3546 0
RESOURCE_SEMAPHORE 5080 1.447798E+07 169546
DTC 0 0 0
OLEDB 1449175 1.795676E+07 0
FAILPOINT 0 0 0
RESOURCE_QUEUE 0 0 0
ASYNC_DISKPOOL_LOCK 0 0 0
THREADPOOL 4065 238640 0
DEBUG 0 0 0
REPLICA_WRITES 0 0 0
BROKER_RECEIVE_WAITFOR 0 0 0
DBMIRRORING_CMD 0 0 0
WAIT_FOR_RESULTS 0 0 0
SOS_SCHEDULER_YIELD 2.271372E+07 2.75143E+09 2.751332E+09
SOS_VIRTUALMEMORY_LOW 0 0 0
SOS_RESERVEDMEMBLOCKLIST 77 140 0
SOS_LOCALALLOCATORLIST 0 0 0
SOS_CALLBACK_REMOVAL 0 0 0
LOWFAIL_MEMMGR_QUEUE 0 0 0
BACKUP 0 0 0
BACKUPBUFFER 19 953 0
BACKUPIO 333 250 0
BACKUPTHREAD 28 1156 0
DBMIRROR_DBM_MUTEX 0 0 0
DBMIRROR_DBM_EVENT 0 0 0
DBMIRROR_SEND 0 0 0
CURSOR_ASYNC 0 0 0
HTTP_ENUMERATION 0 0 0
SOAP_READ 0 0 0
SOAP_WRITE 0 0 0
DUMP_LOG_COORDINATOR 0 0 0
DISKIO_SUSPEND 0 0 0
IMPPROV_IOWAIT 0 0 0
QNMANAGER_ACQUIRE 0 0 0
DEADLOCK_TASK_SEARCH 0 0 0
REPL_SCHEMA_ACCESS 0 0 0
REPL_CACHE_ACCESS 0 0 0
SQLSORT_SORTMUTEX 0 0 0
SQLSORT_NORMMUTEX 0 0 0
SQLTRACE_WAIT_ENTRIES 0 0 0
SQLTRACE_LOCK 1 46 0
SQLTRACE_BUFFER_FLUSH 342016 1.369791E+09 1486062
SQLTRACE_SHUTDOWN 0 0 0
MSQL_SYNC_PIPE 0 0 0
QUERY_TRACEOUT 0 0 0
DTC_STATE 0 0 0
FCB_REPLICA_WRITE 0 0 0
FCB_REPLICA_READ 0 0 0
WRITELOG 3524620 9444968 953453
HTTP_ENDPOINT_COLLCREATE 0 0 0
EXCHANGE 0 0 0
DBTABLE 0 0 0
EC 0 0 0
TEMPOBJ 0 0 0
XACTLOCKINFO 0 0 0
LOGMGR 0 0 0
CMEMTHREAD 7251 83562 23625
CXPACKET 4273 1.581308E+07 4625
WAITFOR 0 0 0
CURSOR 0 0 0
EXECSYNC 488 721671 656
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 150427 1.203797E+07 0
MSQL_DQ 19652 406515 0
LOGBUFFER 373 18484 1546
TRANSACTION_MUTEX 0 0 0
MSSEARCH 282784 598578 0
XACTWORKSPACE_MUTEX 0 0 0
CLR_JOIN 0 0 0
CLR_CRST 0 0 0
CLR_SEMAPHORE 0 0 0
CLR_MANUAL_EVENT 0 0 0
CLR_AUTO_EVENT 0 0 0
CLR_MONITOR 0 0 0
CLR_RWLOCK_READER 0 0 0
CLR_RWLOCK_WRITER 0 0 0
SQLCLR_QUANTUM_PUNISHMENT 0 0 0
SQLCLR_APPDOMAIN 0 0 0
SQLCLR_ASSEMBLY 0 0 0
KTM_ENLISTMENT 0 0 0
KTM_RECOVERY_RESOLUTION 0 0 0
KTM_RECOVERY_MANAGER 0 0 0
SQLCLR_DEADLOCK_DETECTION 0 0 0
QPJOB_WAITFOR_ABORT 0 0 0
QPJOB_KILL 0 0 0
BAD_PAGE_PROCESS 0 0 0
BACKUP_OPERATOR 0 0 0
PRINT_ROLLBACK_PROGRESS 0 0 0
ENABLE_VERSIONING 0 0 0
DISABLE_VERSIONING 0 0 0
REQUEST_DISPENSER_PAUSE 0 0 0
DROPTEMP 0 0 0
FT_RESTART_CRAWL 0 0 0
FT_RESUME_CRAWL 0 0 0
LOGMGR_RESERVE_APPEND 0 0 0
LOGMGR_FLUSH 0 0 0
XACT_OWN_TRANSACTION 0 0 0
XACT_RECLAIM_SESSION 0 0 0
DTC_WAITFOR_OUTCOME 0 0 0
DTC_RESOLVE 0 0 0
SEC_DROP_TEMP_KEY 0 0 0
SRVPROC_SHUTDOWN 0 0 0
NET_WAITFOR_PACKET 0 0 0
DTC_ABORT_REQUEST 0 0 0
DTC_TMDOWN_REQUEST 0 0 0
RECOVER_CHANGEDB 0 0 0
WORKTBL_DROP 0 0 0
MIRROR_SEND_MESSAGE 0 0 0
SNI_HTTP_ACCEPT 0 0 0
SNI_HTTP_WAITFOR_0_DISCON 0 0 0
UTIL_PAGE_ALLOC 0 0 0
SERVER_IDLE_CHECK 0 0 0
BACKUP_CLIENTLOCK 0 0 0
DEADLOCK_ENUM_MUTEX 0 0 0
INDEX_USAGE_STATS_MUTEX 0 0 0
VIEW_DEFINITION_MUTEX 0 0 0
QUERY_NOTIFICATION_MGR_MUTEX 0 0 0
QUERY_NOTIFICATION_TABLE_MGR_MUT 0 0 0
QUERY_NOTIFICATION_SUBSCRIPTION_ 0 0 0
QUERY_NOTIFICATION_UNITTEST_MUTE 0 0 0
IMP_IMPORT_MUTEX 0 0 0
RESOURCE_SEMAPHORE_MUTEX 21419 25312 12859
IO_AUDIT_MUTEX 374 46 15
BUILTIN_HASHKEY_MUTEX 0 0 0
SOS_PROCESS_AFFINITY_MUTEX 0 0 0
MSQL_XACT_MGR_MUTEX 0 0 0
MSQL_XACT_MUTEX 0 0 0
QRY_MEM_GRANT_INFO_MUTEX 0 0 0
SOS_STACKSTORE_INIT_MUTEX 0 0 0
SOS_SYNC_TASK_ENQUEUE_EVENT 0 0 0
SOS_OBJECT_STORE_DESTROY_MUTEX 0 0 0
EE_PMOLOCK 0 0 0
RESOURCE_SEMAPHORE_QUERY_COMPILE 264 1680546 11390
RESOURCE_SEMAPHORE_SMALL_QUERY 0 0 0
FULLTEXT GATHERER 8 1046 296
SEQUENTIAL_GUID 0 0 0
BROKER_TASK_STOP 34 123500 15
SNI_LISTENER_ACCESS 0 0 0
EXECUTION_PIPE_EVENT_INTERNAL 0 0 0
ASSEMBLY_LOAD 0 0 0
INTERNAL_TESTING 0 0 0
Total 5.260822E+07 5.595119E+09 2.763098E+09