首页 > 代码库 > SQLServer 调优基础
SQLServer 调优基础
当确定了应用性能问题可以归结到某一个，或者几个耗时资源的语句后，对这些语句进行调优，就是数据库管理员或者数据库应用程序开发者当仁不让的职责了。语句调优是和数据库打交道的必备基本功之一。
当你面对一个“有问题”的语句时，应该怎么分析它的问题所在，最后达到优化语句的目的呢？首先要想一想，“有问题”的语句“问题”究竟在那里？也就是说，你要优化的目标是什么。常见的需求有:
1) 语句需要访问大量的数据页面，造成内在压力、磁盘繁忙等。
对于这类问题，所关心的是为什么语句要执行要访问这么多数据页面？是语句的结果集本身就比较大;还是SQL SERVER没有办法有效地seek，而是像大炮打苍蝇一样从大量的原始数据里找出需要返回的结果；还是因为数据页面里有很多碎片，导致SQL SERVER读了很多页面，但是每个页面里的数据量不多。这些都是要考虑的因素。
2) 在内存没有压力的前提下（语句所访问的页面都事先缓存在内存里），语句运行的时间还是很长。
语句的运行时间一般会主要花在这3步上：语句编译、语句执行和结果集返回。结果集返回的速度和SQL SERVER自身没有太大关系，所以一般不会在语句调优的时候来考虑。语句调优时要搞清楚编译和执行各花了多少时间，哪 一段时间有优化的空间，以及怎么来优化。
3) 单个语句执行时间可以接受，但是苦CPU使用量比较大，多个语句并发执行会造成SQL SERVER CPU高。
有些语句单句执行可能一两秒钟就能执行完毕，对用户来讲还在可接受的范围。但是它的CPU间可能也是在一两秒，甚至更长。如果同时有十几个用户在跑同样的语句，SQL SERVER 就会满负荷了。语句的CPU时间也分编译阶段和执行阶段。优化者要先搞清楚这两个阶段各用了多少CPU资源，然后再看看有没有优化降低CPU使用量的可能。
4) 语句单独执行看不出有大问题，但是并发执行就容易遇到阻塞和死锁。
这个也是语句调优的一个重要任务。很多语句执行速度很快，使用资源量SQL SERVER也能够承受，但是就是容易引起阻塞和死锁。这种现象往往是由于应用在某个表或者索引上的并发度特别高，而问题语句申请的锁数量比较大造成的。当然有时候可以使用Query Hint 来强制 SQL SERVER使用粒度比较小的锁。但是这往往不是最好的解决办法，也可能解决不了问题。最理想的方法，是通过调整语句运行方式，引导它申请尽可能少的、粒度尽可能小的锁。这里也要做语句调优。
在做这些调优的时候，首先要对目标语句做估算，看看它优化的空间有多大。有些语句本身比较简单，可以通过调整索引的方法迅速提高性能，这样的调优是很值得做的。有些语句非常复杂，或者返回的结果集很大，通过调整SQL SERVER这里的设置，提高性能的空间往往不大。这个时候就要考虑，语句本身是不是能够换一种方法实现。很多时候改一下语句，把一条大的语句拆分成若干条小的语句，或者去掉一些不必要的逻辑，会达到事半功倍的效果
在谈论如何做语句调优的具体方法之前，必须先介绍一下最必需的背景知识。不了解这些知识 ，做语句调优就只能基本靠猜。所需要的背景知识主要包括理解索引和统计信息，理解什么是统计和重编译，并且能够基本读懂语句的执行计划。以下为例子，借助MS示例数据库AdventureWordks来介绍。
--测试用例USE AdventureWorks2008GOIF OBJECT_ID (‘SalesOrderHeader_TEST‘) IS NOT NULL DROP TABLE dbo.SalesOrderHeader_TESTGOIF OBJECT_ID (‘dbo.SalesOrderDetail_TEST‘) IS NOT NULL DROP TABLE dbo.SalesOrderDetail_TESTGO-- (31465 行受影响)SELECT * INTO dbo.SalesOrderHeader_TESTFROM Sales.SalesOrderHeader-- (121317 行受影响)SELECT * INTO dbo.SalesOrderDetail_TESTFROM Sales.SalesOrderDetail-- 建立聚集索引CREATE CLUSTERED INDEX SalesOrderHeader_TEST_CL ON dbo.SalesOrderHeader_TEST(SalesOrderID)-- 建立非聚集索引CREATE NONCLUSTERED INDEX SalesOrderDetail_TEST_NCL ON dbo.SalesOrderDetail_test(SalesOrderID)go
SalesOrderHeader_TEST 里存放的是每一张订单的头信息，包括订单创建日期、客户编号、合同编号、销售员编号等，每个订单都有一个单独的订单号。在订单号这个字段上，有一个聚集索引。
SalesOrderDetail_TEST 里存放的是订单的详细内容。一张订单可以销售多个产品给同一个客户，所以SalesOrderHeader_TEST 和SalesOrderDetail_TEST是一对多的关系。每每详细内容包括它所属的订单编号，它自己在表格里的唯一编号(SalesOrderDetailID)、产品编号、单价、以及销售数量等。在这里，先只在SalesOrderDetailID 上建立一个非聚集索引。
按照AdventureWorks里原先的数据， header_test 里面有3万多条订单信息，detail里有12万多条订单详细记录，基本上一条订单有3-5条详细记录。这是一个正常的分布。
下面再在 header_test 里面加入9条订单记录，他们的编号是从75124 到75132这是9张特殊的订单，每张有12万多条详细记录。也就是说 deatil_test里会有90%的数据属于这9张订单。
declare @i int set @i = 1while @i < 10 begin INSERT INTO [AdventureWorks2008].[dbo].[SalesOrderHeader_TEST] ([RevisionNumber] ,[OrderDate] ,[DueDate] ,[ShipDate] ,[Status] ,[OnlineOrderFlag] ,[SalesOrderNumber] ,[PurchaseOrderNumber] ,[AccountNumber] ,[CustomerID] ,[SalesPersonID] ,[TerritoryID] ,[BillToAddressID] ,[ShipToAddressID] ,[ShipMethodID] ,[CreditCardID] ,[CreditCardApprovalCode] ,[CurrencyRateID] ,[SubTotal] ,[TaxAmt] ,[Freight] ,[TotalDue] ,[Comment] ,[rowguid] ,[ModifiedDate])SELECT [RevisionNumber] ,[OrderDate] ,[DueDate] ,[ShipDate] ,[Status] ,[OnlineOrderFlag] ,[SalesOrderNumber] ,[PurchaseOrderNumber] ,[AccountNumber] ,[CustomerID] ,[SalesPersonID] ,[TerritoryID] ,[BillToAddressID] ,[ShipToAddressID] ,[ShipMethodID] ,[CreditCardID] ,[CreditCardApprovalCode] ,[CurrencyRateID] ,[SubTotal] ,[TaxAmt] ,[Freight] ,[TotalDue] ,[Comment] ,[rowguid] ,[ModifiedDate] FROM [SalesOrderHeader_TEST] WHERE SalesOrderID = 75123INSERT INTO [AdventureWorks2008].[dbo].[SalesOrderDetail_TEST] ([SalesOrderID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] ,[ModifiedDate]) SELECT 75123 + @i ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] ,GETDATE() FROM Sales.SalesOrderDetailSET @i = @i + 1 END GO