开发者社区> fanr_zh> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

2年SQL Server DBA调优方面总结

简介: 2年SQL Server DBA调优方面总结 当2年dba 我觉得,有些东西需要和大家分享探讨,先书单。 书单 1.《深入解析SQL Server 2008 系列》 这个就是mssql 2005 的技术内幕系列。
+关注继续查看

2SQL Server DBA调优方面总结

当2年dba 我觉得,有些东西需要和大家分享探讨,先书单。

书单

1.《深入解析SQL Server 2008 系列》 这个就是mssql 2005 的技术内幕系列。2012版的也出了有兴趣可以看看,技术内幕系列是我接触最早的书,里面内容涵盖量很大,但是都是点到为止。所以很多都是可以细细品味,回头再看的。

2.《Troubleshooting SQL Server A Guide for the Accidental DBA》 这本书是我接触最早的关于性能调优的书。链接已经给出可以去下载,不过需要注册SQLServerCenter ,这个网站是SQL Server 方面比较出名的网站。很多国外大牛。

3.《联机文档》也就是sql server 装机后自带的帮助文档,内容全面的吓人,几乎包含了技术内幕系列的所有内容。

4.《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》这本书是将sql server 2000的内核,从软件开发的角度来看SQL Server 2000,很深入作者也十分的出名,可惜死的太早。对sql server框架理解主要来源于这本书,可惜没有中文版。

5.《SQL Server 2008 内核剖析和故障排除》接触的第二本关于性能调优的书,真本书比较绝的地方时,先将原理再讲调优。全书分为2部分第一部分就是原理,第二部分是性能调优。也是不错的一本,书中对扩展事件的功能做了比较详细的解释。我在其他书上是没看到过的。

该书的2012英文原版已经出了。

6.《Microsoft SQL Server企业级平台管理实践》是一本少见的国产好书,书的编写很符合中国人心理,直指问题本身,很适合当工具书。其中有关于性能跟踪调整,从捕获到处理讲的很实际。

7.《SQLSERVER求生秘籍》和《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》是同一个作者,这本书主要是针对SQL Server 2005和上一本一样对个别点讲的很深入,缺点讲到的东西太少。

8.《SQL Server 2008查询性能调优》这本书比较实用的一本书,讲了各个瓶颈的发现,性能基线的简历,从查询,存储过程角度出发,分析性能,讲解可能出现性能问题的点。

9.《Pro SQL Server 2008 Service Broker》 讲解关于Service Broker,异步消息处理程序,很多比较大的公司会使用,我知道的是新蛋是使用这个的,全书围绕一个大例子比较清晰,容易接受。

10.《Pro SQL Server 2008 Policy-Based Management》关于策略管理方面的知识,个人觉得比较鸡肋。

 

安全性

楼主是小公司的DBA所以关于安全性使用的比较少,就管理一些权限和密码

可用性

到SQL Server 2012实现了多种可用性方案,1.日志传送,2.数据库复制,3.数据库镜像,4.alwaysonline。

1.日志传送,楼主觉得是数据库镜像的雏形。没有数据库镜像那样试试的传送和redo日志

2.数据库复制,数据库复制有比较多的分类:快照,事务,合并。事务复制是被应用最广的,从sql server 2000到sql server 2005事务复制被改进了很对具体可以看联机文档。

3.数据库镜像,我对于不需要读写分离的数据库中,数据库镜像是被应用最广的可用性方案,数据库镜像和其他的比最突出的优点是切换方便。

高性能

DBA的大头应该是性能调优。性能的调优大头是索引,最求更高的性能索引是必不可少的。一个性能主要体现的执行时间上,执行时间= 运行时间+等待时间。这个公式我觉得很经典。当你没有头绪的时候能帮你梳理清楚应该怎么排查问题。做性能调优一定要对性能的指标十分熟悉。

 

性能基线

当你刚刚入职一家公司,对公司数据库现在的负载一无所知,那么一开始要做的事情就是创建一个数据库性能基线。有人会问基线能用来干什么,很多人感觉没用,我刚入职时我也觉得没用。但是性能基线是一个性能调优,监控的开始。

 

一般比较正规的公司,一个业务上线前会通过压力测试预计这个服务器的性能边境在哪里,到达性能边境之后各个性能指标的表现是如何的。如果如果性能基线接近了性能边界,到了这个时候,那么就要考虑换服务器或者加服务器了。这个是性能基线的一个用处。

 

拿到一个服务器我先会做一下性能基线,性能基线也就是服务器在正常运转的时候数据库的性能指标的表现。我会抓取24小时的性能指标作为性能基线(可以看我相关的文章:SQL Server 性能基线和监控SQL Server 性能调优(性能基线))。

 

以下是我使用的抓取的指标

cpu:

    \Processor(_Total)\% Processor Time
    \Processor(_Total)\% Privileged Time

    \SQLServer:SQL Statistics\Batch Requests/sec
    \SQLServer:SQL Statistics\SQL Compilations/sec
    \SQLServer:SQL Statistics\SQL Re-Compilations/sec
    \System\Processor Queue Length
    \System\Context Switches/sec

  Memory:

    \Memory\Available Bytes
    \Memory\Pages/sec
    \Memory\Page Faults/sec
    \Memory\Pages Input/sec
    \Memory\Pages Output/sec
    \Process(sqlservr)\Private Bytes
    \SQLServer:Buffer Manager\Buffer cache hit ratio
    \SQLServer:Buffer Manager\Page life expectancy
    \SQLServer:Buffer Manager\Lazy writes/sec
    \SQLServer:Memory Manager\Memory Grants Pending
    \SQLServer:Memory Manager\Target Server Memory (KB)
    \SQLServer:Memory Manager\Total Server Memory (KB)

  Disk:

    \PhysicalDisk(_Total)\% Disk Time
    \PhysicalDisk(_Total)\Current Disk Queue Length
    \PhysicalDisk(_Total)\Avg. Disk Queue Length
    \PhysicalDisk(_Total)\Disk Transfers/sec
    \PhysicalDisk(_Total)\Disk Bytes/sec
    \PhysicalDisk(_Total)\Avg. Disk sec/Read
    \PhysicalDisk(_Total)\Avg. Disk sec/Write

  SQL Server:

    \SQLServer:Access Methods\FreeSpace Scans/sec
    \SQLServer:Access Methods\Full Scans/sec
    \SQLServer:Access Methods\Table Lock Escalations/sec
    \SQLServer:Access Methods\Worktables Created/sec
    \SQLServer:General Statistics\Processes blocked
    \SQLServer:General Statistics\User Connections
    \SQLServer:Latches\Total Latch Wait Time (ms)
    \SQLServer:Locks(_Total)\Lock Timeouts (timeout > 0)/sec
    \SQLServer:Locks(_Total)\Lock Wait Time (ms)
    \SQLServer:Locks(_Total)\Number of Deadlocks/sec
    \SQLServer:SQL Statistics\Batch Requests/sec
    \SQLServer:SQL Statistics\SQL Re-Compilations/sec

指标代表啥意思我就不解释了,你可以开perfmon,挨个看说明。

假设你现在已经有了性能指标了,那么你就可以根据性能基线简历告警了,以前的文章(SQL Server 性能基线和监控)中我已经提供了使用powershell如何监控性能。

 

性能运行性能问题分析:

基线建好了监控也建好了,出现告警了。按讲关于调优的书上就会开始分开,分为CPU瓶颈,IO瓶颈,还是内存瓶颈讲。关于这些瓶颈的确认我这里就没必要说了,在以前的文章SQL Server 性能调优(ioSQL Server 性能调优(cpuSQL Server 性能调优(内存)都有讲到。如何确认各个瓶颈。

 

其实这些辨认瓶颈的方法都是不够全面的,瓶颈确认需要经验,因为往往出现性能问题了,不是一个指标,而是一批指标都有问题,比如当你索引没建好,导致了全表扫描,io变大,cpu飙高,内存出现分页,所以有时候十分难判断。

 

如果已经确定是那部分照成的性能问题如IO,CPU,内存。归根结底就只有2中方法,1.调整。2.硬件升级。

 

如果问题出现了,要急着解决问题1.使用top 10 io,top 10 cpu,来查看需要优化的语句根据执行计划进行调优。还有就是通过profiler,前提是当前服务器还能允许你使用profiler。2008之后出现了扩展事件,可能可以通过这个来处理,但是关于扩展事件做跟踪我还没有涉及,相关资料也不是很多。

 

那么如何确定使用内存比较多的语句呢,内存有点特别,sql server把数据放在buffer pool里面,大家都能用,内存压力分为内部和外部,内部是sql server 自身引起的内存压力,外部是其他进程照成的内存压力(相关的只是可以查看sql server 2005 troubleshooting 白皮书)。

出现内存瓶颈也就是buffer pool满了,要清除原先的buffer pool数据才能把新读入的数据存放在里面,那么就简单了,那个语句读取的最多那么哪个语句使用内存最多(详细内容可以查看《Microsoft SQL Server企业级平台管理实践》)。

 

那么假设已经定位到了一个出问题的SQL语句,那么接下来就是要优化它,里面使用到的最关键的就是执行计划。如何根据执行计划优化SQL语句不同的人想法都不太一样。优化方法和各有特色。所以不再升入以免以偏概全。但是运行时间主要还是这样几点:执行计划,统计信息,索引。

性能等待问题分析:

等待时间:锁等待,闩锁等待。

关于资源等待,这里有三篇文章,《SQL Server 性能调优 Wait Event》,《SQL Server 性能调优 Wait Event (二)》,《SQL Server 2008 性能调优 session级别 wait event》作者是同一个人。通过WaitEvent的角度来调整。所以在此之前需要先了解关于sys.dm_os_wait_stats 中相关指标主要指的是什么意思,关于这个SQL Server出了一个《SQL Server 2005 Waits and Queues》很详细的介绍了各个指标的意思。《SQL Server 2008查询性能调优》中有个很好的关于收集堵塞情况的SQL语句。

 

当收集到堵塞如果是出现在锁级别上的,那么没有其他办法,用索引或者在select 语句上面加nolock,或者开带快照的隔离级别,但是个人比较不赞成快照隔离级别,有朋友已经测试过,一开快照隔离级别,tempdb的负载增加十分明显,一个问题解决导致了另外一个更棘手的问题。若是select语句,尽量使用覆盖索引,来减少因为引用多个索引导致和update死锁的情况。当然这个也看具体的系统运行环境而定。

 

如果是出现在闩上,一般比较大的指标是PAGEIOLATCH_x系列的,WRITELOG,PAGELATCH_x,tempdb上的PAGELATCH_x。

 

PAGEIOLATCH_x是在等待磁盘io时产生的,会产生磁盘io的原因也就是内存中没有数据,就是内存不够才会出现这种状况,那么就加内存吧。或者优化一下业务规则。

 

WRITELOG 是写入日志的时候出现了等待,日志是顺序写的,本质就是事务多写入时磁盘速度不够快出现了等待,如果有问题建议1.把日志和数据文件分开,放到2个独立的盘或者raid,2.换成速度更快的盘。

 

PAGELATCH_x是操作buffer pool数据页产生的闩。如果等待过大,很简单就是调用这个页的session过多,那么就减少对页面的访问。1.通过索引优化语句,尽量减少sql读取的页面数量。2.想办法把页面的数据分散多个页面。3.考虑读写分离。

 

tempdb上的PAGELATCH_x主要发生在GAM,SGAM,PFS几个页面,因为order by,group by,临时表,表变量,lazy操作符。都会使用到tempdb,会开辟一个空间。如果并发量大。那么tempdb上的PAGELATCH_x的等待将会很大。1.减少执行计划中sort操作符,减少lazy操作符。2.把tempdb的数据文件扩展,上限是cpu个数(有个条件是tempdb容量要平衡)。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
792 0
使用sql语句直接生成带有’小计’,’合计’的数据集
在软件开发过程中经常要做一些报表,而且大部分报表都需要用到根据某一列进行统计,计算出’小计’以及’合计’项目。一般来说大家都是在前台程序中进行处理,其实,用sql语句就可以很轻松的完成这个功能。SQL> select decode(grouping(dept.
787 0
使用Oracle的DBMS_SQL包执行动态SQL语句
引用自:http://blog.csdn.net/ggjjzhzz/archive/2005/10/17/507880.aspx 在某些场合下,存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。
1338 0
方便查看数据表和字段的SQL语句(适用于SQLServer2000)
  经过对SQLServer2000系统表的分析,写出了以下两个SQL语句。可以把这两个语句分别建为两个“视图”,方便查看用户数据表和字段的信息。1、列出所有的用户数据表:SELECT TOP 100 PERCENT o.
568 0
toad执行sql语句
链接:http://www.itpub.net/showthread.php?s=&threadid=700007&perpage=10&pagenumber=1 在toad中执行sql语句与sqlplus中存在一点细小的区别,就是在每个sql的后面包括一个空格。
979 0
寻找没有使用绑定变量的sql语句
SELECT plan_hash_value, COUNT (*) FROM v$sql WHERE plan_hash_value 0GROUP BY plan_hash_valueORDER BY 2 DESC;SELECT * FR...
588 0
收藏几段SQL Server语句和存储过程
中文转载--   收藏几段SQL  Server语句和存储过程 -- ====================================================== --列出SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息...
531 0
一条sql语句的优化
今天跟踪程序,无意中发现一条不良的SQL语句今天跟踪程序,无意中发现一条不良的SQL语句分析如下:SELECT   fee_stat_name, NVL (t1.
656 0
+关注
fanr_zh
数据库技术专家
341
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载