sqlserver2008数据库优化常用脚本2精选.doc

sqlserver2008数据库优化常用脚本 --查询某个数据库的连接数select count(*) from Master.dbo.SysProcesses where dbid=db_id()--前10名其他等待类型??SELECT TOP 10 *?from sys.dm_os_wait_stats?ORDER BY wait_time_ms DESC?SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like PAGELATCH%? ?OR wait_type like LAZYWRITER_SLEEP%?--CPU的压力SELECT scheduler_id, current_tasks_count, runnable_tasks_count?FROM sys.dm_os_schedulersWHERE scheduler_id 255--表现最差的前10名使用查询SELECT TOP ?10 ? ?ProcedureName ? ?= t.text,?? ?ExecutionCount ? = s.execution_count,?? ?AvgExecutionTime = ?isnull ( s.total_elapsed_time / s.execution_count, 0 ),? ?AvgWorkerTime ? ?= s.total_worker_time / s.execution_count,? ?TotalWorkerTime ?= s.total_worker_time,? ?MaxLogicalReads ?= s.max_logical_reads,? ?MaxPhysicalReads = s.max_physical_reads,? ?MaxLogicalWrites = s.max_logical_writes,? ?CreationDateTime = s.creation_time,? ?CallsPerSecond ? = ?isnull ( s.execution_count / ?datediff ( ?second , s.creation_time, ?getdate ()), 0 )?FROM ?sys.dm_exec_query_stats s? ? CROSS ?APPLY sys.dm_exec_sql_text( s.sql_handle ) ?t ORDER BY ?? ?s.max_physical_reads ?DESC? ?? ?SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,?SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]FROM sys.dm_os_wait_stats--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。--查看进程所执行的SQL语句?if (select COUNT(*) from master.dbo.sysprocesses) 500begin?select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses ?a?endselect text,a.* from master.sys.sysprocesses ?aCROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) ?where a.spid = 51dbcc ? inputbuffer(53)with tbas(select blocking_session_id,session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests ?a