记录工作中有关SQL Server中的SQL优化的点点滴滴，只是简单的记录，不太详细。

 

2012年2月5号：

今天周日，天气不错，为了帮助数据采集部门完成报表，所以周六、周日加班，基本上已经把sql语句写好了，待他们配置好，就可以测试了。我想把之前在工作中遇到的问题，记录一下：

1、在做某个项目的报表时，由于要显示10种品牌产品的数据，所以一开始按照每种品牌单独统计，但是速度非常慢

2、所以通过转置，一次就求出了10种品牌的所有统计数字，这样虽然效率稍微高了（3分钟吧），但还是很慢。

3、偶然的把语句中的一个Left join 改成了inner join之后，速度大幅提升，这时我查看了语句执行计划，不过也没发现什么明显的异常，于是我采用尝试的方法，加了连接提示，把left join 改为left hash join。

4、对于数据量太大的，接近1000万的表，在写sql的时候，一定要充分简化语句：

       a、没必要关联的表，就不要关联。

       b、用inner join关联、但是字段又不出现在select子句中的表，可以用where子句中的exists中的相关子查询来代替，这样的开销没有直接的表关联大，因为这里用的是半连接。

       c、针对效率低的语句，一定要建立索引。where子句中的字段可以写在复合索引，而关联字段和select中的字段，只需要写在include覆盖索引字段中。

       d、还可以考虑把查询中产生的部分结果集放到临时表、表变量中，这样也可能提高速度，实际尝试过，确实有效果。

       e、还有一点不得不说的就是，通过表提示中的 with (nolock)来读取未提交数据，防止被其他正在修改表的任务给阻塞住；当然直接在联接会话中设置：set transaction isolation level read uncommitted也是可以的；如果要保证报表中显示的结果总是读取已提交数据的话，可以通过设置数据库为读已提交快照：alter database dbname set read_committed_snapshot ，也就是通过读取行版本中维护的已提交数据，使得读不会被写操作阻塞。

暂时就写这些吧。

       f、假设数据量再大点，比如单表2亿条，两个表进行关联，该怎么办呢？我觉得可以这样，像这么大的表，应该考虑用分区表，如果没有再设计阶段采用分区表，考虑到报表总是显示汇总统计的信息，最后的结果不会太多，所以

可以考虑把语句的逻辑进行水平的划分，首先通过group by 建一张临时表，把id以及这个id对应的记录条数放进去，接下来通过每次取出临时表中的一条记录，进行统计计算，最后把所有的结果再进行汇总，这个有点像级联的层层分解，最后再层层汇总，也就是把大的问题变小，再把小得结果汇总吧，我觉得这个思路可以尝试！

 

 

2012年2月7日 ：

天气不错，今天主要是完成了 表数据（或者只是表结构sys.tables）、视图定义（sys.sql_modules）、函数定义（表值函数、标量函数sys.sql_modules）从一个库批量导入到另一个库中，复制过程中由于对象之间的依赖关系出了很多问题。另一个问题是，当改变某个表的某个字段的数据类型时，而这个字段被其他表作为外键了，而这个字段如果是主键的话，那么必须逐级先删除主键、外键约束（禁用约束不起作用），然后修改字段类型之后，再逐级加上主键、外键。

 

2012年4月7日：

天气挺好的，最近在做某个项目的报表时发现，语句并不复杂，可是运行速度非常慢，一开始想通过添加连接提示来加快速度，发现完全没有效果。

接下来把每个查询的结果都插入到临时表中，速度果然很快，但是最后把所有的临时表关联起来运行时还是很慢，查询出来的记录条数有几十万条，之前做的另一个项目的报表，结果中记录数更多，也没这么慢。最后直接登录到远程服务器上运行了语句，非常快，之前那么慢是由于公司网速太慢，导致传递相同量的数据，花了好几倍的时间。

在这个项目上的经验是：当查询速度慢时，网速也是一个要重点考虑的因素。

 

最近还在做另一个项目的报表，其中有一张拜访类报表逻辑比较复杂，需要通过工作时间、考勤时间、是否工作日等条件，来判断工作次数，本来要写多个语句才能完成的，我通过多个表关联，然后在select子句中写了很多case when来判断，只用一个语句就完成了，但是写的有点复杂了。

接下来又另一个报表，呵呵，没想到的是更加复杂，我也从同样的方法完成了，只能说写的太复杂。

后来想想其实可以在关联表的时候，增加一个关联字段：上下午字段，来区分是上午的工作，还是下午的工作，这样就能稍微降低开发的难度，同时使得语句易读、易懂了。

在这两个项目上的经验就是：在关系数据库的众多表之间，通过字段关联，来反应实体之间的关系，对应到我的工作报表开发来说，最后的查询结果无非就是把多个结果集通过多个字段关联起来成一个整体，所以必须得分析要通过哪些个字段来关联，怎么关联更好，这样开发报表，才能事半功倍，游刃有余。

 

 

 

2012年9月13：

今天一大早来，接到一封转发的邮件，是一个项目的报表，选择查询一个月的数据就很慢，几分钟都出不来。

于是登录远程的生产服务器，连接数据库，把sql语句加上日期限制条件，运行了一下，确实太慢，取消了查询。

由于语句比较长，所以接下来是一段一段运行，发现其中有3段都是对相同的2个表的查询，只是查询条件不同，不过有一个查询特别慢，于是我看了一下这两个表，都是有主键的，关联之后总的数据大概200w条，子表在关联主表时，关联字段上没有建立索引，这可能是个问题，但应该不太可能导致类似的查询，有的非常快有的很慢。想到可能是统计信息不准确导致查询比较慢，所以更新了这2个表的统计信息，再次查询，发现速度非常快。

接下来还有一个报表也是非常慢，通过逐段运行语句，发现有是2个表的关联的查询非常慢，查了一下是2个日结的表，一个主表1200w条，子表有6200w条，晕，这么大的表都没有主键，也没有建立聚集索引，完全是堆表，只是建立了多个索引，怪不得这么慢，查了动态性能视图，发现建的索引中有好几个的用户seek次数，用户scan次数都很低，说明这些索引基本上建了没派上用场，倒是用户update好多次。所以，首先把用处不大的几个索引删掉了，接下来按照查询条件、关联字段、返回字段，给2个表建立了索引。之后再查报表，只需要5秒，但是觉得这2个表还是需要考虑根据多个id建立聚集索引，同时对超大的表可以改为分区表，这样查询速度应该会更快的，不过这个工作应该在系统使用低峰的时候进行，建立聚集索引的同时指定分区架构就行了。

 

 

2012年9月24：

上周五接到一个报表的优化任务，说这个报表的cpu占用率太高了，于是在服务器上运行了这个报表的sql语句，CPU的使用率一下子从百分之几上升到100%，不过还好只持续了5秒左右，语句已经使用了临时表，性能相对稳定，速度还可以，并不慢。查看了一下语句，觉得很难从语句级别进行优化了，所以把注意力放在语句中使用的几个表以及这些表的索引，发现有索引建的并不合理，于是重建了索引，再次运行速度更快，但是cpu使用率还是比较高，通过在语句中使用查询提示来降低了并行度，cpu的使用率正常多了，不过还是有cpu占用率100%的情况。

今天早上继续优化这个报表，查看了执行计划，发现对其中一个表的索引查找占用整个执行计划80%的开销，这个表已经建立了索引，而且这个索引看着也没错，仔细一看发现，这个索引中有3个字段，但是字段的前后顺序有问题，用在where子句中的那个字段是索引的最后一个字段，这是有问题的。于是重建了这个索引，把这个字段放到第一个，再次执行查询，原来得好几秒，现在1秒都不到就出来了，同样是索引查找，效率有本质的区别，这让我再次注意到一定要细心，细微之处才能显示真本事。

另外，尝试把这个报表中语句的中间结果都放到临时表中，发现执行速度并没有变快，反而速度有所下降。所以最后还是决定不改成都用临时表了。

 

 

2012年10月25：

前段时间，在一个服务器的2个数据库中的日结存储过程经常出问题，导致日结表中的数据缺失，对其中一个数据库通过新建索引和设置隔离级别为读取未提交数据，至少保证了在日结时尽量不会遇到锁表的问题。而另一个数据库虽然日结成功，但是日结时间仍然比较长。经过运维的监控发现，这台数据库服务器的平均磁盘等待队列经常达到1000多，磁盘有问题，另外，这个服务器经常性的cpu接近100%，应该也是由于磁盘的性能问题导致的，由于磁盘的问题，使得查询优化器更多的使用并行的查询计划，导致在查询报表的时候一下子所有cpu使用率都接近100%，内存的使用比较平稳。另外，其中一个库的表数据量接近1亿条，但是任然没有建立分区表，而且这2个数据库文件放在同一块硬盘上，文件大小接近200G，怪不得这么慢。

 

这两天一直在优化一个报表，查一天的数据需要5-6分钟，我看了代码，查询中用到了2个数据库中的表，在代码中使用了一个视图xx，然后这个视图xx又调用了另一个视图xxx，这2个视图中多次用了union all来把不同表中的数据合并在一起，那么在sql代码中也是大量使用了union all 来合并2个数据库的计算结果集，其中的几个大表有几百万到几千万的数据量，于是我开始从sql语句级别进行优化，适当简化代码，并且把明细部分的结果插入到临时表中，再用临时表的结果集计算汇总的值，最后再把明细和汇总的union all，当然还是很慢，于是按照语句的写法建立了9个索引，查询一天的1秒不到就来了，就应该这么快，索引的效果相当好，不过有一点比较奇怪的是我发现其中有一个数据库中的一个表有86个索引，其中大部分索引的user_seeks,user_scans,user_lookups全是0，这些索引全是没有用的索引，应该全部删除，更有问题的是这个表才2000多条数据，不知道为什么要建立那么多的索引，其实不用建索引速度应该非常快。

 

 

 

2013年02月25：

早上同事说刷新缓存报错，具体就是在执行一个拜访计划的存储过程时，在执行了半个小时后，报了一个数值无法转化为datetime类型的错误。首先，查看了存储过程，看了主要的过程就是通过一个复杂的select，然后把结果集插入到一个目标表，整个过程还是很清晰的，但是看不懂select语句的意义，其中里面引用了多个表，还有视图，通过把语句层层分解，定位了出现问题的表和相关字段，这个字段是一个numeric类型，但语句中把这个值转化成datetime类型，这个字段中的某些行无法转化成日期类型，所以导致报错，应该是客户提供的数据有问题导致。

 

2013年02月28

早上同事收到一份客户的邮件，说昨天晚上在执行日结存储过程时报了一个临时表空间不足的错误。

由于这个客户用的数据库是Oracle，而且没有登录到远程数据库服务器的权限，所以同事和我讨论了这个问题可能的原因。

这个日结存储过程是在一个java的定时任务里调用的，和其他项目的日结存储过程都一样，先delete，然后是一个简单的select...insert操作，我问了同事有没有在java中提交事务，或者在存储过程中提交事务，他说没有，在网上查了一下，说这种情况下，一般都会在执行完任务后才会提交事务。

基于上面的这些，我认为这个错误不太可能是临时表空间太小的问题，因为这个日结存储过程对多个表的数据进行日结，既没有表关联，也没排序，只是会先delete，然后insert。相反，很有可能是undo表空间太小不足导致的，由于涉及到大量的delete，insert，导致undo表空间需要保存大量的undo数据，而存储过程中又没有及时的commit，那么undo表空间中的undo数据不能及时释放，而客户对undo表空间限制了最大的容量，不能自动扩展，最后导致这个错误。

接下来同事对存储过程做了一些修改，增加了commit语句，之后再也没有报错类似的错误。

 

 

2013年5月23日

今天同事在运行SQL语句是报错：varchar转化为numeric时溢出。

一开始通过isnumeric函数来判断出问题的那一列，但发现这一列的所有值都为数值，但这样就不会出现转化溢出的问题，肯定还有其他的问题。

接下来，通过把这一列值进行去重，并按照列所占字符长度进行排序，发现这一列中有‘.5’和‘.’，经过验证发现cast('.' as numeric)时会报错，但isnumeric('.')返回1，也就是isnumeric函数先转化，然后检查是否是数值，而这个转化与cast函数的转化规则是不同的，导致2个函数对'.'会有不同的结论。

 

2013年5月24日

今天修改了一个存储过程，原本以为肯定没什么问题，但没想到修改后，存储过程执行后的结果，竟然是错误的。

花了半个多小时，总算找到了问题所在。

通过这个问题，让我想到做事一定要仔细，要认真，不要一遇到很熟悉的问题就放松马虎，觉得这个问题很简单，就没去看原来的代码，也没有验证这样修改是否正确。

所以，不管是简单的问题还是复杂的问题，不管是遇到过的还是没有遇到过的问题，都要认真对待。

 

 

2.13年6月6日

最近有一个问题：

当某个大表和某个小表关联，
而查询条件作用到小表，这时可以通过使用索引查找来加快查询速度。

但如果现在有2个大表和一个小表关联，
而查询条件还是作用到小表，这个时候往往就需要通过表提示强制使用索引。