1、SQL SERVER 2005的性能工具中有SQL Server Profiler和数据库引擎优化顾问,极好的东东,必须熟练使用。
2、查询SQL语句时打开“显示估计的执行计划”,分析每个步骤的情况
3、初级做法,在CPU占用率高的时候,打开SQL Server Profiler运行,将跑下来的数据存到文件中,然后打开数据库引擎优化顾问调用那个文件进行分析,由SQL SERVER提供索引优化建议。采纳它的INDEX索引优化部分。
4、但上面的做法经常不会跑出你所需要的,在最近的优化过程中CPU占用率极高,但根本提不出我需要的优化建议,特别是有些语句是在存储过程中并且多表联立。这时就需要用中级做法来定位占用CPU高的语句。
5、还是运行SQL Server Profiler,将运行结果保存到某个库的新表中(随便起个名字系统会自己建)。让它运行一段时间,然后可以用
select top 100 * from test where textdata is not null order by duration desc
这个可以选出运行时间长的语句,在ORDER BY 中可以替换成CPU、READS,来选出CPU占用时间长和读数据过多的语句。
定位出问题的语句之后就可以具体分析了。有些语句在执行计划中很明显可以看出问题所在。
常见的有没有建索引或索引建立不合理,会出现table scan或index scan,凡是看到SCAN,就意味着会做全表或全索引扫描,这是带来的必然是读次数过多。我们期望看到的是seek或键查找。
6、怎么看SQL语句执行的计划很有讲究,初学者会过于关注里面显示的开销比例,而实际上这个有时会误导。我在实际优化过程中就被发现,一个index scan的执行项开销只占25%,另一个键查找的开销占50%,而键查找部分根本没有可优化的,SEEK谓词就是ID=XXX这个建立在主键上的查找。而仔细分析可以看到,后者CPU开销0.00015,I/O开销0.0013。而前者呢,CPU开销1.4xxxx,I/O开销也远大于后者。因此,优化重点应该放在前者。
7、如何优化单个部分,一个复杂的SQL语句,SQL SERVER会很聪明地重组WHERE后的语句,试图匹配索引。选中带优化的步骤,选择旁边的‘属性”,再选择其中的“谓词”,将其中部分复制下来,这部分就是分解后的WHERE 语句,然后在查询界面中select * from 表 where 刚才复制下来的“谓词”。这个就是需要优化的部分,既然已经走到这一步了,大部分人应该能手动建立索引了,因为这里的WHERE语句比之前的肯定简单不少。(在我项目中原始SELECT语句的WHERE部分有10个条件组合,涉及6个字段,提取出来要优化的部分就4个条件,涉及到3个字段。新的索引建立后,CPU占用率一下子就降低了,而且新建立的索引涉及的字段属于不常UPDATE的部分,频繁的读写操作不会影响UPDATE的效率)
8、以上就是优化的思路,最后提一些优化过程或是系统设计时中需要注意的问题。
A、尽量避免用select * from xxx where abc like '%xxx'类型的模糊查询,因为%在前面的话是无法利用到索引,必然会引起全量SCAN操作。应该找寻替代方式或用前置条件语句把like查找之前的行数减到最低。
B、尽量避免对大表数据进行select top n * from xxx where xxxx order by newid()的取随机记录的操作。newid()操作会读全量数据后再排序。也会占用大量CPU和读操作。可以考虑用RAND()函数来实现,这方面我还在研究中,对于整表操作比较好弄,比如id>=(select max(id) from table)*rand()。但如果取局部数据的随机记录还需要思量。
C、在SQL Server Profiler记录中会看到Audit Logout会占用大量CPU和读写等操作。查了一些资料称是某个链接在某次连接过程中执行SQL语句产生的总数,不用过于担心。看下来的确似乎这样,很多Audit Logout的CPU和IO消耗量和之前优化的语句基本一致。所以在第5点我提的SQL语句用textdata is not null条件把Audit Logout给隐去。
D、两个不同字段OR语句会导致全表扫描。例如 where m=1 or n=1。如果建立一个索引是m和n,同样会引起scan,解决方法是给m和n分别建立索引。测试12万条数据的表,索引建立错误的情况下IO开销高达 10.xxx,分别建立索引后,全部变成0.003,这个反差是非常巨大的。虽然会引起INSERT操作的性能问题,但毕竟大部分瓶颈在SELECT的读操作上。
E、索引查找(Index Seek)和索引扫描(Index Scan),我们需要的是前者,而引起后者的原因通常是某个索引里的字段多余要查找的,例如索引建立在A和B两个字段,而我们只要查找A,则会导致 INDEX SCAN。建议针对单独的A建立索引,以形成索引查找。
F、对于小表不建议建立索引,特别是几百的数据量,只有上千上万级别的数据建立索引才有效果。
数据库优化是很深的学问,在数据库设计时就应该注意,特别是最后提到的A、B两点,尽可能在设计初期避免。
SQL SERVER 的SQL语句优化方式小结
2019-11-13 14:52mssql教程网 Sql Server
千辛万苦,终于把数据库服务器的CPU从超过50%(开5个程序线程)乃至100%(开10个程序线程)降低到了5%。摸索到了一些门道,总结一下
延伸 · 阅读
- 2022-03-09sql语句中union的用法与踩坑记录
- 2022-03-09SQL利用游标遍历日期查询的过程详解
- 2022-03-08SQL删除重复的电子邮箱力扣题目解答流程
- 2022-03-06C#连接SQL Server的实现方法
- 2022-03-04SQL Server2012无法连接到服务器的解决方法
- 2022-03-03SQL行转列与列转行
- Sql Server
SQL查询的底层运行原理深入分析
这篇文章主要给大家介绍了关于SQL查询的底层运行原理,文中通过实例代码结束的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友...
- Sql Server
sql的临时表使用小结
这篇文章主要介绍了sql的临时表使用小结,需要的朋友可以参考下 ...
- Sql Server
针对Sqlserver大数据量插入速度慢或丢失数据的解决方法
这篇文章主要介绍了针对Sqlserver大数据量插入速度慢或丢失数据的解决方法,很有实用价值,需要的朋友可以参考下 ...
- Sql Server
SqlServer将查询结果转换为XML和JSON
这篇文章主要介绍了SqlServer将查询结果转换为XML和JSON的相关资料,需要的朋友可以参考下 ...
- Sql Server
SQL server 2008 数据安全(备份和恢复数据库)
备份和恢复数据库对于数据库管理员来说是保证数据安全性的一项重要工作。SQL server 2008提供了高性能的备份和恢复功能,可以实现多种方式的数据库备份...
- Sql Server
SQL server 随机数函数
在SQL server中,有个随机函数rand(),有不少新手可能不知道存在这个函数,现在我就把这个函数的一些随机数生成技巧写出来,这是面向菜鸟的,老鸟请不要...
- Sql Server
SQL查询入门(上篇) 推荐收藏
SQL语言是一门简单易学却又功能强大的语言,它能让你快速上手并写出比较复杂的查询语句。 ...
- Sql Server
SQLServer 数据库故障修复顶级技巧之一
SQL Server 2005 和 2008 有几个关于高可用性的选项,如日志传输、副本和数据库镜像。 ...