优化数据库性能?

rjzou2006 2009-09-16 10:32:04

优化数据库性能?

这个是
select * from sys.dm_os_wait_stats
运行的结果





MISCELLANEOUS,0,0,0,0

LCK_M_SCH_S,0,0,0,0

LCK_M_SCH_M,4,92093,91062,0

LCK_M_S,600,329046,20515,31

LCK_M_U,37,207546,89109,0

LCK_M_X,9,375,203,0

LCK_M_IS,0,0,0,0

LCK_M_IU,0,0,0,0

LCK_M_IX,7,10546,3296,0

LCK_M_SIU,0,0,0,0

LCK_M_SIX,0,0,0,0

LCK_M_UIX,0,0,0,0

LCK_M_BU,0,0,0,0

LCK_M_RS_S,0,0,0,0

LCK_M_RS_U,0,0,0,0

LCK_M_RIn_NL,0,0,0,0

LCK_M_RIn_S,0,0,0,0

LCK_M_RIn_U,0,0,0,0

LCK_M_RIn_X,0,0,0,0

LCK_M_RX_S,0,0,0,0

LCK_M_RX_U,0,0,0,0

LCK_M_RX_X,0,0,0,0

LATCH_NL,0,0,0,0

LATCH_KP,0,0,0,0

LATCH_SH,460,187,15,31

LATCH_UP,0,0,0,0

LATCH_EX,121497,658093,25203,37828

LATCH_DT,0,0,0,0

PAGELATCH_NL,0,0,0,0

PAGELATCH_KP,0,0,0,0

PAGELATCH_SH,24547,2500,218,1109

PAGELATCH_UP,22869,8531,671,2453

PAGELATCH_EX,4197691,26906,484,20500

PAGELATCH_DT,0,0,0,0

PAGEIOLATCH_NL,0,0,0,0

PAGEIOLATCH_KP,0,0,0,0

PAGEIOLATCH_SH,12345,307437,1812,4187

PAGEIOLATCH_UP,1068,9656,984,15

PAGEIOLATCH_EX,11220,150093,2046,4843

PAGEIOLATCH_DT,0,0,0,0

TRAN_MARKLATCH_NL,0,0,0,0

TRAN_MARKLATCH_KP,0,0,0,0

TRAN_MARKLATCH_SH,0,0,0,0

TRAN_MARKLATCH_UP,0,0,0,0

TRAN_MARKLATCH_EX,0,0,0,0

TRAN_MARKLATCH_DT,0,0,0,0

LAZYWRITER_SLEEP,37604,37099609,1015,625

IO_COMPLETION,27193,264750,4281,250

ASYNC_IO_COMPLETION,1,93,93,0

ASYNC_NETWORK_IO,117180,402062,2156,9359

SLEEP_BPOOL_FLUSH,47894,230359,31,1640

CHKPT,1,2109,2109,0

SLEEP_TASK,211275,36798125,546,36735078

SLEEP_SYSTEMTASK,1,2109,2109,0

RESOURCE_SEMAPHORE,0,0,0,0

DTC,0,0,0,0

OLEDB,363752,4140218,48531,0

FAILPOINT,0,0,0,0

RESOURCE_QUEUE,0,0,0,0

ASYNC_DISKPOOL_LOCK,0,0,0,0

THREADPOOL,10,62,15,0

DEBUG,0,0,0,0

REPLICA_WRITES,0,0,0,0

BROKER_RECEIVE_WAITFOR,0,0,0,0

DBMIRRORING_CMD,0,0,0,0

WAIT_FOR_RESULTS,0,0,0,0

SOS_SCHEDULER_YIELD,5276222,108281,109,105640

SOS_VIRTUALMEMORY_LOW,0,0,0,0

SOS_RESERVEDMEMBLOCKLIST,118,31,15,31

SOS_LOCALALLOCATORLIST,4,0,0,0

SOS_CALLBACK_REMOVAL,0,0,0,0

LOWFAIL_MEMMGR_QUEUE,0,0,0,0

BACKUP,0,0,0,0

BACKUPBUFFER,0,0,0,0

BACKUPIO,0,0,0,0

BACKUPTHREAD,0,0,0,0

DBMIRROR_DBM_MUTEX,0,0,0,0

DBMIRROR_DBM_EVENT,0,0,0,0

DBMIRROR_SEND,0,0,0,0

CURSOR_ASYNC,0,0,0,0

HTTP_ENUMERATION,0,0,0,0

SOAP_READ,0,0,0,0

SOAP_WRITE,0,0,0,0

DUMP_LOG_COORDINATOR,0,0,0,0

DISKIO_SUSPEND,0,0,0,0

IMPPROV_IOWAIT,0,0,0,0

QNMANAGER_ACQUIRE,0,0,0,0

DEADLOCK_TASK_SEARCH,0,0,0,0

REPL_SCHEMA_ACCESS,0,0,0,0

REPL_CACHE_ACCESS,0,0,0,0

SQLSORT_SORTMUTEX,0,0,0,0

SQLSORT_NORMMUTEX,0,0,0,0

SQLTRACE_WAIT_ENTRIES,0,0,0,0

SQLTRACE_LOCK,50,3640,781,0

SQLTRACE_BUFFER_FLUSH,9287,37144546,4031,140

SQLTRACE_SHUTDOWN,0,0,0,0

MSQL_SYNC_PIPE,0,0,0,0

QUERY_TRACEOUT,0,0,0,0

DTC_STATE,0,0,0,0

FCB_REPLICA_WRITE,0,0,0,0

FCB_REPLICA_READ,0,0,0,0

WRITELOG,122829,230296,3593,12343

HTTP_ENDPOINT_COLLCREATE,0,0,0,0

EXCHANGE,0,0,0,0

DBTABLE,0,0,0,0

EC,0,0,0,0

TEMPOBJ,0,0,0,0

XACTLOCKINFO,0,0,0,0

LOGMGR,0,0,0,0

CMEMTHREAD,41904,6828,15,5953

CXPACKET,58856,703218,7296,19000

WAITFOR,0,0,0,0

CURSOR,0,0,0,0

EXECSYNC,1051,1656,31,203

SOSHOST_INTERNAL,0,0,0,0

SOSHOST_SLEEP,0,0,0,0

SOSHOST_WAITFORDONE,0,0,0,0

SOSHOST_MUTEX,0,0,0,0

SOSHOST_EVENT,0,0,0,0

SOSHOST_SEMAPHORE,0,0,0,0

SOSHOST_RWLOCK,0,0,0,0

SOSHOST_TRACELOCK,0,0,0,0

MSQL_XP,3952,35093,4921,0

MSQL_DQ,13969,18328,296,0

LOGBUFFER,455,78328,2562,93

TRANSACTION_MUTEX,0,0,0,0

MSSEARCH,0,0,0,0

XACTWORKSPACE_MUTEX,0,0,0,0

CLR_JOIN,0,0,0,0

CLR_CRST,47,0,0,0

CLR_SEMAPHORE,0,0,0,0

CLR_MANUAL_EVENT,1499,44728281,30015,546

CLR_AUTO_EVENT,23,60382328,11459437,0

CLR_MONITOR,0,0,0,0

CLR_RWLOCK_READER,0,0,0,0

CLR_RWLOCK_WRITER,0,0,0,0

SQLCLR_QUANTUM_PUNISHMENT,0,0,0,0

SQLCLR_APPDOMAIN,0,0,0,0

SQLCLR_ASSEMBLY,9,46,15,0

KTM_ENLISTMENT,0,0,0,0

KTM_RECOVERY_RESOLUTION,0,0,0,0

KTM_RECOVERY_MANAGER,0,0,0,0

SQLCLR_DEADLOCK_DETECTION,0,0,0,0

QPJOB_WAITFOR_ABORT,0,0,0,0

QPJOB_KILL,0,0,0,0

BAD_PAGE_PROCESS,0,0,0,0

BACKUP_OPERATOR,0,0,0,0

PRINT_ROLLBACK_PROGRESS,0,0,0,0

ENABLE_VERSIONING,0,0,0,0

DISABLE_VERSIONING,0,0,0,0

REQUEST_DISPENSER_PAUSE,0,0,0,0

DROPTEMP,0,0,0,0

FT_RESTART_CRAWL,0,0,0,0

FT_RESUME_CRAWL,0,0,0,0

LOGMGR_RESERVE_APPEND,0,0,0,0

LOGMGR_FLUSH,0,0,0,0

XACT_OWN_TRANSACTION,0,0,0,0

XACT_RECLAIM_SESSION,0,0,0,0

DTC_WAITFOR_OUTCOME,0,0,0,0

DTC_RESOLVE,0,0,0,0

SEC_DROP_TEMP_KEY,0,0,0,0

SRVPROC_SHUTDOWN,0,0,0,0

NET_WAITFOR_PACKET,0,0,0,0

DTC_ABORT_REQUEST,0,0,0,0

DTC_TMDOWN_REQUEST,0,0,0,0

RECOVER_CHANGEDB,0,0,0,0

WORKTBL_DROP,0,0,0,0

MIRROR_SEND_MESSAGE,0,0,0,0

SNI_HTTP_ACCEPT,0,0,0,0

SNI_HTTP_WAITFOR_0_DISCON,0,0,0,0

UTIL_PAGE_ALLOC,0,0,0,0

SERVER_IDLE_CHECK,0,0,0,0

BACKUP_CLIENTLOCK,0,0,0,0

DEADLOCK_ENUM_MUTEX,0,0,0,0

INDEX_USAGE_STATS_MUTEX,0,0,0,0

VIEW_DEFINITION_MUTEX,0,0,0,0

QUERY_NOTIFICATION_MGR_MUTEX,0,0,0,0

QUERY_NOTIFICATION_TABLE_MGR_MUTEX,0,0,0,0

QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX,0,0,0,0

QUERY_NOTIFICATION_UNITTEST_MUTEX,0,0,0,0

IMP_IMPORT_MUTEX,0,0,0,0

RESOURCE_SEMAPHORE_MUTEX,124,0,0,0

IO_AUDIT_MUTEX,0,0,0,0

BUILTIN_HASHKEY_MUTEX,0,0,0,0

SOS_PROCESS_AFFINITY_MUTEX,0,0,0,0

MSQL_XACT_MGR_MUTEX,0,0,0,0

MSQL_XACT_MUTEX,0,0,0,0

QRY_MEM_GRANT_INFO_MUTEX,0,0,0,0

SOS_STACKSTORE_INIT_MUTEX,0,0,0,0

SOS_SYNC_TASK_ENQUEUE_EVENT,12,0,0,0

SOS_OBJECT_STORE_DESTROY_MUTEX,0,0,0,0

EE_PMOLOCK,2,0,0,0

RESOURCE_SEMAPHORE_QUERY_COMPILE,0,0,0,0

RESOURCE_SEMAPHORE_SMALL_QUERY,0,0,0,0

FULLTEXT GATHERER,0,0,0,0

SEQUENTIAL_GUID,0,0,0,0

BROKER_TASK_STOP,23,176671,10000,15

SNI_LISTENER_ACCESS,0,0,0,0

EXECUTION_PIPE_EVENT_INTERNAL,0,0,0,0

ASSEMBLY_LOAD,0,0,0,0

TIMEPRIV_TIMEPERIOD,0,0,0,0

INTERNAL_TESTING,0,0,0,0