缺少索引

      案例：某次线上有很多接口请求失败，服务器上发现CPU使用率很高.通过活动分析器(最近耗费大量资源的查询)发现有几个查询的CPU时间(毫秒/秒)达到3000以上。推测是查询没有利用索引导致的，通过在相关表上加索引解决了问题。

      分析：大部分性能问题都是因为缺失索引或索引失效导致的(书签查找)。此次问题是由于基础表(配置表)之间的连接没有索引，当随着配置的数据越来越多，查询计算量会越来越大，当数据量达到一定量级(几千至几万)且并发比较高(几千)问题就会显现出来。这些问题可以在写查询时建立索引(通过对数据总量和并发的预估)，也可以定期通过活动分析器针对CPU时间比较多的查询添加索引。

TempDB争用

      案例：系统某个高并发接口近期经常请求失败，服务器上发现CPU使用率和硬盘占用时间均比较正常。通过活动分析器(资源等待)发现Buffer Latch等待时间（毫秒/秒）达到30000以上。通过逐段分析存储过程性能以及查阅网上资料定位到是TempDB闩锁问题，后经过几次针对性优化解决了此问题。

      分析：闩锁是数据库为保护内部数据结构所使用的锁，比如为了防止多个线程同时写一个数据页会在写之前加上闩锁。临时表的创建和删除过程会涉及到数据页的分配和回收，高并发接口中使用临时表会导致频繁修改记录空间分配的页，从而产生较多的锁等待（影响并发性能）。期间做过将临时表换为表变量，但由于临时表和表变量在存储等方面的机制相似，效果不太理想。后来发现表连接用到了Split函数，而函数里会用到临时表,这将导致n倍临时表的创建和删除，通过将split函数替换为like效果显著。后来估计并发量变大，又开始出现请求失败，通过优化逻辑先join过滤后select into 到临时表，可以减少临时表的创建（select into 行数为0时似乎对性能没有影响），有一定效果。后来又出现了问题，通过创建多个tempdb文件可以将临时表空间的分配分担到多个页上，减少锁的争用，到现在还没有再出现问题。这几次优化都是循序渐进的，虽然到最后一步才把问题解决，但是越是前面的步骤优化的幅度越大，对于并发特别高(>5000)且需要用到临时表的场景应考虑在web端进行逻辑优化（如加入redis缓存等）。

收缩数据库时间太长

      案例：系统数据库所在磁盘空间不足，数据无法写入，急需通过删除部分历史数据后收缩数据库腾出空间。收缩整个数据库花费比较长的时间(几分钟)，这段时间会影响数据库的执行，应想办法缩短时间减少影响。

      分析：由于数据库空间规划不当或者业务数据急剧增多导致硬盘空间不足无法写入数据，这时必须要收缩数据库。数据库收缩原理是将文件末尾的数据移动到文件前面然后截断文件，耗时与移动的数据量成正比。可以先分几次收缩固定大小的空间(有空间写入，分几次例如每次1G容易把握每次收缩的时间)，到凌晨时可以通过维护计划收缩数据库，减少对线上用户的影响。

作业执行失败

      案例：一段时间每天晚上某个接口都会有几次访问失败的记录，后来发现是由于维护计划作业失败导致的。删除了维护计划里的重新组织索引、重新创建生成索引、更新统计信息就再没有出现此类问题。

      分析：有些只在某个时间段才偶现的问题可能是由于作业执行导致的，可以通过对比作业执行起止时间和错误的发生时间可以定位是否为作业导致的，以及是哪个作业导致的。维护计划里的一些操作如重新组织索引、重新创建生成索引、更新统计信息等貌似作用不大(数据库默认会自动更新统计信息)，却非常耗时且影响线上的数据库使用，可以选择删除。

修改数据结构失败

      案例：某次线上做已有大表分区,在交换表名称这一步时交换失败。后来将操作之前的备份表改为线上表导致CPU升到100多，打算将原表改为线上表却一直修改失败。后来临时禁用了sqlserver账号,就可以修改表名称了。

      分析：sql执行期间会有架构稳定锁，对于高并发访问的表很容易修改数据结构失败。此时建议先临时停止相关业务或禁止sqlserver账户，再修改数据结构。

总结

1. 如果CPU很高可以通过活动分析器(最近耗费大量资源的查询)找出耗时最长的查询。如果有比较耗时的查询（CPU时间(毫秒/秒)大于3000以上）应尝试按照缺少索引进行优化。
2. 如果CPU使用率和磁盘的占用时间都比较正常，可以通过活动分析器(资源等待)找出等待时间最长的等待类别。如果Buffer Latch占用时间比较长（等待时间（毫秒/秒）达到30000以上），应尝试按照TempDB争用进行优化。
3. 可以查看作业执行记录，判断问题是否由作业导致的
4. 线上收缩数据库最好能分批收缩数据库，这样时间可控，也可以减少对线上业务的影响。
5. 线上紧急情况下需要修改数据结构，但由于锁等原因操作失败时可以临时停止相关业务或禁止sqlserver账号。

SqlServer常见问题及案例(性能+维护)