sql server 优化配置 sql2008优化

1. 前言

SQL是程序设计人员与数据库进行沟通的标准语言，在数据库应用程序中，使用最多的访问数据库的方法就是SQL语言。SQL性能的调整涉及到SQLServer 2008的方方面面，优化器的选择、内存参数的设定、SQL语句的写法等。本文主要从SQL语句的优化方面进行阐述，给出了一些可行的调整数据库应用性能的策略与方法，并以SQLServer 2008为平台对列举规则给予了验证。

2. 实验环境

 

• 硬件环境：Inter(R) Core(TM)2 Duo CPU P7450 @2.12GHz 、2G内存。
• 操作系统：windows7操作系统，
• SQL Server 2008版本：Microsoft SQLServer 2008
• 所用表结构：

• Consume(ConsumeID,Cardno,ShopId,GoodsId,Amount,ConsumeDate)
• CreditCard(Cardno,OwnerName,IDCard,MaxConsume,Address,PostCode,Phone,ConsumeAmount) 
• Goods(GoodId,GoodsName,TypeId,ShopId,Price)
• Shop(ShopId,ShopName)

• 记录数：Consume:686302    CreditCard:9    Goods:32    Shop:4

本实验是在SQL Server Management Studio平台上执行的。且运行时均利用 DBCC FREEPROCCACHE  DBCCDROPCLEANBUFFERS  DBCC FREESYSTEMCACHE ('ALL' )这三条语句清理了缓冲区，从而得出相对公平的执行时间。

另外，每段实验代码均放入如下代码中间执行，这样可以得到SQL语句执行所用的时间，获取执行时间的代码如下：

 

[sql] view plaincopy

1. declare @begin_date datetime  
2. declare @end_date datetime  
3. select @begin_date = getdate()  
4. <实验SQL语句写在此处>  
5. select @end_date = getdate()  
6. select datediff(ms,@begin_date,@end_date) as '用时/毫秒'  

 

3. SQL调优

3.1 优化SELECT语句

如果查询数据时涉及多个表，则通常需要使用连接查询。SQL Server 2008支持内连接、外连接和交叉连接等连接查询方式。

3.1.1 连接查询的优化

子查询替换连接表

低效语句：

 

[sql] 
1. select c.cardno,cc.IDCard,c.amount,c.consumedate  
2. from CreditCard cc left join Consume c  
3. on cc.Cardno = c.Cardno  
4. where c.Cardno = '9555xxxxxxx1'

 

高效语句：

 

[sql] 
1. select @begin_date = getdate()  
2. select c.cardno,cc.IDCard,c.amount,c.consumedate  
3. from (select * from CreditCard where Cardno = '9555xxxxxxx1') ccleft join Consume c  
4. on cc.Cardno = c.Cardno

 

3.1.2控制查询的行和列

优化SELECT语句，首先需要考虑的是减少返回结果集中的数据量。在SELECT语句中，应该尽量可能不返回多余的数据，这需要从行和列两方面进行优化。

减少结果集中列的数量：

低效语句：

 

[sql]
1. select  *  
2. from CreditCard cc left joinConsume c  
3. on cc.Cardno = c.Cardno  
4. where c.Cardno = '9555xxxxxxx1'

 

高效语句：

 

[sql]
1. select @begin_date = getdate()  
2. selectc.cardno,cc.IDCard,c.amount,c.consumedate  
3. from CreditCard cc left join Consume c  
4. on cc.Cardno = c.Cardno  
5. where c.Cardno = '9555xxxxxxx1'

 

3.1.3 慎用DISTINCT关键字

DISTINCT关键字一般用来于字段很少的表，如果在SELECT语句中查询的字段很多，则使用DISTINCI关键字反而会大大降低查询的效率。

低效语句（使用DISTINCT）：

 

[sql] 
1. select distinctc.cardno,cc.OwnerName,cc.IDCard,s.shopname,  
2. g.goodsname,c.amount,c.consumedate  
3. from Consume c inner join CreditCard cc  
4. on cc.Cardno = c.Cardno  
5. inner join Shop s on c.ShopId=s.ShopId  
6. inner join Goods g onc.GoodsId=g.GoodsId

 

高效语句（不使用DISTINCT）：

 

[sql] 
1. selectc.cardno,cc.OwnerName,cc.IDCard,s.shopname,  
2. g.goodsname,c.amount,c.consumedate  
3. from Consume c inner join CreditCard cc  
4. on cc.Cardno = c.Cardno  
5. inner join Shop s on c.ShopId=s.ShopId  
6. inner join Goods g onc.GoodsId=g.GoodsId

 

3.1.4 判断表中是否存在记录

有些人喜欢使用COUNT(*)来判断表中是否存在记录，例如

 

[sql] 
1. select count(*) from consume

这种方法要统计表中所有记录的总数量，因此执行效率比较低。可以在select语句中使用TOP1返回表中的第一条记录来判断表是否存在记录：

低效语句：

 

[sql]
1. select COUNT(*) from Consume

高效语句：

 

[sql] 
1. select top 1 ConsumeID from Consume

 

3.2 对大批量插入、修改和删除数据操作的优化

3.2.1 使用SQL脚本模拟实时批量插入数据

[sql] 
1. -- 使用Credit数据库  
2. USE Credit  
3. GO  
4. -- 创建临时表#test  
5. CREATE TABLE #test  
6. (  
7. int,  
8. name varchar(100),  
9.     createdate datetime,  
10. )  
11. GO   
12. DECLARE @i as INT  
13. declare @strsql as varchar(8000)   
14. SET @i = 0  
15. -- 循环插入10万条记录  
16. WHILE @i < 100000  
17. BEGIN  
18. SET @strsql = 'INSERT INTO #test VALUES(' + convert(varchar(100), @i) + ', ''test'', ''' + convert(varchar(50), getdate()) + ''')' --VALUES(@i, 'test', getdate())  
19. exec(@strsql)  
20. SET @i = @i + 1  
21. END

 

下面我们对批量插入数据进行优化，可以使用WAITFOR DELAY语句进行休息，例如，让数据库引擎休息100ms的代码如下：

 

[sql] 
1. WQITFOR DELAY ’00:00:00:100’

 

[sql] 
1. -- 使用Credit数据库  
2. USE Credit  
3. GO  
4. -- 创建临时表#test  
5. CREATE TABLE #test  
6. (  
7. int,  
8. name varchar(100),  
9.     createdate datetime,  
10. )  
11. GO   
12. DECLARE @i as INT  
13. declare @strsql as varchar(8000)   
14. SET @i = 0  
15. -- 循环插入10万条记录  
16. WHILE @i < 100000  
17. BEGIN  
18. SET @strsql = 'INSERT INTO #test VALUES(' + convert(varchar(100), @i) + ', ''test'', ''' + convert(varchar(50), getdate()) + ''')' --VALUES(@i, 'test', getdate())  
19. exec(@strsql)  
20. '00:00:00:20'  
21. SET @i = @i + 1  
22. END

1.  

CPU的占用率已经降到5%左右，虽然执行的时间增加了，但是这完全满足系统正常运行的需要。

3.2.2 优化修改和删除语句

如果一条UPDATE语句或者DELETE语句设计的记录过多，则执行语句的时间会很长，在执行语句的过程中，数据库服务器的CPU利用率会很高，从而影响其他用户对数据库的访问效率。当对大量数据进行更新和删除操作时，可以根据用户的具体情况来决定操作的方式。

a)如果在非工作时间执行大量更新或者删除操作，则应该尽可能地在一条语句中更新更多的记录，这是最快的操作方式。

b)如果在工作时间执行大量更行或删除操作，则应该根据实际情况将UPDATE语句或者DELETE语句拆分成多条语句，每执行一条语句休息一段时间。

下面做一个对比实验，执行下面的UPDATE语句，将表Consume中所有记录的Amount数量增加10%。

 

[sql] 
1. update Consume set Amount = Amount * 1.1 whereConsumeID>0

 

 

CPU的使用率一直维持在60%左右：

 

如果希望在执行更新操作时尽可能降低数据库服务器的CPU使用率，不影响其他用户对数据库的访问，可以使用下面的SQL语句：

 

[sql] 
1. update Consume set Amount = Amount*1.1where Amount<200  
2. waitfor delay '00:00:00:100'  
3. update Consume set Amount = Amount*1.1where Amount>=200 and Amount<500  
4. waitfor delay '00:00:00:100'  
5. update Consume set Amount = Amount*1.1where Amount>=1000 and Amount<2000  
6. update Consume set Amount = Amount*1.1where Amount>2000  
7. waitfor delay '00:00:00:100'

 

 

4. 总结

在SQL Server 2008中性能的优化还有许多方面，如存储引擎优化、处理器管理优化、视图的优化等等。以上实验只是针对SQL Server 2008中的SQL语句进行调优。本文中的实验所测得的SQL执行时间会有微小误差，这是不能避免的。更进一步的数据库优化操作有待于进一步的学习和总结。