admin管理员组文章数量:1655503
补充:常规服务器动态管理对象包括,下面有些资料可能会应用到
dm_db_*:数据库和数据库对象
dm_exec_*:执行用户代码和关联的连接
dm_os_*:内存、锁定和时间安排
dm_tran_*:事务和隔离
dm_io_*:网络和磁盘的输入/输出
优化性能的常用方法是检索速度最慢的查询构成您 SQL Server 实例上的正常、 每日工作负载的一部分,然后调整它们,一个接一个的"Top 10"列表。 跟踪会话、 请求
和 SQL Server 基础架构中的最耗费大量资源,查询和执行时间最长。
稍微科学的方法可能会开始在较低级别,寻找特定区域,其中 SQL Server 遇到资源压力。 检查以确定其中等待进程完成后再进行一些其他操作异常长时间。 这种方式
,可以计算出是否缓慢的执行时间的主要组件是 CPU 时间 (如果系统是 cpu) 或时间所用等待 I/O (如果系统是 O 绑定) 等我们从自顶向下的方法去开始美妙的优
化过程
1) 分析实例级的等待(从最近一次SQL服务启动开始计数)
a.在实例中找到哪些等待类型占用了大部分时间
select * from sys.dm_os_wait_stats order by wait_type
b.分离出重量级等待即占总耗时80%或90%的等待类型
;with wait as
(
select
wait_type,
wait_time_ms /1000 as wait_time_ms,
100*wait_time_ms/SUM(wait_time_ms) over() as pct,
ROW_NUMBER() over(order by wait_time_ms desc) as rn
from sys.dm_os_wait_stats
where wait_type not like '%SLEEP%'
)
select
a.wait_type as 等待类型,
round(a.wait_time_ms,2) as 等待时间秒,
ROUND(a.pct,2) as 占百分比,
ROUND(SUM(b.pct),2) as 百分比
from wait as a
join wait as b
on b.rn<=a.rn
group by a.rn,a.wait_type,a.wait_time_ms ,a.pct
having SUM(b.pct )-a.pct<90
order by a.rn
附图(服务器96上的主要等待类型统计)
c.找到对应问题的领域开始执行下一步优化(等待类型是哪个资源引起的可以参考联机丛书,里面有每个类型的详细解释ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-
CHS/s10de_6tsql/html/568d89ed-2c96-4795-8a0c-2f3e375081da.htm)
下面对附图我们前几个等待做一个详细的分析
REQUEST_FOR_DEADLOCK_SEARCH 联机丛书的解释是(在死锁监视器等待开始下一次死锁搜索时出现。在两次死锁检测之间可能出现该等待,长时间等待此资源并不指示出
现问题)这个系统os调度的原因我们忽略
XE_TIMER_EVENT(SQL自身的原因,这就不多解释了,具体解释可以查联机丛书)
SQLTRACE_BUFFER_FLUSH(当某任务正在等待后台任务将跟踪缓冲区每隔四秒刷新到磁盘时出现)这应该是SQL缓存中Checkpoint定时写入磁盘引起的,也是SQL自身忽略
LOGMGR_QUEUE(在日志编写器任务等待工作请求时出现)这是日志编写空间空闲等待记录日志造成,忽略
……庆幸的是,下面几个主要也是OS内部调度造成的,OK这系统实例级的等待中不存在大问题
OK,以上示例是本人针对公司一台主要服务器做的统计,没大问题不代表其它服务器不出现瓶颈问题,那实例及的等待中主要是由有哪些类型造成瓶颈呢?这边列出了几个
最可能会出现瓶颈的等待,并提出对产出该瓶颈时的优化方案,篇幅会很长
对优化分析获取 SQL Server 平稳运行时性能计数器和主要 DMV 查询输出的基线非常重要。这里我先要引出利用DMV查询来做数据透视表的方法,常见的性能计数器基线
做法我会另写一篇博文
a.创建表waitstas 用于记录各时段等待信息
use ZhouWei
select getdate() as DT,wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms
into waitstas from sys.dm_os_wait_stats where 1=2
alter table waitstas add constraint pk_waitstas primary key(dt,wait_type)
create index dt_index on waitstas(wait_type,dt)
b.建立作业收集wait消息(可以每小时执行一次)
Insert into monitor.dbo.WaitStats
(wait_type, waiting_tasks_count, wait_time_ms,
max_wait_time_ms, signal_wait_time_ms)
select wait_type, waiting_tasks_count, wait_time_ms,
max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'MISCELLANEOUS')
c.建立函数收集每次执行作业间隔中的wait消息
CREATE FUNCTION [dbo].[IntervalWaits]
(@fromdt AS DATETIME, @todt AS DATETIME)
RETURNS TABLE
AS
RETURN
WITH Waits AS
(
SELECT dt, wait_type, wait_time_ms,
ROW_NUMBER() OVER(PARTITION BY wait_type
ORDER BY dt) AS rn
FROM dbo.WaitStats
)
SELECT Prv.wait_type, Prv.dt AS start_time,
CAST((Cur.wait_time_ms - Prv.wait_time_ms)
/ 1000. AS NUMERIC(12, 2)) AS interval_wait_s
FROM Waits AS Cur
JOIN Waits AS Prv
ON Cur.wait_type = Prv.wait_type
AND Cur.rn = Prv.rn + 1
AND Prv.dt >= @fromdt
AND Prv.dt < DATEADD(day, 1, @todt)
d.创建视图用于透视表的数据源
CREATE VIEW [dbo].[IntervalWaitsSample]
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20111201', '20111202') AS F
where wait_type='????' --根据前面的实例分析中得到的,选择你要分析的wait_type类型
;
GO
e.通过EXCEL 透视表对上一步骤进行分析
附图
一. IO类型的等待是最常出现的
1.我们先对IO信息做个统计具体可以参考以下语句
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count as avg_waittime from
sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' and waiting_tasks_count > 0 order by wait_type
附图2 注IO类型有些等待是破环类型长时间等待会造成磁盘损坏引起重视
2.下面我们细化到数据库(通过此查询可以分析出具体哪个数据库占用了大量的IO资源)用动态管理函数DMV来返回相关的信息
;with dbio AS
(
select DB_NAME(ivfs.database_id) as db,
case when mf.type=1 then 'log' else 'database' end as file_type,
SUM(ivfs.num_of_bytes_read+ivfs.num_of_bytes_written) as io,
SUM(ivfs.io_stall) as io_stall
from sys.dm_io_virtual_file_stats(null,null) as ivfs
join sys.master_files as mf on ivfs.database_id =mf.database_id and ivfs.file_id=mf.file_id
group by DB_NAME(ivfs.database_id),mf.type
)select db AS 数据库名,file_type as 文件类型,
cast(1.* io/(1024*1024) AS decimal(12,2)) AS IO流量,
CAST(io_stall/1000. as decimal(12,2)) as IO等待时间,
cast(100.*io_stall/sum(io_stall)over() as decimal(12,2)) as 占百分比,
row_number() over(order by io_stall desc) as rn
from dbio
order by io_stall desc;
附图3
分析出是否是数据库数据事物日志还是数据库数据区,如果是日志考虑是否配置合理,是否放到独立磁盘,是否磁盘驱动器够快等信息等。分析数据有两种方法:第一是要
建立跟踪具体是在Profiler中生成跟踪代码,然后自己新建脚本执行,原因是在优化器中执行其实是做了两步操作,还有怎么从分析资料中获取有效信息我后面再做整理,
第二我们建立以下代码可用于查找哪些批处理/请求生成的 I/O 最多。如下所示的 DMV 查询可用于查找可生成最多 I/O 的前15个请求。调整这些查询将提高系统性能。
select top 15(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,statement_start_offset as stmt_start_offset,
plan_handle,
query_stats.statement_text AS "Statement Text"
from (SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats order by (total_logical_reads +total_logical_writes) Desc
二. CPU 瓶颈通常由以下原因引起:查询计划并非最优、配置不当、设计因素不良或硬件资源不足。下面的常用查询可帮助您确定导致 CPU 瓶颈的原因。
1.下面的查询代码是当前缓存中的哪些批处理或过程占用了大部分CPU资源。
SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time,SUM(qs.execution_count) AS total_execution_count,COUNT(*) AS number_of_statements,
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) as sql_text
FROM sys.dm_exec_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC
2. 下面的查询显示 CPU 平均占用率最高的前 10 个 SQL 语句。
SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
3. 下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
三.阻塞
运行下面的查询可确定阻塞的会话。
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
使用此调用可找出 blocking_session_id 所返回的 SQL。例如,如果 blocking_session_id 是 87,则运行此查询可获得相应的 SQL。
dbcc INPUTBUFFER(87)
若要找出哪个 spid 正在阻塞另一个 spid,可在数据库中创建以下存储过程,然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid;@spid
是可选参数。
create proc dbo.sp_block (@spid bigint=NULL) as select t1.resource_type, 'database'=db_name(resource_database_id), 'blk object' =
t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id from sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address and t1.request_session_id = isnull
(@spid,t1.request_session_id)
以下是使用此存储过程的示例。
exec sp_block exec sp_block @spid = 7
概览:- 数据库性能问题故障排除
- 检查硬件原因
- 使用 PerfMon 跟踪数据库瓶颈
- 评估查询性能
解决数据库系统的性能问题可能是一项艰巨的任务。了解如何找到问题很重要,但是了解系统对特定请求作出特定反应的原因更加重要。影响数据库服务器上的 CPU 利用率 的因素有很多:SQL 语句的编译和重新编译、缺少索引、多线程操作、磁盘瓶颈、内存瓶颈、日常维护以及抽取、转换和装载 (ETL) 活动和其他因素。利用 CPU 本身并不是一件坏事情,执行任务是 CPU 的职责所在。CPU 利用率正常的关键是确保 CPU 处理您需要它处理的任务,而不是将循环浪费在不良优化的代码或缓慢的硬件上。
达到同一目的的两种途径 概括来讲,有两种途径可以确定 CPU 的性能问题。第一种途径是检查系统的硬件性能,这种做法有助于继续确定使用第二种途径 — 检查服务器的查询效率时从何处入手。第二种途径在确定 SQL Server™ 性能问题时通常更有效。然而,除非您确切知道查询性能问题的具体所在,否则,应该始终从系统性能评估开始着手。最后,通常是这两种途径齐头并进。让我们先了解一些基础知识,以便我们研究这两种途径。
了解基础知识 超线程 超线程影响 SQL Server 的方式使超线程成为一个非常值得讨论的主题。超线程实际上代表每个物理处理器的两个操作系统逻辑处理器。超线程实质上利用物理处理器上的闲置时间,以便每个处理器得到更充分的利用。Intel 网站 ( intel/technology/platform-technology/hyper-threading/index.htm) 对超线程的工作原理进行了更为全面的介绍。 对于 SQL Server 系统,DBMS 实际上处理自己的极其有效的操作系统队列和线程,因此,超线程仅在 CPU 利用率已经很高、系统上的物理 CPU 超载的情况下使用。当 SQL Server 在多个计划程序上对执行任务请求进行排队时,实际上操作系统必须在物理处理器上来回切换线程的上下文,以满足不断发出的请求,即使同一物理处理器上存在两个逻辑处理器也不例外。如果每个物理处理器的 Context Switches/sec 高于 5000,我们强烈建议您考虑关闭系统上的超线程并重新测试性能。 在 SQL Server 上出现高 CPU 利用率时应用程序极少能有效地使用超线程。在生产系统上实施更改之前,必须在超线程启动和关闭这两种情况下针对 SQL Server 测试应用程序。 高端双核处理器肯定会优于计算机中的 RAM,而后者又比附加的存储设备快。一个好的 CPU 可以处理的吞吐量大约是当前顶尖 DDR2 内存的六倍,大约是顶尖 DDR3 内存的两倍。典型内存吞吐量是最快的光纤信道驱动器的 10 倍以上。这样,硬盘只能执行次数有限的 IOPS(每秒的输入/输出操作),该值完全受驱动器每秒可以执行的寻道次数限制。公平地讲,仅使用一个存储驱动器处理企业数据库系统的所有存储需求的情况并不常见。目前,大多数组织在企业数据库服务器或更大的 RAID 组(消除或最大程度地减小磁盘 I/O 处理器问题)上利用存储区域网络 (SAN)。最重要的是,无论您的组织规模如何,磁盘瓶颈和内存瓶颈都会影响处理器的性能。 由于 I/O 速度不同,从磁盘中检索数据的开销会远远大于从内存中检索数据的开销。SQL Server 中的一个数据页为 8KB。SQL Server 中的一个扩展由八个 8KB 页组成,即该扩展的大小等于 64KB。了解这一点非常重要,因为当 SQL Server 从磁盘请求特定数据页时,它不仅仅检索该数据页,还会检索该数据页驻留的整个扩展。实际上,存在各种使 SQL Server 更经济高效的因素,但是我在此不再详细阐述。在性能最佳的状态下,从缓冲池中提取已经缓存的数据页花费的时间应该不到半毫秒;在最佳环境下,从磁盘检索单个扩展会花费 2 至 4 毫秒的时间。通常,我认为从性能良好、状态正常的磁盘子系统读取数据应花费 4 至 10 毫秒。从内存检索数据页通常比从磁盘提取数据快 4 至 20 倍。 当 SQL Server 请求某个数据页时,它会在从磁盘子系统上查找此数据页之前检查内存中的缓冲区高速缓存。如果该数据页位于缓冲池中,则处理器将检索数据,然后执行请求的任务。这称为软页面错误。软页面错误是 SQL Server 的理想选择,因为作为请求的一部分检索的数据必须位于缓冲区高速缓存中才能使用。在缓冲区高速缓存中找不到的数据页必须从服务器的磁盘子系统中检索。当操作系统必须从磁盘检索数据页时,这称为硬页面错误。 使内存性能、磁盘性能和 CPU 性能相互关联时,一个通用标准可以帮助我们看清所有问题:吞吐量。从不太专业的角度而言,吞吐量是对可以填满有限管道的数据量的测量结果。
途径 1:系统性能 实际上,用于确定服务器是否遇到 CPU 瓶颈的方法只有几种,而且导致高 CPU 利用率的潜在因素并不多。其中的部分问题可以使用 PerfMon 或类似的系统监视工具进行跟踪,而其他问题可以使用 SQL Profiler 或类似的工具进行跟踪。另一种方法是通过查询分析器或 SQL Server Management Studio (SSMS) 使用 SQL 命令。 评估系统性能时我使用的基本原理是“先广泛开展调查,然后集中深入研究”。显然,只有确定问题范围之后,才能集中进行深入研究。使用像 PerfMon 这样的工具评估整体 CPU 利用率之后,您可以使用该工具查看两个非常简单、易于理解的性能计数器。 最熟悉的性能计数器之一是 % Processor Time;在 PerfMon 中,打开“添加计数器”窗口时就会突出显示该计数器。% Processor Time 是处理器忙于执行任务的时间量。当大多数高峰操作时间此值为 80% 或更高时,通常认为处理器的利用率很高。即使服务器的利用率不到 80%,通常您也会看到峰值高达 100%,您应该会预见到这种情况。 您应该查看的另一个计数器是 Processor Queue Length,在 PerfMon 中的“系统性能”对象下可以找到该计数器。Processor Queue Length 显示正在等待在 CPU 上执行任务的线程数。SQL Server 通过数据库引擎中的计划程序管理其任务,在计划程序中对请求进行排队和处理。由于 SQL Server 管理自己的任务,对于每个逻辑处理器它将只利用单个 CPU 线程。这意味着在专用于 SQL Server 的系统上,正在处理器队列中等待执行任务的线程数应该最少。通常,在专用的 SQL Server 上,线程数不应超过物理处理器数的五倍,但是我认为超过两倍就会出现问题。在 DBMS 与其他应用程序共享系统的服务器上,除了此计数器外,还需要查看 % Processor Time 和 Context Switches/sec 性能计数器(稍后我将简要介绍一下上下文切换),从而确定是否需要将其他应用程序或 DBMS 移至其他服务器。 了解处理器队列和高 CPU 利用率后,下面我们查看 SQL Server:SQL Statistic 性能对象下的 Compilations/sec 和 Re-Compilations/sec 计数器(请参阅 图 1)。编译和重新编译查询计划增加了系统的 CPU 利用率。您应该可以看到 Re-Compilations 的值接近于零,但是观察系统内的趋势可以确定服务器的通常行为以及正常的编译次数。不可能始终避免重新编译,但是可优化查询和存储过程以在最大程度上减少重新编译,并重新使用查询计划。通过 Batch Requests/sec(也可以在 SQL Server:SQL Statistic 性能对象中找到)将这些值与进入系统的实际 SQL 语句进行比较。如果每秒的编译和重新编译在进入系统的批请求中占很高比例,则说明应该检查这一方面。在某些情况下,SQL 开发人员也许并不了解他们的代码对这些类型的系统资源问题有什么影响以及为什么会有影响。在下文中,我将提供一些参考资料,帮助您在最大程度上减少这种行为。 图 1 选择用于监视的计数器 (单击该图像获得较大视图) 在 PerfMon 中,请检查名为 Context Switches/sec 的性能计数器(参阅 图 2)。此计数器显示为了为其他等待线程执行任务必须从操作系统计划程序(而非 SQL 计划程序)中取出线程的次数。对于与其他应用程序(如 IIS)或其他供应商应用程序服务器组件共享的数据库系统,上下文切换可能更加频繁。我使用的 Context Switches/sec 阈值大约是服务器中处理器数量的 5000 倍。在启用了超线程并且对 CPU 的利用率达到中高程度的系统上,该阈值还可更高。如果 CPU 利用率和上下文切换经常同时超出各自的阈值,这就表明出现了 CPU 瓶颈。如果经常发生这种情况,并且您的系统已过时,则应该开始计划购买更多或处理速度更快的 CPU。有关详细信息,请参阅侧栏上的“超线程”。 Figure 2 需要注意的性能计数器
性能计数器 | 计数器对象 | 阈值 | 注释 |
% Processor Time | 处理器 | > 80% | 潜在因素包括内存不足、低查询计划重用率和未经优化的查询。 |
Context Switches/sec | 系统 | > 5000 x 处理器数 | 潜在因素包括服务器上的其他应用程序、在同一服务器上运行了多个 SQL Server 实例和超线程已打开。 |
Processor Queue Length | 系统 | > 5 x 处理器数 | 潜在因素包括服务器上的其他应用程序、频繁的编译或重新编译以及在同一服务器上运行了多个 SQL Server 实例。 |
Compilations/sec | SQLServer:SQL Statistics | 趋势 | 与 Batch Requests/sec 进行比较。 |
Re-Compilations/sec | SQLServer:SQL Statistics | 趋势 | 与 Batch Requests/sec 进行比较。 |
Batch Request/sec | SQLServer:SQL Statistics | 趋势 | 与每秒编译和重新编译数进行比较。 |
Page Life Expectancy | SQLServer:Buffer Manager | < 300 | 内存不足的潜在因素。 |
Lazy Writes/sec | SQLServer:Buffer Manager | 趋势 | 大量数据缓存刷新或内存不足的潜在因素。 |
Checkpoints/sec | SQLServer:Buffer Manager | 趋势 | 根据 PLE 和 Lazy Writes/sec 评估检查点。 |
Cache Hit Ratio:SQL Plans | SQLServer:Plan Cache | < 70% | 表示计划重用率低。 |
Buffer Cache Hit Ratio | SQLServer:Buffer Manager | < 97% | 内存不足的潜在因素。 |
途径 2:查询性能 SP 跟踪 跟踪 SQL Server 应用程序时,应该先熟悉用于跟踪的存储过程。使用 GUI 界面 (SQL Server Profiler) 进行跟踪可将系统负载增加 15% 到 25%。如果可以在跟踪过程中利用存储过程,则对系统产生的负载可减少大约一半。 当我知道系统在某个地方发生瓶颈,并且想确定哪些 SQL 语句导致服务器出现问题时,我运行以下查询。这个查询帮助我了解各个语句及其当前正在使用的资源,以及需要对其进行检查以改进性能的语句。有关 SQL 跟踪的详细信息,请参阅 msdn2.microsoft/ms191006.aspx。 复制代码
SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writes FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handle ORDER BY 3 DESC向 SQL Server 提交新查询后,会对查询计划进行评估、优化、编译,并将其放置到过程缓存中。每次向服务器提交查询,都会检查过程缓存以尝试使查询计划与请求匹配。如果找不到,则 SQL Server 将为其创建新计划,此操作可能会造成很大的开销。 T-SQL CPU 优化应注意以下几个方面:
- 查询计划重用
- 减少编译和重新编译
- 排序操作
- 不适当联接
- 缺少索引
- 表/索引扫描
- SELECT 和 WHERE 子句中的函数使用情况
- 多线程操作
USE SHIPPING_DIST01; SELECT Container_ID ,Carton_ID ,Product_ID ,ProductCount ,ModifiedDate FROM Container.Carton WHERE Carton_ID = 982350144;查询计划重用对于实现存在大量事务的系统的最佳性能至关重要,并且大多数情况下是通过参数化查询或存储过程实现的。以下是有关查询计划重用信息的一些有用资源:
- SQL Server 2005 中的批处理编译、重新编译和计划缓存问题 (microsoft/technet/prodtechnol/sql/2005/recomp.mspx)
- 优化 SQL Server 存储过程以避免重新编译 (sql-server-performance/rd_optimizing_sp_recompiles.asp)
- SQL Server 2000 中的查询重新编译 (msdn2.microsoft/aa902682.aspx)
SELECT es.session_id ,es.program_name ,es.login_name ,es.nt_user_name ,es.login_time ,es.host_name ,es.cpu_time ,es.total_scheduled_time ,es.total_elapsed_time ,es.memory_usage ,es.logical_reads ,es.reads ,es.writes ,st.text FROM sys.dm_exec_sessions es LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st WHERE es.session_id > 50 -- < 50 system sessions ORDER BY es.cpu_time DESC我发现此语句有助于确定需要重点了解哪些应用程序。当将一个应用程序的各个会话的 CPU、内存、读取、写入和逻辑读取进行比较,确定 CPU 资源要远远高于正在使用的其他资源时,我开始重点关注这些 SQL 语句。 为了完整跟踪应用程序的 SQL 语句,我使用 SQL Server 跟踪。可以通过 SQL Server Profiler 工具或跟踪系统存储过程实现上述目的,以帮助评估正在进行的操作。(有关本主题的详细信息,请参阅侧栏“SP 跟踪”。)对于具有高 CPU 使用率的语句,以及哈希和分类警告、缓存未命中以及其他红色标志,应查看探查器。这有助于您把范围缩小到特定的 SQL 语句或产生高资源使用率的特定时间段。探查器能够跟踪 SQL 语句文本、执行计划、CPU 使用率、内存使用率、逻辑读取、写入、查询计划缓存、重新编译、对来自缓存的查询计划的拒绝、缓存未命中、表和索引扫描、缺少的统计信息以及许多其他事件。 通过 sp_trace 存储过程或 SQL Server Profiler 收集数据后,我通常会使用一个数据库,该数据库有两种填充方式:事后使用跟踪数据填充;将跟踪设置为写入数据库。事后填充数据库可以通过名为 fn_trace_getinfo 的 SQL Server 系统函数来完成。此方法的优点在于我可以通过多种方式查询和分类数据来查看哪些 SQL 语句使用的 CPU 最多或读取的次数最多、计算发生重新编译的次数等。以下示例说明如何使用此函数加载具有探查器跟踪文件的表。Default 指定将按照创建顺序加载此跟踪的所有跟踪文件: 复制代码
SELECT * INTO trc_20070401 FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default); GO
总结 您可以看到,高的 CPU 使用率并不一定表示出现了 CPU 瓶颈。高 CPU 使用率也可能是大量其他应用程序或硬件出现瓶颈造成的。尽管其他计数器看起来运行状况良好,但确定 CPU 使用率过高后,您可以在系统内查找原因,然后找出解决方案(购买更多的 CPU 或优化 SQL 代码)。不管怎样做,都不要放弃!使用本文中提供的提示,再进行一点实践和研究,在 SQL Server 下优化 CPU 使用率这一执行计划是完全可以实现的
版权声明:本文标题:SQL优化笔记—CPU优化 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/xitong/1729693347a1210317.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论