sqlserver2008数据库优化常用脚本2资料.pdfVIP

sqlserver2008 数据库优化常用脚本 kanni -- 查询某个数据库的连接数 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_schedulers WHERE 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) 500 begin select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from mas