【SQL开源代码栏目提醒】:网学会员鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“SQL Server 2008 性能调优 - 计算机教材”一文,供大家参考学习
SQL SERVER 2008 议题
SQL Server 2005/2008 的架构
Sql监控工具哪些因素影响性能
SQL 2008对系统资源的使用和监控方法共享的资源的优化CPU、内存、IO、tempdb编程时的注意事项
SQL Server 2005/2008 的架构哪些因素影响性能等待系统资源–内存CPU磁盘IO错误的配置–硬件 软件不优化的查询 设计–写法不好 设计不周索引问题–和工作量与资源配置没有关系不好的执行计划和客户端网络交互太多
Sql监控工具性能监视器
Sql profilerSql 跟踪默认跟踪
Sql server management studio动态管理视图DMVTransact-SQLSQL 2008对系统资源的使用和监控方法
SQL Trace–
SQL Profiler对性能的影响可能比较大–Server Side TracePerformance MonitorDMV DMF 动态管理视图和函数数据库定义以及数据统计信息使用SQLDiag在
SQL 2000里面的PSSDiag缺省在
SQL 2005/2008里面就有安装可以自动搜集–
SQL Trace–Performance Log–SQLDiagreport–Windows Event Logs附带两个模板–SD_Detailed.XML–SD_General.XMLLab 1使用SQLDiag监视
SQL 2008的运行SQLDIAG /O cdSQLDiagOutput /P /E 00:15:00共享的资源数据库级共享的资源–数据库性能受到日志文件最大写入能力的限制日志的写入必须是串行的–可以通过以下改进性能??增加更多的物理硬盘??增加数据库的数目以增加日志的数目服务器级共享的资源–TEMPDB–Memory 64-bit–Memory 32-bit??32-bit AWE扩展的内存只能cache数据页面??Proc cache locks user connections sorting 还是只能使用2-3GB 的地址空间–可以通过在一台机器上安装多个instance解决机器级共享的资源–CPU 和网络向上扩展scalability规则数据库的scalability 受到日志文件最大写入能力的限制–Disk I/O实例Instance的scalability 受到进程最大资源数目的限制–Memory服务器的scalability受到机器能力的限制–CPU –网络带宽磁盘RAIDRAID10RAID5硬盘读写瓶颈I/O 瓶颈通常比较容易发现对日志文件一定要小心–使用独立的设备–使用RAID 10RAID5写性能问题: –Each RAID5 write 2 READS 2 WRITES –最近的测试结果是RAID5的写性能比RAID 01要差50评测磁盘IOIometer是一个免费的
开源的测试磁盘性能的工具–www.iometer.org下载iometer数据库事务日志不超过5ms 最好是0msOLTP数据不超过10ms决策支持系统OLAP和报表数据不超过25ms检查系统是否有IO瓶颈描述值源问题描述Avg Disk seconds / read 10 ms Perfmon objectPhysical Disk Reads should take 4-8ms with NO IO pressureAvg Disk seconds / write 10 ms Perfmon objectPhysical DiskWrites sequential can be as fast as 1ms for transaction log.Big IOsrange scanstable scans1 PerfmonobjectSQL Server Access MethodsMissing index flushes cache If Top 2 values for Wait stats includes:ASYNCH_IO_COMPLETIONIO_COMPLETIONLOGMGRWRITELOGPAGEIOLATCH_EXTop 2Sys.dm_os_wait_statsIf top 2 wait_statsvalues include IO there is an IO bottleneck select from Sys.dm_os_wait_statsorder by wait_time_msdesc32位系统内存配置物理地址扩展PAE3GB不支持物理内存超过16GB地址窗口扩展AWE系统内存小于4GB大于等于4GB大于16GB/PAE否是是/3GB否是否AWE否是是内存-系统性能监视器项目名称策略SQLServer:Buffer ManagerBuffer cache hit ratioSQL server 缓存命中率缓存命中率应用大于90较低说明系统内存或者数据缓存分配低于所需的大小/3GBAWESQLServer:Buffer ManagerPage life expectancy缓冲面在内存的保留的时间应为300秒以上低于该值表明有内存压力增加物理内存MemoryPages/sec每秒页面错误是操作系统通过磁盘解决内存引用时出现的现象应该尽可能接近于0值越大表示发生的硬页面转换越多表明系统没有足够的系统内存。
增加系统内存阻塞问题阻塞是由于并发的连接争抢共同的资源但是没有形成死锁检测工具–
SQL 2005/2008 Profiler就可以检测出–DMVs–Sp_who2 sp_lockSnapshot Isolation -Row Versioning读不阻塞写检测阻塞DMVDMF sys.dm_db_index_operational_stats 可以看出资源争抢的对象–Row locks counts 行锁申请数目–Row lock waits counts 行锁等待次数–Total wait time for blocks 总共被阻塞的时间–可以算出发生阻塞的百分比和平均等待时间row_lock_wait_count/ row_lock_countrow_lock_wait_in_ms/ row_lock_wait_countselect dbid from master..sysdatabasesselect from sys.dm_db_index_operational_statsdbidNULLNULLNULL检测阻塞SP_locksp_who2Sp_lock sp_who2 –lists real time blocksTrace –for historical analysis–Capture long blocks using the Trace Event “Block Process Report” ??Sp_configure “blocked process threshold”15 seconds检测阻塞
SQL 2005/2008 ProfilerLab 2确认
SQL Server内部的阻塞问题CPU使用连接在Runnable状态完全是在等待CPU时间片执行CPU使用–执行计划的compile和recompile–SortJoinAggregation通常情况下
SQL的CPU使用量不会太大。
相对于CPU memory和disk更容易成为系统的瓶颈执行计划与它的生成执行计划重用Master..Sys.dm_exec_cached_plans–包含存储过程和语句–引用该缓存对象的其他缓存对象数–自开始以来使用该缓存对象的次数可以重用的执行计划–Procs Triggers Views–Defaults Check constraints rules–adhoc
SQL sp_executesql检查计划重用情况
SQL Batch requests/sec–和
SQL Compilations/sec作对比
SQL Compilations/sec–包含初始的compiles AND re-compiles–去掉re-compilations能大致算出初始compiles的数目–在Sys.dm_exec_cached_plans里面找出usecounts最低的
SQL语句
SQL Re-compilations/sec–语句一级的Recompiles–Check profiler for sp:recompileevent to identify
SQL statement. 监视执行计划的compile和recompilePerfmon: SQLServer:
SQL Statistics–Batch requests / sec 1000’s/sec server is busy–
SQL Compilations / sec 10s/sec could be problem–
SQL Recompilations / sec OLTP should avoid high recompsRatio of compiles / requests is important–Compiles –recompiles initial compiles –Plan re-use Batch requests –initial compiles / Batch requestsRecompile 的原因:–表格的定义发生变化–先前的并发计划需要串行执行–统计值更新过了–表格更新的行数超过了限度–sys.sysindexes.rowmodctr使用
SQL Trace观察执行计划重用量主要的event有:–SP:CacheMiss event ID 34 in Profiler–SP:CacheInsert event ID 35 in Profiler–SP:CacheRemove event ID 36 in Profiler–SP:Recompile event ID 37 in Profiler–SP:CacheHit event ID 38 in ProfilerSP:Starting 标志一个stored procedure开始执行SP:StmtStarting 标志单个语句开始执行–Example: sequence is ??SP:StmtStarting??SP:CacheMiss no plan found??SP:CacheInsert plan created–注意使用
SQL Profiler可能会影响
SQL性能加入Eventsubclass 字段可以显示recompile的原因Lab 3观察执行计划重用情况Tempdb资源争抢Tempdb在
SQL 2005/2008里使用量更大–Tempdb的配置更加重要使用方式启用了触发器的大容量加载操作公用表表达式查询游标数据库邮件DBCC CHECKDB事件通知索引大型对象LOB 数据类型变量和参数多个活动的结果集MARS查询通知查询行版本控制Service Broker 存储过程临时表和table 变量??用户定义的表和索引??系统表和索引??全局临时表和索引??局部临时表和索引??table 变量??表值函数中返回的表触发器用户定义函数XMLTempdb使用规划设置tempdb的自动增长。
执行单独的查询或工作负荷跟踪文件监视tempdb空间使用。
执行索引维护操作例如重新生成索引监视tempdb空间。
使用前面步骤中的空间使用值来预测总的工作负荷使用情况针对计划的并发活动调整此值然后相应地设置tempdb的大小。
编程时的注意事项1、避免将字段设为“允许为空”2、数据表设计要规范3、深入分析数据操作所要对数据库进行的操作4、尽量不要使用临时表5、多多使用事务6、尽量不要使用游标7、避免死锁8、要注意读写锁的使用9、不要打开大的数据集10、不要使用服务器端游标11、在程序编码时使用大数据量的数据库12、不要给“性别”列创建索引13、注意超时问题14、不要使用Select 15、在细节表中插入纪录时不要在主表执行Select MAXID 16、尽量不要使用TEXT数据类型17、使用参数查询18、不要使用Insert导入大批的数据19、学会分析查询20、使用参照完整性21、用INNER JOIN 和LEFT JOIN代替Where性能调优的方法学数据架构索引表查询
代码存储过程视图存储设计文件组分区硬件配置内存处理器亲和度调优顺序最困难但最有成效最简单但是收效最少常见的性能问题磁盘负载过重内存负载过重处理器负载过重网络负载过重架构不良
代码不良
代码不良客户端
代码不良调整架构调整
代码调整存储调整客户端设计常规情况下GTSC UNIVERSTIYThank You
上一篇:
扩展 iBatis 以透明支持多种数据库
下一篇:
基于量子反馈的量子态调控方案研究