The Accidental DBA

时间:2021-08-25 15:47:48
本文大意:
     全篇主要讲硬件选择和服务器成本的考虑,包括内存的开销,cpu开销,以及sql server的收费方式。
本文大意:
     存储在sql server中占了很重要的角色,存储子系统配置的不对就会让server性能很烂。
     磁盘比较多的性能往往比磁盘少的性能好,因为磁盘多吞吐量大。
     关于容量的评估,可以更具,评估文件大小,tempdb大小,备份大小
     关于性能的评估,可以评估,顺序,随机的读写性能
性能测试工具:CrystalDiskMark
本文大意:
     关于SSD磁盘的使用,首先要看传统的磁盘和SSD的区别,通过测试传统的磁盘,顺序读写性能远远高于随机读写。而SSD除了顺序读写性能不俗之外,随机读写性能远远的提升。那么很显然了,如果传统的磁盘为了适应服务器性能需求,花费比使用SSD要大的时候,那么就可以考虑使用SSD来代替。一般适用的场景:1.单实例多数据库,随机io过大,可以考虑把log放入ssd中,2.tempdb io过大可以考虑把tempdb放ssd中。
本文大意:
     sql server的安装配置最佳实践从还未装之前就已经开始,在决定要cpu,服务器,io子系统之后,
     1.先保持bios,os版本最新。配置专用的域账号。
     2.在bios的电源控制中是否选择了关闭或者OS控制。windows上的电源控制是否选择了高性能。
     3.是否开启超线程,是否经过了测试。
     4.raid级别,需要的空间,是否需要多个逻辑盘,使用CrystalDiskMark,SQLIO测试性能,raid的cache大小,cache策略是否设置成需要的
     5.启动Windows instant File Initialization和Page Lock
     6.把sql server更新到最新版本
     7.设置最大服务内存
     8.Optimize for ad hoc workloads是否配置
     9.tempdb多文件考虑
     10.启动T3226关闭,当备份成功后往errorlog写入信息
额外阅读:

Provisioning a New SQL Server Instance – Part Two

Provisioning a New SQL Server Instance – Part Three

本文大意:
     使用虚拟化的原因:因为虚拟化便宜
     缺点:1.io达不到要求。2.VMWare下,VM会过渡使用内存。3.过渡使用cpu问题
     IO达不到要求
          作者使用6个1T的7200-RPM在NetApp SAN的磁盘和5400-RPM的usb2.0的磁盘做了性能对比:
          The Accidental DBA
     发现2个性能差不多,对于SQL Server来所这样的io性能是不够的,并且如果出现io size的问题SAM并没有好的解决方案,SQL Server对磁盘的要求不单单是空间,还有性能
     内存问题:
     内存大了可以减少io,VMWare和hyper-v内存的分配方式不同,VMWare默认可以超过设置的内存,但是hyper-v启动时会检查可以使用的内存数量,不会超过这个数量,但是当有压力时,有一个最小可用内存保证了内存的需求。VMware也可以静态的保留内存以免VM过渡的使用内存。
     CPU问题:
     VM是通过共享cpu片段来实现并发问题,当一个host分配为一个宽VM一个窄VM就很容易出现调度的问题。对于4个虚拟处理器的VM,当其他VM发生并发时,可以调用的cpu时间片并不是4个cpu。为了解决这个问题引入了协作调度(co-schedule)
     在虚拟环境下经常会碰到特殊的workload导致cpu性能问题,表现在VM的cpu利用率高,增加vcpu反而加重问题。减少vcpu反而性能编号。说明协同调度有问题,要不是过度的提交,要不就是宽,窄混合,导致调度问题。

The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO

本文大意:
     RTO:恢复时间需求,RPO:允许丢失的数据
本文大意:
     不管对数据库做了什么修改都会产生日志,日志的去向就2中1.提交,2.回滚。
     管理事务日志,不能让日志文件过大,妨碍数据库正常使用。
     恢复模式:
     完全:日志全部记录,在日志备份时被截断
     大容量日志:某些日志被最小化记录,在日志备份时截断
     简单:某些日志最小化记录,checkpoint被截断
     备份类型:
     全备,日志备份,差异备份,文件组备份,文件备份,文件差异备份,文件组差异备份
     全备:备份所有数据和一部分日志,允许一致性事务点,全备不会截断日志,全备的备份日志量:从备份读取数据时最早的活动日志到,备份读取结束的日志
     事务日志:备份所有日志,要在全备之后才能使用,第一次全备之后,全备和日志备份将不再有任何关系
     差异备份:从你上次全备后的所有修改,差异备份是累计的,不是递增的
本文大意:
     还原最主要的2个问题:1.要多久,2.可以接受多少数据的丢失
     根据以上的2个点来设置还原策略,进而设计备份策略
     比如,如果数据丢失可以接受在15分钟,那么就日志的备份至少是15分钟一次,若无法承受,那么最后还要恢复尾日志。当然还可以使用同步进制如镜像
     恢复时间,需要同个各种恢复策略的测试,保证恢复时间在指定时间内。
笔者意见:
     用备份来做灾难性恢复,随着数据库越来越大,已经有点不太现实了,一般的做法还是使用数据库冗余,镜像,故障转移等手段。备份如果只是用来做容灾,那么已经有点落伍了。

本文大意:
     压缩:可以让备份更快,更小,但是费cpu
     Copy_only:只复制,对日志备份中,不会对日志链产生影响
     Description和File Names:加入一些描述性的东西到备份文件中。
     Checksum:1.验证从数据文件中过来的page,如果checksum对不上,默认备份失败,并且报出现数据页错误。2.对整个备份计算checksum并放入备份文件头
     Status:用来表示backup的进度

本文大意:
     验证备份,不单单是验证备份的文件是否可用,而且还要验证是不是在备份恢复的时间内,DBA不单单要保证备份的正确性,而且要验证备份。保证随着时间的推移不会造成备份文件错误,备份验证比较简单只需要做恢复就可以了。如果要验证备份内容的正确性,那么在备份的时候使用checksum,在恢复的时候也使用checksum,用来验证存在备份头里面的checksum是否正确。同时需要考虑备份保存的问题,保存在哪里,保存时间多长。
本文大意:
     关于备份的保存有2个重大问题:1.不要保存在和数据库同一个io子系统下,2.只保留一份最新的backup
     如果数据库突然crash,那么在异地有一份就可以迅速还原上来,但是 往往是本地的一台机器crash,其他机器没事儿,所以不但要在异地保留一份,要在本地异机也保留一份。
     如果只保留一份最新的备份,如果刚好,全备出错,那么就无法恢复了。所以,备份的保留时间也是一个问题。本地至少要保留一个月的备份,异地至少3个月。除非没周都测试。可以适当减少。
本文大意:
     关于VM快照的备份,作者说了很多,但是关键点是,VM快照备份有些地方不给力,如不能根据日志链恢复,只能恢复到快照的点。好处,可以恢复到某个文件,甚至是某一个对象。还可以帮忙截断事务日志。
本文大意:
     DBCC checkdb,如果要检查整个库需要执行DBCC CHECKALLOC,  DBCC CHECKCATALOG,  DBCC CHECKTABLE and  DBCC CHECKFILEGROUP是不正确的。
     对于大的数据库,可以先运行checkfilegroup,然后执行checkcatlog来分解。checkdb,检查尽早的排除数据错误的问题。如果出现问题,可以使用错误日志发现问题。发现问题后需要恢复,就要考虑2个问题:1.可以丢失多少数据,2.会宕机多久。可以先通过备份简历测试,通过测试发现是否在可以接受的范围,然后再处理。当dbcc checkdb恢复后,是不管约束的,所以之后要使用dbcc checkident和dbcc checkconstraints善后。
额外阅读:
本文大意:
     所以维护主要是维护索引碎片的问题。定期重建或者重组。碎片2个坏处:1.造成io量大,2.造成内存,空间浪费。
     对于脚本维护有一下建议1.少于1000页不用处理,2.碎片少于10%不用处理,3.10-30%重组,4.30%以上重建。
     Ola Hallengren’s Maintenance Solution是很牛b的脚本,可以阅读一下然后使用。

本文大意:
     对于统计信息维护,sql server会自动维护的规则:若 <500,累计500行更新,重新统计,>500则500+20%更新。
     统计信息是以BLOB方式存放在数据库中,一般自己不维护,而是更具优化器的需要自动创建,一般只有大的表需要做手动的统计信息维护。统计信息主要是包含了key的数据分布。统计信息分为3个部分:1.头,2.密度向量,3.直方图。
     统计信息的准确性,有很多因素,其中比较重要的是:表的大小,表是否频繁被修改。注意:索引重建会更改统计信息,但是索引重组不会。
      Ola Hallengren’s Maintenance Solution也可以用来维护统计信息
额外阅读:
本文大意:
     使用Agent alert可以用来反映严重错误的信息。通过配置agent alert 和操作员来完成。
     并且作者分享了一段代码
本文大意:
     谈到基线,就有4个问题:1.为什么要有基线,2.怎么获取基线,3.什么时候抓数据,4.怎么分析
     为什么要有基线:在出问题之间,提前发现问题;可以主动去调整;通过直方图发现其中变法,排查问题;数据和环境的变化;制定资源和能力计划
     怎么获取基线:通过抓取DMV,性能计数器
     什么时候抓数据:有些可以一天一次,比如可用空间,有些需要间隔几分钟一次比如性能指标。
     怎么分析:通过分析数据的变化来预测未来将要发生的情况
本文大意:
     主要介绍一些用来做监控的工具:
     1.性能计数器,比较全面,系统自带兼容性好。
     2.PAL,也是收集性能指标的,有统一的套件,不需要再自己配置了
     3.cleartrace,RML是用来分析sql trace的工具,有被扩展事件代替的趋势
     4.SQL Nexus是用来分析,SQLDiag和PSSDiag的结果
     5.DMV监控
我觉得在使用工具之前必须要了解是干什么用的,怎么分析的,才能用的得心应手
本文大意:
     索引是个很头痛的问题,如果不合适造成性能问题,如果没有select有性能问题。如果太多更改有性能问题。空间又浪费
     没有用的索引需要删除:
            1.完全重复的索引,可以使用通过DMV找出完全一样的索引干掉
            2.超出没用的索引,通过sys.dm_db_indes_usage_stats超出,干掉,注意,视图中的user_update是以语句个数来计算的
            3.相似的索引,索引相似一般分为几种情况,1.key相似 2.key的左边相似。 如果合并,会让io变大,可能会造成另外一个语句变烂,但是减少了空间,减少了维护成本,如果不合并浪费空间,浪费内存,更改性能可能会有潜在变化,并且便随死锁的出现。这些还是要看语句是否需要窄的索引。
     检查已存在的索引:
          1.主要是碎片的维护,2.填充因子的设置
     添加新的索引:
          添加新的索引是比较有难度的,因为需要分析已经存在的索引,如果只是单纯的添加索引,那么只会让索引越来越多,越来越臃肿,可以以miss index为指导创建索引。如果有miss index提示,那么把语句放到DTA上面,分析这样会比miss index更为全面,miss index 是为每个索引找最合适的索引,所以有时候需要考虑索引合并问题
额外阅读:
     讨论常用的一些性能指标的意义,性能计数器可以通过PAL进行统计,好处可以减少配置的时间,坏处是阀值是被写死的。
     CPU相关统计信息:
    • Processor
      • %Processor Time
      • %Privileged Time
    • Process (sqlservr.exe)
      • %Processor Time
      • %Privileged Time
     处理器有多少cpu时钟被使用,有多少被用在内核模式,程序占用了多少cpu,有多少使用与内核模式
     内存相关统计信息:
    • Memory
      • Available Mbytes    可用内存
    • SQL Server:Buffer Manager
      • Lazy writes/sec             Lazy write 次数
      • Page life expectancy    页生命周期
      • Page reads/sec             每秒页读取次数
      • Page writes/sec            每秒页写入次数
    • SQL Server:Memory Manager
      • Total Server Memory (KB)            总共服务内存
      • Target Server Memory (KB)          目标服务内存
    磁盘相关统计信息:
    • Physical Disk
      • Avg. Disk sec/Read                   读取延迟
      • Avg. Disk Bytes/Read               读取字节数
      • Avg. Disk sec/Write                   写入延迟
      • Avg. Disk Bytes/Write               写入字节数
    • Paging File
      • %Usage                                         page file使用率
    • SQL Server:Access Methods
      • Forwarded Records/sec              顺序记录数
      • Full Scans/sec                              扫描次数
      • Index Searches/sec                      查询次数
       对读取和写入延迟有一个推荐值来确定io是否正常:
        < 8ms: excellent
< 12ms: good
< 20ms: fair
> 20ms: poor
本文大意:
     决定使用高可用的策略:
     1.确定会使用到什么技术,并且是否有某些高可用会对这些技术排斥
     2.选择高可用技术
     3.测试高可用性,是否能够达到高可用的需要,并且对性能造成的影响可以在接受的范围内
本文大意:
     最不想聊的,就是说一些高可用和灾难恢复的一些技术,而且很浅。新人可以以此为切入点深入
本文大意:
     当sql server执行一个task时,出现的等待都会被记录到sys.dm_os_wait_stats上面,在paul的博客上面:wait stats post介绍了有关这个DMV一些脚本。然后过滤掉不想管的wait stats。wait stats只能当做一个troubleshooting的方向和切入点,不能认为真的是这个问题。
额外阅读:
      wait stats post
本文大意:
      对于dba来说,不单单是存储空间,还有性能,吞吐量,sys.dm_io_vaitual_file_stats获取数据库io信息。里面的值都是累加的,只有重启时才会重置。这个dmv不但有io延迟,还有读写次数和读写的字节数,用来标记读写做多的文件。
     io的延迟使用avg disk sec/write 和 avg disk sec/read,磁盘缓存,控制卡,存储系统都会影响延迟。延迟不单单是和host和磁盘相关,是从host到磁盘的整个路径,如总线,交换机,SAN控制器,磁盘。一般资深的存储管理员都会知道这个路径。
     avg disk bytes/read和avg disk bytes/write用来表示吞吐量,要测试吞吐量的极限可以简单的创建索引,来增大io的量。
     对sys.dm_io_vaitual_file_stats建立极限可以有详细的信息,说服存储管理员给予性能上的支持。还可以帮助预测将要发生的问题。如果io是渐变的,那么考虑是否在预期的范围内。SAN是共享存储,所以也需要考虑是否有可能是这个原因造成io上升,可以在数据库性能恶化钱给SAN管理员一个参考值。
本文大意:
     tempdb冲突时一个典型的冲突,大量的查询使用tempdb,当创建时,要分配page,元数据,处理FPS,GAM,SGAM等,为了优化,sql server对做了一个cache,更多信息可以看tempdb的白皮书。
     根据以下sql可以查看sql server中所有的堵塞:
     SELECT
    [owt]. [session_id],
    [owt]. [exec_context_id],
    [owt]. [wait_duration_ms],
    [owt]. [wait_type],
    [owt]. [blocking_session_id],
    [owt]. [resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
                RIGHT ( [owt].[resource_description] ,
                CHARINDEX ( N'=', REVERSE ( [owt].[resource_description] )) - 1)
            ELSE NULL
        END AS [Node ID],
        [es].[program_name] ,
        [est].text ,
        [er].[database_id] ,
        [eqp].[query_plan] ,
        [er].[cpu_time]
     FROM sys .dm_os_waiting_tasks [owt]
     INNER JOIN sys. dm_exec_sessions [es] ON
        [owt].[session_id] = [es].[session_id]
     INNER JOIN sys. dm_exec_requests [er] ON
        [es].[session_id] = [er].[session_id]
     OUTER APPLY sys. dm_exec_sql_text ( [er].[sql_handle] ) [est]
     OUTER APPLY sys. dm_exec_query_plan ( [er].[plan_handle] ) [eqp]
     WHERE
        [es].[is_user_process] = 1
     ORDER BY
        [owt].[session_id] ,
        [owt].[exec_context_id] ;
     GO
若大量出现FPS,GAM,SGAM堵塞,一般是2:1:1,2:1:2,2:1:3(fps间隔8088个页,GAM/SGAM间隔 7988*8页,页可以使用dbcc page 查看pagetype确定是否是GAM/SGAM/FPS)。
若出现冲突解决方法:1.减少临时表的使用,2.开启1118,3.创建多个数据文件。方法1比较简单,只要减少tempdb的使用就可以了,方法2:如果还出现冲突,启动1118,当表一上来就直接在专用区分配,而不是在混合区,这个trace是全局的,不单单是tempdb生效。方法3:也是现在用的比较多的方法,被认为是最佳实践,创建多个文件,文件个数=min(8,逻辑内核)+4*N
本文大意:
     一般导致锁堵塞的情况:1.无效的更新字段,2.update 没有相关index帮助,3.事务存在用户交互问题。
     通过对wait stats建立等待baseline,可以及时的发现问题。也可以使用sys.dm_os_waiting_tasks监控堵塞问题。
      A DMV A Day – Day 27 可以更具获取堵塞头,如果已经获取了堵塞链源头的spid,那么就可以根据以下几个方法获取到底是如何发送堵塞的:
     1.使用sp_blocker_pss08,2.使用SQLDiag,3.使用 Adam Machanic’s sp_WhoIsActive(但是我觉得,这3种我都觉得怎么样,更倾向于直接使用dmv获取堵塞链)
     解决方法:1.是否可以通过调整索引解决,2.是否考虑使用行版本
额外阅读:
     死锁的信息可以通过1222,1204,1205trace flag,写入到错误日志中。
     除了以上的方法,还可以使用SQL Trace,消息通知,WMI和扩展事件收集消息。
     Graphically Viewing Extended Events Deadlock Graphs 介绍了把deadlock graph重命名为.xdl然后可以在2012 ssms中打开,显示可视化界面。
     然后通过等待和已获得的资源的信息,分析死锁,调整死锁。可能的解决方案:1.创建索引。2.使用行版本
额外阅读:
本文大意:
     对于新手来说,往往会出现日志文件很大,但是数据文件相对来说很小。造成这个问题一般,除了程序bug之外,1.使用了完全恢复模式,但是没有日志备份,2.全备后被手动切换到完全模式,但是没有日志备份。
     SELECT [log_reuse_wait_desc] FROM sys.databases;
     使用这个sql查看到底是什么原因造成的如果是LOG_BACKUP那么就是日志没有备份的问题。
     每次日志增长都会带来一些问题:
     1.日志文件初始化,让写入操作暂停
     2.日志增长,日志块页会增长(应该指的是虚拟日志文件),会对性能造成影响(特别是olap负荷)
     3.日志大,恢复时间长
额外阅读: