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.日志大，恢复时间长
额外阅读：

相关文章