SQL Server 2008数据库优化关键脚本：监控连接、等待类型与CPU压力
189 浏览量
更新于2024-08-31
收藏 52KB PDF 举报
在SQL Server 2008中，数据库优化是一项关键任务，确保系统的性能、稳定性和响应速度。本文档提供了几个实用的脚本，帮助管理员诊断和改进数据库性能。以下是其中的关键知识点：
1. **监控数据库连接数**:
- `SELECT count(*) FROM Master.dbo.SysProcesses WHERE dbid = db_id()`：这个脚本用于检查特定数据库的当前连接数，这对于了解并发负载和锁定情况至关重要。通过监控连接数，可以判断是否存在过多的连接请求，从而可能引发资源争抢。
2. **识别高耗时等待事件**:
- `SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC`：dm_os_wait_stats是系统表，用于跟踪SQL Server中的各种等待事件。排序结果可以帮助识别哪些操作或事务导致了长时间的等待，以便针对这些瓶颈进行优化。
- `SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGELATCH%' OR wait_type LIKE 'LAZYWRITER_SLEEP%'`：这两个查询特别关注页锁定（PAGELATCH）和懒惰写入（LAZYWRITER_SLEEP），它们通常是由于表锁竞争或I/O密集型操作造成的，需要分析和调整。
3. **评估CPU压力**:
- `SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255`：这个脚本提供各个调度器的CPU使用情况，包括当前任务数和可运行任务数，有助于识别哪个任务或计划可能导致CPU过载，可能需要重新考虑查询计划或索引设计。
4. **找出性能最差的查询**:
- `SELECT ... FROM sys.dm_exec_query_stats ... ORDER BY s.max_physical_reads DESC`: dm_exec_query_stats用于获取执行历史数据，包括查询名称、执行次数、平均执行时间和I/O操作。通过查看物理读取次数，可以识别那些频繁造成大量磁盘I/O的查询，进一步分析并优化查询语句。
5. **计算总体信号等待时间**:
- `SELECT SUM(signal_wait_time_ms) AS total_wait_time`：信号等待时间反映了SQL Server在等待被其他进程唤醒的时间。这个指标可以帮助确定是否有阻塞操作影响了整体性能。
这些脚本组合起来构成了一个强大的诊断工具，用于定位SQL Server 2008数据库中的性能瓶颈，为数据库优化提供了数据驱动的方法。通过定期运行和分析这些脚本的结果，管理员可以有效地优化查询性能、减少锁竞争和提高整体数据库效率。
SQL server 2008 数据库优化常用脚本数据库优化常用脚本
–查询某个数据库的连接数查询某个数据库的连接数
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 master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
where a.spid = ’51’
dbcc inputbuffer(53)
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8 as ‘memory_usage(KB)’,
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on
a.session_id=b.session_id
–当前进程数当前进程数
select * from master.dbo.sysprocesses
order by cpu desc
下载后可阅读完整内容，剩余3页未读，立即下载
219 浏览量
点击了解资源详情
581 浏览量
898 浏览量
811 浏览量
weixin_38663415
- 粉丝: 3
- 资源: 891
我的内容管理
展开
