SQLServer数据库日常运维与性能优化方法

接手一个SQL Server数据库，最怕的不是报错，而是慢得莫名其妙，还找不到原因。日常运维不是等出了问题再去救火，而是把80%的隐患消灭在日常的巡检和配置中。很多人习惯用默认设置上线，跑了一段时间后，CPU飙高、内存吃满、IO延迟大到业务无法忍受，回头一看，连最基本的最大内存限制都没设。SQL Server默认会尽可能多地占用内存，这本身不是问题，问题是如果不给它一个上限，当服务器还有其他应用或操作系统本身需要内存时，就会发生内存争抢，导致SQL Server工作集被修剪，性能急剧抖动。直接上服务器，打开Management Studio，右键实例属性，在“内存”页把“最大服务器内存”设为一个合理值，这个值不是拍脑袋，是看服务器总物理内存，给操作系统和其他服务留出至少2到4GB，剩下的分配给SQL Server。这是运维的第一步，也是最容易忽略的一步。

索引碎片是性能衰减的慢性毒药。数据增删改频繁的表，索引内部页的逻辑顺序和物理顺序会逐渐不一致，导致扫描时需要更多的IO。不要等到查询从毫秒级变成秒级才去管。日常运维中，需要定期检查索引碎片率。可以通过sys.dm_db_index_physical_stats这个动态管理函数来抓取数据，重点看avg_fragmentation_in_percent这个字段。碎片率大于30%且页数较多的索引，用ALTER INDEX REBUILD重建；碎片率在5%到30%之间，用ALTER INDEX REORGANIZE重组。重建会锁表，在繁忙的生产库上要慎重，企业版可以用ONLINE=ON选项在线重建，标准版就得挑窗口。重组虽然力度小，但在线操作，对业务影响可控。把这些操作写成脚本，放进SQL Agent作业里，每周或每月跑一次，跑完后记得更新统计信息，因为重建索引会自动更新统计信息，但重组不会完全更新，需要手动跑一下UPDATE STATISTICS。

比碎片更隐蔽的是缺失索引。SQL Server在编译查询计划时，如果发现没有合适的索引，会把建议记录在动态管理视图里。经常查一下sys.dm_db_missing_index_details、sys.dm_db_missing_index_groups和sys.dm_db_missing_index_group_stats这三张表关联的结果，可以看到数据库建议创建哪些索引，以及这些索引如果存在能带来多大的性能提升。但不要盲目照搬建议，要结合业务查询模式，把建议里的等值列、不等值列和包含列分析清楚，合并重复度高的建议，避免索引冗余。建索引是双刃剑，加速查询的同时拖慢写入，所以每一个新增索引都要评估写入负载的承受能力。

遇到性能问题，第一反应不应该是去翻慢查询日志，而是先看等待统计。SQL Server内部有清晰的等待类型体系，查询sys.dm_os_wait_stats视图，按wait_time_ms降序排列，能立刻看到数据库到底在等什么。常见的高发等待类型，比如PAGEIOLATCH_SH，说明存在严重的数据页IO瓶颈，查询需要的数据不在内存里，要从磁盘读，这时候就要去看是不是内存给少了，或者查询扫描了太多没必要的数据页。LCK_M_开头的等待，是阻塞和死锁的征兆，说明事务设计或索引策略有问题。WRITELOG等待高，意味着事务日志写入磁盘成为瓶颈，日志磁盘的IO性能跟不上事务提交的速度。ASYNC_NETWORK_IO等待，往往不是网络真慢，而是客户端程序一行一行取数据，没有用批量获取的方式，让SQL Server干等着把结果发完。每次排查性能问题前，先把这个视图的数据抓下来，和基线对比，方向就不会错。

tempdb的设计和配置直接影响整个SQL Server实例的并发能力。所有用户的临时表、表变量、排序溢出、行版本控制都在这里面发生。默认情况下，安装SQL Server时tempdb只有一个数据文件，这在多核服务器上是巨大的并发瓶颈，因为多个会话同时分配页面时，会争抢SGAM和PFS页面的闩锁。运维规范里必须有一条：根据CPU核心数拆分tempdb数据文件。通常的做法是，逻辑核心数小于等于8，就建8个数据文件；超过8个，以4的倍数递增，但不要无脑堆到和核心数一样多，一般8到16个足够。所有数据文件大小必须完全一致，并且开启自动增长，但增长步长要固定，不能按百分比。文件放在独立的快速磁盘上，最好是SSD。做完这些，重启SQL Server服务让配置生效，tempdb的争用问题能解决一大半。

日志文件不是数据文件，它的写入是顺序的，随机读写对它没意义，所以不要把日志文件放在和数据文件同一个慢速磁盘上，更不要放在网络存储的高延迟路径上。日志文件的大小和自动增长设置，是日常运维必须检查的项。很多人看到日志文件很大，第一反应是收缩，这是危险操作。反复收缩和自动增长会导致日志内部产生大量虚拟日志文件碎片，事务提交时需要切换VLF，性能会断崖式下跌。正确的做法是，先确认数据库的恢复模式，完整恢复模式下，必须配置定期的事务日志备份，日志备份会截断已提交的事务日志，释放空间供循环使用。如果没有日志备份，日志会一直增长直到磁盘爆满。如果日志已经失控变大，先做一次完整备份，然后做日志备份，再手动把日志文件收缩到一个合理的大小，比如当前日志实际使用空间的1.5倍，之后立即修改自动增长为固定大小，比如512MB或1GB，避免频繁的小步增长产生大量VLF。用DBCC LOGINFO可以查看VLF数量，如果几百上千个，说明增长步长太小，需要重建日志来清理。

存储过程跑得好好的，突然就慢到超时，过一会儿又自己好了，十有八九是参数嗅探在捣鬼。SQL Server在第一次编译存储过程时，会根据传入的参数值生成一个最优的查询计划，这个计划被缓存起来重复使用。问题在于，第一次传入的参数值可能是一个选择性极低的值，生成的计划适合全表扫描，但后续大部分调用传入的是高选择性的值，用全表扫描就灾难了。解决思路不是清空计划缓存，那个治标不治本。更稳妥的方法是在存储过程内部，对关键查询使用OPTION (RECOMPILE)提示，让这个特定的语句每次执行时都重新编译，牺牲一点编译CPU换取执行计划的准确性。如果不想每次都编译，可以在存储过程定义时使用WITH RECOMPILE，或者用OPTION (OPTIMIZE FOR UNKNOWN)让优化器按平均分布来生成计划。还有一个高级技巧，把存储过程里的核心逻辑拆成动态SQL，配合sp_executesql参数化执行，也能有效避免不合理的计划重用。

日常运维中，备份的优先级高于一切性能优化。没有备份，再快的数据库也是裸奔。完整备份、差异备份、事务日志备份三层组合，是标准配置。完整备份一周一次或每天一次，取决于数据量和恢复时间目标。差异备份在两次完整备份之间，频率可以比完整备份高，比如每四小时一次。事务日志备份在完整恢复模式下必须高频执行，每15分钟或30分钟一次，这样一旦发生故障，最多丢失15分钟的数据。备份文件不要放在数据库服务器本地磁盘上，要异地存储，或者至少拷贝到另一台存储设备。更重要的是，备份必须定期做还原演练，不验证的备份等于没备份。写个脚本，每个月自动把最新备份还原到一台测试服务器上，跑一遍DBCC CHECKDB，确认数据一致性，这才是完整的运维闭环。

人工巡检总有遗漏，必须建立自动化监控体系。不需要多复杂的第三方工具，SQL Server自带的系统视图和SQL Agent就能搭起一套基础监控。创建作业，定期收集sys.dm_os_performance_counters里的关键计数器，比如Page life expectancy、Buffer cache hit ratio、Batch Requests/sec、User Connections等，写入一个监控数据库的表中。当Page life expectancy这个值持续低于300秒时，说明内存压力大，数据页在内存里呆不住，频繁被刷出，这时候就要考虑加内存或者优化查询减少数据扫描。Blocked Process Threshold配置项可以设置阻塞阈值，配合事件通知或扩展事件，一旦有会话被阻塞超过设定秒数，自动记录阻塞链信息到日志表，方便事后分析。把这些监控数据做成基线，每周对比，性能衰退的趋势一目了然，在问题爆发前就能介入处理。

数据库用久了，物理文件可能出现页损坏，这种损坏在查询报错之前，可能已经存在了很长时间。DBCC CHECKDB是SQL Server提供的数据库一致性检查命令，会检查数据库的分配结构、表间链接、系统表完整性和每个页的校验和。这个命令很耗IO和CPU，但不能不做。在维护窗口内，至少每周对核心业务库跑一次完整检查。如果库太大，可以用PHYSICAL_ONLY选项先跑物理一致性检查，速度快很多，逻辑一致性检查可以放在月度的深度维护里。跑CHECKDB的时候，务必指定NO_INFOMSGS，减少输出噪音，重点看最后的错误信息。一旦发现页损坏，立即从最近的有效备份还原，不要尝试修复，修复选项往往会导致数据丢失。

运维安全容易被性能问题掩盖，但权限失控是更大的隐患。定期审计数据库用户和服务器登录的权限，重点看谁有sysadmin角色、谁有CONTROL SERVER权限、谁在具体数据库里有db_owner角色。生产环境应该严格遵循最小权限原则，应用程序连接数据库的账号，只给对特定存储过程的执行权限和对表的增删改查权限，绝对不给DDL权限。用sys.server_permissions和sys.database_permissions视图可以导出完整的权限清单，和上个月的版本做比对，任何新增的高权限账号都要追查来源。SQL Server错误日志和Windows事件日志也要定期检查，异常的登录失败、密码更改尝试，可能是暴力破解的前兆。

日常运维需要一个标准化的配置检查清单，新上线的实例按这个清单过一遍，能避免90%的初级问题。最大服务器内存必须设置，不能留默认值。最大并行度根据CPU核心数调整，OLTP系统通常设为4或8，避免单个查询并行度过高吃掉所有CPU。备份压缩默认开启，节省备份时间和空间。数据库自动关闭和自动收缩必须关闭，这两个选项对性能的破坏是毁灭性的。即时文件初始化功能，通过给SQL Server服务账号添加“执行卷维护任务”权限来启用，能让数据文件增长瞬间完成，不用等待零填充。远程DAC连接开启，方便在实例无响应时通过专用管理员连接进去排查。这些配置项，每一项背后都有血泪教训，不要等踩了坑再去改。

有些查询，优化器就是选不对计划，加提示也不稳定。这时候可以用查询存储来强制计划。查询存储是SQL Server 2016之后引入的功能，它会持续收集查询的运行时统计信息和执行计划，保存在数据库内部。启用查询存储后，在图形界面里可以直观看到某个时间段内，哪些查询消耗资源最多，哪些查询出现了计划回归。对于已经确认的最优计划，直接右键强制使用这个计划，后续所有执行都会走这个计划，直到你手动取消强制。这个功能在解决因统计信息更新、数据分布变化导致的计划突变时，非常有效。但要注意，查询存储本身会消耗一定资源，需要监控其大小和清理策略，保持在一个合理的数据保留期内。

SQL Server的日常运维和性能优化，本质上是对资源的精细化管理，对等待的准确解读，和对执行计划的持续干预。没有一招鲜的银弹，只有把这些检查项、脚本、监控变成肌肉记忆，才能让数据库跑得稳、跑得快。每次处理完一个性能故障，把根因、解决步骤、预防措施记录下来，形成自己的知识库，这才是运维能力持续提升的正循环。