sqlServer慢sql问题的排查与解决和优化

引言

           前一段时间，项目将要上线到现场时，发现开发服务器上的程序，查询开始变得越来越慢。今天对该类问题写个总结，已记录下来。

慢sql出现的原因

• 没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)。
• I/O吞吐量小，形成了瓶颈效应。
• 没有创建计算列导致查询不优化。
• 内存不足 。
• 网络速度慢 。
• 查询出的数据量过大(可以采用多次查询，其他的方法降低数据量)。
• 锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)。
• sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
• 返回了不必要的行和列 。
• 查询语句不好，没有优化 。
  以上都是前人总结的相关八股文，发钱上面的第1条第4条以及第7条第9条问题在我们的系统中都是存在的。

问题排查

    通过问题定位与一个表有关，我们现在将这张表定义为A表，A表在项目中被大量引用，当查询时，出现一个慢sql现象从而导致其他的查询（与A表相关联的查询）也开始变慢。

  下面是问题排查的具体过程

    0.当执行完与A表相关的语句之后，发现整个系统都变慢了，我们首先查看了系统中是否存在大量的锁表，发现与A表被锁住了，导致其他表也开始锁住，当解锁之后，系统开始变快了。
    1.然后我们定位到那一条查询语句，在系统中执行一遍，发现系统中就会有表被锁住，后面我们开始对那一条语句进行优化。
    2.我们考虑到，可能是因为与A表关联的其他表，在关联字段上没有加索引，导致了查询变慢，当我们将索引加上之后，我们发现速度并没有变快。数据量在5000以内，查询居然要27s左右，这肯定是不可以接受的。
    3.后来我们发现只对大量引用的那张表进行单独查询时使用到order by 进行排序，耗时居然也在27s左右。因此我们发现可能是因为使用了order by 数据库自动排序存在的原因，但是问题真的是这样吗？
    4.接下来我们，将原来那个sql语句中的order by 去掉，运行sql语句，发现速度依然没有变快。
    5.我们又改变了思路，想到了是不是因为使用了 select * from A ,并未有遵守 用那些字段去哪些字段呢.因此我们取用了部分字段做连接查询，发现并没有变快。
    6.后面我开始在csdn，博客园，百度查找有没有相关的解决方案，收获甚微，我发现大量对sql优化的文章，都会查看sql的查询过程因此我改变了思路，先看以他的执行过程，发现那一条语句在执行时，sqlserver 会对拿一条语句进行自动优化，自动排序，因此出现了耗时的情况。
    7.既然病根找到了，那我又开始查找相关的解决方法，发现可以通过强制索引减少sqlserver自动优化产生的排序。
    8.当加上了强制排序之后，执行该sql语句发现，由原来的27s变成了0.02s。哈哈，我以为我成功了，第二天现实又把我从兴奋中拉到了苦海
    9.当第二天开始上班之后，我们发现了，又出现了很多大量锁表的sql，后面我们开始后顾，在前两天还没有种种问题，然后我的师傅去服务器那边看了一下，发现了内存开始不足了,重启了一下服务器，系统又开始丝滑顺畅了(<~~>)。

排查过程中的知识记录

  SqlServer锁表与解表

• 查看被锁的表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';

# 这时会出现两列数据
第一列 --spid 锁表进程
第二列 --tableName 被锁表名

• 对锁表进行解锁

declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

• 查看所有的spid

select blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)

• 输出产生死锁的操作

DBCC INPUTBUFFER (@spid)

看懂SqlServer查询计划

sql日常优化分享