SQLServer2005性能调优

    技术2022-05-13  16

    名词解释

     

    DMVs:dynamic management views

     

    三个点

     

    · 资源瓶颈: CPU、内存、I/O(这里面不考虑网络问题)

     

    · Tempdb瓶颈:

    · User query瓶颈,可能是统计信息的变化、不恰当的索引、阻塞或者死锁等

    上述三点,可能是相互影响的。

     

    资源瓶颈

     

    工具

     

     

    1. System Monitor (PerfMon):windows自带

     

     

    2. SQL Server Profiler: 2005继续有

     

     

    3. DBCC commands: 参考联机文档

     

     

    4. DMVs: 见上名次解释

     

    CPU瓶颈

    CPU瓶颈,是突然并且不可预料的。一般来讲,没有优化的查询计划、系统低配置、设计不合理等,很容易导致这些问题。

    在perfmon中,我们一般需要监视Processor:% Processor Time,如果每个CPU持续高于80%,CPU就是瓶颈了。当然,在强大的2005下我们也可监视sys.dm_os_schedulers ,如果有内容,表明有任务等待CPU来分配给它。如下面这个DMVs的查询:

    select scheduler_id,current_tasks_count,runnable_tasks_count from sys.dm_os_schedulers where scheduler_id < 255

    下面的查询,更高级点。分析方法是,看结果的number_of_statements,如果该值大于1,说明可能有问题,要进一步分析。

    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, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc

    执行计划的编译与重新编译

    在sql2005中的一个改进,就是对于某个sp,进行recompile的时候,只需要针对改变的部分进行编译,sql2000只能是全部都搞一遍。

    Recompile的原因很多,如:

    · Schema的变更 changed

    · Statistics变更

    · 延迟编译

    · SET option的执行

    · 临时表的变化

    · Sp使用了RECOMPILE提示或者使用了OPTION (RECOMPILE)

    诊断方法,老朋友了,继续使用perfmon或者sql profiler。

    对于perfmon,监视下面的 计数器

    · SQL Server: SQL Statistics: Batch Requests/sec

    · SQL Server: SQL Statistics: SQL Compilations/sec

    · SQL Server: SQL Statistics: SQL Recompilations/sec

    对于profiler抓到的trace,分析这几个event:SP:Recompile / SQL:StmtRecompile / Showplan XML For Query Compile。如果我们抓到了trace,对于文件,可以这么做:

    select spid,StartTime,Textdata,EventSubclass,ObjectID,SQLHandle from fn_trace_gettable ( 'e:/recompiletrace.trc' , 1) where EventClass in(37,75,166)

    这里面,EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile

    如果你事先保存到了 table jq中,那么把上面的from修改为from jq即可。

     

    或者使用这个DMVs: sys.dm_exec_query_optimizer_info(注意一个技巧!多执行几次,看中间的差异)

    select * from sys.dm_exec_query_optimizer_info

    另外一个DMVs是:sys.dm_exec_query_stats,如执行这个sql:

    select * from sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

    另外, plan_generation_num标示出了被recompile的所有query。如下面这个

    select top 25 sql_text.text,sql_handle,plan_generation_num, execution_count,dbid,objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc

    解决办法

    · 如果因为使用了Set option,那么通过profiler来观察是什么引起的变化。

    · 如果因为临时表,那么尽量使用表变量,而不是临时表。(对于表变量的限制,请查看联机文档)另一个解决办法,使用KEEP PLAN查询提示,这会把临时表当作普通表一样对待,会进行statistics的跟踪

    · 关闭对于索引或者索引视图上的状态自动更新(偶个人不建议)

    · 对象名称写全了,如dbo.Table1

    · 尽量少用延迟编译。如你的SP或者query里面,有N多的if/else之类的。

    · 运行索引调优向导(sql2000里面就有)

    · 看看sp是不是使用了WITH RECOMPILE来建立的,或者RECOMPILE查询提示。

    弱智的查询计划

    每个查询执行之前,sqlserver都会“试图”优化一个最快的查询计划出来。注意的是,这里的最快的,不代表I/O最小,也不代表CPU占用最小。它是一个权衡后的值。

    对于Hash join或者sort等,它们都是与CPU密切相关的。对于nested loop,很可能会因为大量的index lookups,导致I/O迅速上涨。如果search的数据散落在各个pages里面,很可能会导致缓冲命中率下降。

    诊断方法

    使用这个DMVs: sys.dm_exec_query_stats,它可以有效地监视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

    解决办法

    · 使用优化向导调优

    · 检查一下,WHERE条件是不是限制的足够好?

    游标问题

    强烈建议,尽量减少使用游标。可以使用perfmon监视SQL Server:Cursor Manager By Type – Cursor Requests/Sec。或者使用DMVs:

    select cur.* from sys.dm_exec_connections con cross apply sys.dm_exec_cursors(con.session_id) as cur where cur.fetch_buffer_size = 1 and cur.properties LIKE 'API%'

    如果使用profiler,可以监视sp_cursorfetch(前提是包含了RPC:Completed这个event class)

    内存瓶颈

    对于VAS和AWE概念,请自行查找MSDN。我印象中,M$的人强烈建议不要在32bit windows上面使用AWE或者3BG之类的东西。

    检测内存问题

    打开taskmgr看物理内存中的Avaiable,如果持续低于10M,恭喜你,系统内存压力太大!通过perfmon,监视Memory: Available Mbytes,一样的效果。

    对于AWE使用,可以用这个DMVs来看:

    select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks

    对于虚拟内存等,可以观察Commit Charge中的Total,与limit的对比。如果两者很接近,虚拟内存可能不够了。如果你好奇,想看看sqlserver自己的内存分配,可以执行DBCC MEMORYSTATUS。具体内容,见联机文档。

    对于内存问题,偶认为采用sql的默认设置,一般情况下足够了。

    I/O瓶颈

    除非操作系统能够,并且内存足够大,把你的db放到物理内存里,否则,I/O我们永远回避不过去。使用perfmon的话,可以监视

    · PhysicalDisk Object: Avg. Disk Queue Length,如果经常性的大于2*磁盘个数,磁盘有性能问题。

    · Avg. Disk Sec/Read,如果<10ms,很好。20以下,一般。50以下,密切观察。50以上,换硬盘吧!

    · Avg. Disk Sec/Write,这个和上面的两个值,如果持续大于物理磁盘的指标的85%,说明磁盘已经到极限了。

    · Physical Disk: %Disk Time,一般如果超过50%,I/O有瓶颈。

    如果用了raid,采用下面这个公式来计算:

    Raid 0 -- I/Os per disk = (reads + writes) / number of disks

     

    Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2

     

    Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks

     

    Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

    如下面这个例子,2个磁盘,raid1,监测到的结果:

    Disk Reads/sec             80

     

    Disk Writes/sec            70

     

    Avg. Disk Queue Length     5

    那么I/O平均是80/2+70=110,队列长度上限是2*2=4

    解决办法

    · 检查sqlserver的内存配置

    · 增加或者替换更快的硬盘,读写缓存越高越好

    · 检查执行计划,找到I/O大的地方。如这个DMVs

    select top 50 (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, sql_handle, s2.text plan_handle from sys.dm_exec_query_stats cross APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by (total_logical_reads + total_logical_writes) Desc

    小TIP,如果要清除缓存的作用,执行这个:

    checkpoint

    dbcc freeproccache

    dbcc dropcleanbuffers


    最新回复(0)