SQLServer DBA 三十问(第11~20题)

SQLServer DBA 三十问(第11~20题)

11. 死锁如何跟踪;阻塞如何跟踪和查找;发现有问题的语句后,如何进行处理;用Profile做跟踪时,一般我们需要跟踪哪些事件;

答:dbcc traceon(1204) 可以开打跟踪死锁的标记,sqlserver2005新增了1222跟踪标志,就是格式更详细,死锁的信息会记录在errlog文件里,顺便说下 errlog一共有errlog,errlog1~errlog6共7个文件,关于这些可以看看books online,如果觉得books online太乏味,可以看看徐海蔚的《Microsoft SQLServer企业级平台管理实践》;

相对来说,阻塞的问题比死锁要严重,死锁只是牺牲部分进程,阻塞的话会导致整个系统瘫痪,阻塞的定位我有一篇专门的博客介绍:sqlserver阻塞定位

要 跟踪死锁的话,当然是选择“锁”事件,不过我一般用profiler都是做性能调优的,选 常规——》模板名——》SQLProfileTuning 就ok了,死锁很少跟踪,因为我们的sqlserver降低了事务隔离级别,基本不会产生死锁,有的话也在可接受的范围内,都是凌晨运行作业时产生的,也 不会太多。

 

12. Windows日志主要有哪几种,SQLServer日志一般保留几个,什么情况下会产生新的SQL日志;数据库日志恢复模式有哪几种,区别是什么;数据库日志突然变得很大,而且你无法收缩,可能的原因是什么,怎么查找原因,分别将如何处理;

 答:windows 的日志可以自己看iis就明白了,我一般只看应用程序,一次发现asp.net抛出的错误和异常,解决之;sqlserver的日志一般保留7 个,errlog,errlog1~errlog6,每次数据库重启都会产生新的日志,新的日志命名为errlog,旧的日志也会跟着改名,之前老的 errlog改为errlog1,老的errlog1 改成 errlog2,直到 errlog5 改成 errlog6, 之前的errlog6会被删除,所以如果数据库出错千万不要盲目充能更新启动,可以先看看错误日志,多次重启之后可能就丢失了最原始的错误信息;

应该是数据库恢复模式吧,简单,大容量,完全;用哪个要视情况而定,不重要的业务可以用简单,重要业务就要用完整;

完整模式支持最完善的备份和还原方案,可以还原到某个时间点,简单模式只能还原到该备份里面的数据,无法还原到时间点,大容量模式一般只在需要进行批量数据导入的时候才使用; 

日 志突然很大而且无法收缩,其实有很多种可能,不过不管是哪一种可能,都离不开一个现象,有一个更新(insert,update)动作工作正在执行,而且 短时间内不会停止,既然短时间内不会停止,那么我们就应该想到master.dbo.sysprocesses这个表,可以通过 select spid from master.dbo.sysprocesses where open_tran > 0 查看当前运行时间比较长的进程,看看到底有什么动作,我们还可以通过 dbcc inputbuffer(@spid) 找到语句;

     

13. 分区表和分区视图是什么概念,一般是在什么情况下使用,有啥好处;

答:先说分区视图,这个在sql2000就已经支持,他其实还是一个视图;

分区表是sql2005新提供的功能,逻辑上他就是一个表,物理上它可以把数据保存在多个磁盘,以此提高io,提高并发量;

2005下应该都是用分区表了,数据量庞大的时候可以按分区函数把数据分开,对于查询性能有很大的提升,不过我无论如何都认为太大的表不好,2000下我一般都是分表的,比如历史表,或者按自己业务需求制定分表方案。

 

14. 如何比较两个同结构的表数据的差异;如果表损坏了,如何修复;如何在备份文件有问题的情况下尽量还原数据;如何将一个表

     的Identity属性归零;

答: 要比较两个表所有字段的值,光通过sqlserver可能实现不了,可能需要借助第3方工具,但如果数据量很大,什么工具都很难完美实现,提出这个问题, 应该是想找到部分丢失的数据,如果是这样,我一般是通过找到出问题的那段时间开始,把那之后的数据比较一下,然后通过join 把数据update成想要的;

如果表坏了,有个dbcc checktable的命令,不过我至今没遇到过;

一般备份文件有问题,但是可以还原成功,不过会报些错误,可能导致数据库不稳定,可以尝试dbcc checkdb命令,之后再新建一个库,把数据导入新库中;

identity属性的问题,delete并不会归零,因为在结构中还保存了最大值,truncate table就可以 

 

15. CheckPoint和LazyWriter区别;DDL Trigger 和 DML Trigger有啥用,区别是啥;

答:在sqlserver2000只有checkpoint,lazywriter 是 sql2005以后加入的,可以简单认为他们是sqlserver系统内部的进程;

checkpoint 需要达到一定条件才会触发,触发之后会强制把脏页写入磁盘;

lazywriter 是每隔一段时间启动一次,然后检查free list,看看是否低于某个阀值,如果低于这个阀值,他就干活,把脏页数据放入free list,同时写入磁盘;  

可 以打一个形象的比喻:lazywriter是个懒汉,他每隔一会儿就睡(定时启动),睡醒了就看看自己的钱包(free list),如果他认为钱包的钱少了,他就拿钱(脏页放入free list)干活(脏页写入磁盘),checkpoint是一个很卖力的伙计,只有有事情通知他一声,他立马一声不吭的把把活干完(脏页写入磁盘),不会在 乎自己的钱包(free list);  

 

DDL Trigger是针对结构的触发器,比如新建表,修改表,删除表;

DML Trgger是针对数据的触发器,比如insert,delete,update表数据;

 

16. Mirroring 和Logshipping 的区别和使用场景;SQLServer的Mirroring与Oracle的哪像技术比较接近,它们的区别是啥;

答:Mirroring, 即数据库镜像,一般用作高可用性的故障转移集群,需要 主服务器,镜像服务器,见证服务器3台,当见证服务器检测到主服务器出现故障时,会自动把数据库连接指向镜像服。实现的基本流程如下,用户访问主库,并提 交数据,主库会把日志传送到镜像服务器,做镜像的时候也有些麻烦,必须首先保证主库和镜像库数据一致才能建立镜像关系,所以如果你的主库时刻在产生数据,那镜像做起来估计就费神了;其实我还有个疑问,要是见证服务器挂了怎么办,ms貌似没给出方案;

LogShipping, 即日志传送,也有主库-辅助库,没有见证库了,他定时把事务日志传送到辅助库,其实就是不断做back log 和 restore 的操作,他有个缺陷,辅助库每次restore都需要断开所有连接,所以辅助库并不适合让用户访问,这个方案都是可以作为容灾备份的一个方案,当主库出现 故障的时候,马上手工切换到辅助库即可恢复。

相比之下我觉得logshipping更合适一些,做的时候没有那么多条件限制; 

对Oracle不了解,所以无法比较; 

 

17. Mirroring的搭建步骤,Mirroring三种模式区别,Mirroring 中同步和异步的原理和要求,搭建了Mirroring后,需要对数据库日志做

     什么处理;

答:这个搭建步骤还是查资料比较可靠。 

三种模式,高安全(带自动故障转移)同步传输 ,高安全(不带自动故障转移)同步传输,高性能,异步传输数据,同步就是主库要等待镜像库的回应消息才会提交事务,异步就是不等待回应消息就已经提交日志,因为等待消息需要时间,所以不等待性能会更高,但不是很安全,镜像数据库的数据有可能和主库的数据不一致。

需要对数据库日志做什么处理?没看懂这句; 

 

18. Replication配置和使用场景;Replication有哪几种模式;PUSH和PULL有啥区别;搭建Replication后会产生一个什么库;报错时

     用什么来查看报错的具体语句,清理掉某个库的Replication使用什么语句,查看同步链信息主要通过哪些表;

答: 配置的步骤足够写一篇博客,大概的步骤如下:1. 标识分发服务器;2. 在此分发服务器上创建分发数据库;3. 启用将使用此分发服务器的发布服务 器;4. 启用发布数据库;5. 启用将接收发布数据的订阅服务器;其实里面很多细节要注意,可以google得到不错的教程,然后手动操作一遍就知道 了,没什么难度。

场景:一般都是作为数据同步用;有推和拉模式,分发代理程序在分发服务器上运行即推,在订阅服务器上运行即拉;

会产生一个distribution库;

报错的话,一般监视器里面都有优红X的,如果觉得这样看太麻烦,可以直接查 distribution库的dbo.MSdistribution_history表,所有分发相关的信息都保存在了distrition库;

清 理replication看似简单,其实还分很多情况的,比如push,pull模式,还是日志,快照,合并模式,因这些不同,都需要执行不同的系统存储 过程,既然ms给我们提供的好用的企业管理器,我们大可以用他来简化我们的工作,如果一定要脚本,那就找吧,系统存储过程一般以 sp_ 打头,清楚一般包含 "del","drop","remove" 等关键字,比如我通过 select * from master..sysobjects 

 

where xtype = 'p' and name like '%repl%' and name like '%remove%',sql2000下可以查到相关的过程和用法;说实话谁也不能保证自己什么都会,不会应该保证在遇到问题的时候能马上找到解决的办 法,这比死记硬背答案要来的有效些;

在distribution.dbo.MSarticles 可以查看同步链; 

 

 

19. Replication发布端的表能truncate吗,为什么;Replication Identity列如何处理、缺失字段错误如何处理、主键冲突错误如何处

     理、如何跳过指定的错误、订阅端表被删除了如何处理、大规模改动数据如何处理;某条同步链因为其中的某个表一次性改动数据很

     大造成同步链的严重延时,要求尽快恢复同步链,如何处理。

答:不能truncate,具体见我的另外一篇:DBA之问:Replication发布端的表能truncate吗,为什么;

发布的时候默认情况下,目标表不会建立identity字段,也的确不该建立;

缺失字段这种情况不应该发生,如果发生了是技术人员的操作失败,如果此列不许发布,去掉就可以了,如果要在发布之后再对列进行添加或者删除,可以参考 sp_repladdcolumn , sp_repldropcolumn 这两个系统存储过程;

主键冲突,首先忽略,然后再检查两个表数据量是否一样,发生这种问题可能是有技术人员违规在订阅库上修改了表,这是不被允许的,订阅库的库只适合做查询用,不应该人为手动更新;

订阅端表被删除,这样监视器是会提示错误的,应该重新初始化数据;

不建议同事进行大规模的数据改动,因为这会导致同步太频繁,而没一个同步动作都需要等待发布端服务器的确认,如果生产数据库需要不断的等待确认,那等于降低了并发量,如果等待队列和时间都太长有可能导致数据库太慢而停止服务,我们遇到过类似情况。

 

20. SSB(Service Broker)使用场景,如何创建,都会创建些什么对象,有啥优缺点,主要通过什么方式实现不同服务器之间的消息

     传递;可以通过哪些方式排错;

答:这是sql2005以后新增的功能,一直用的2000,对这个还没认真研究过,只知道可以用来做分布式的数据同步,而且是异步实现的。

 

———————————————–

后 语: 这中间的10题比之前的10题难度提高了不少,有些问题在这之前我只是用他们来干活,并没具体研究他们怎么干活的,通过这10题我对sqlserver的 了解又加深了一步,而且很多问题,都是抱着追究本质的态度。在这些题目里面,有些是sqlserver2005新增的功能,因为本人实在没用过,所以不敢 妄给答案,以免误人子弟。知识无限,能力有限,文中难免存在不对的地方,本人秉承着相互交流的态度,若哪位仁兄发现不对,敬请指出,本人也很乐意和从事 sqlserver工作的兄弟们交流。

有些小补充
11 1204或1222的结果可读性比较差,生产环境大多数还是用profiler抓Deadlock graph事件来看死锁链,其实是xml格式的。阻塞一般用微软在KB271509中给出的sp_blocker_pss80存储过程来排查。更进一步可 以自己从DMV(sys.dm_exec_*)里面找。
12 SQL Server默认的error log文件在复杂系统中会增长很快,特别是大量复制的场合。一般每天调用sp_cycle_errorlog强行划分一下日志文件,避免单个文件太大,以 后排查也便于使用日期查找。日志文件无法收缩也要注意使用dbcc loginfo和dbcc log观察内部使用情况。
14 数据比较最有效的方法是使用内置的tablediff命令,但是要注意该命令对中文支持有些问题,有时需要在参数中手工指定code page。重置identity使用dbcc checkident命令
16 Log Shipping和oracle的Data Guard,原理及实现基本相同。Mirror在oracle中没有特别对应的功能,勉强在底层可以说与RAC对应。Mirror目前还不是高可用的主力,主要应用在于快速cutover。
17 做了mirror的数据库日志文件在收缩之前需要手工备份一次
19 replication新增字段现在不需要特殊存储过程了,直接在发布端新增就可以了,会自动同步。发布端大规模操作应限制每个事务影响的记录条数,并在事务之间插入waitfor延时防止阻塞。
20 SSB还是非常有用的,可以说是微软平台目前最有效的message queue方案,建议楼主多关注。

此条目发表在未分类分类目录,贴了, 标签。将固定链接加入收藏夹。