(转)SQLServer2008的优化

  

 注： 本文转自 夜里的烟 代码上的烟灰的CNBlog。

 

在SQL Server的上下文中，优化指充分利用数据库引擎最小化查询响应时间、资源使用和硬件压力。优化的最大挑战之一是知道在哪里投入精力。应该将可以在最大范围内作出最大改进的区域作为努力的目标。除了设计问题以外，最有可能改进的地方是应用程序使用的过程和查询。遗憾的是，许多数据库提供了非常少的索引，并且没有考虑数据库不断增长对查询性能的影响。

硬件优化

　　硬件设计时的目标就是最大化数据吞吐量，吞吐量指的是单位时间内通过处理管道的数据量。OLTP和OLAP的数据库特性不同，因此不要在统一服务器混合使用不同类型的数据库。许多DBA并不是硬件专家，因此他们喜欢通过购买硬件解决问题，这样做会有如下弊端：硬件很贵；只能暂时解决问题；潜在收益低于通过优化解决问题。因此，在面对性能问题时，明智的做法是首先考虑优化，而将添加硬件作为最后的选择。虽然是这样，有时候还是不得不通过硬件升级来优化效率

　　CPU选择：在选择CPU时L2缓存的容量对整体性能有重大影响，较大的L2缓存将使数据避开系统总线并减少内存转移。对于SQL Server来说，将L2缓存改从512KB升级到2M会使得CPU性能提升20%-30%。

　　超线程：大部分情况下应禁用超线程，因为在处理器之间切换线程上下文而不管这些处理器是物理的还是逻辑的，将会导致SQL Server减慢

　　内存：应获得尽可能多的内存

　　存储选项：磁盘IO瓶颈十分常见，因此从磁盘检索数据是处理过程中最慢的操作。如果严格遵循Microsoft的指导原则，对于典型安装，将需要提供12以上的单独存储阵列来支持各种日志、分区、文件组、索引和数据文件。这一推荐配置可以提供很好的磁盘IO性能。通常通过将日志文件放在自己的RAID1或RAID1+0存储阵列上，可以获得最大的性能。同时将tempdb放在自己的存储卷中也能够显著提升性能。写缓存是由驱动器和控制器使用的一项技术，但是支持写缓存的大部分驱动器依赖于没有电池备份的电容机制，为了保证数据完整性，应对SQL Server使用的任何驱动器禁用磁盘缓存。相反，缓存控制器通常为其写缓存提供支持电池支持。另外一项新技术就是使用固体驱动器。

　　网络设计：在许多情况下一个100Mbs/1Gbs网卡就能满足需求，但是要仔细确认速度和双工设置。这些网卡经常会错误地自动感知，从而损害了网络性能。可以通过手动配置网卡来防止此类问题发生。最后，要确保网络用户使用交换机而不是集线器。

　　虚拟化SQL Server：略

设计考虑

　　有效的数据库设计首先要有有效的实现方案设计。所以，值得花时间提前对实现方案进行规划，而不是等到在生产过程中出现问题时再进行应对。

　　数据库恢复模式：对于每个数据库来说，首先要做出的决策之一就是选择恢复模式：简单，大容量日志和完整模式。如果您的数据库通常使用完整恢复模式，而又可以牺牲时间点回复的一些灵活性的话，可以切换为大容量日志回复模式来提高特定的大容量曹祖哦的性能，包括SELECT INTO,BCP,BULK INSERT,CREATE INDEX和更改TEXT和IMAGE字段，可以通过如下语句实现：

USE AdventureWorks2008
GO

ALTER DATABASE AdventureWorks2008 SET RECOVERY BULK_LOGGED
GO

CREATE NONCLUSTERED INDEX IX_ModifiedDate ON Person.BusinessEntity(ModifiedDate)
GO

ALTER DATABASE AdventureWorks2008 SET RECOVERY FULL
GO

SELECT [name],state_desc,recovery_model_desc FROM sys.databases

　　设计有效率的表的指导原则：规范化第三范式；为性能反规范化；总是定义主键和聚集键；强制引用完整性；限制每个表的列数；尽可能使用最窄的数据类型；对非常大的表考虑水平分区；使用命名约定。

　　在选择主键时需要考虑的：主键不能更改；数据类型应是整型或短的，固定宽度的字符；在嵌入式业务代码中避免使用智能键；对于简单的查找表，考虑使用自然键；对于分成的父子结构，考虑使用代理键；必须仍然支持自然键搜索；尽量最少化复合主键的使用

　　聚集键的选择应尽可能符合下列特点：搞选择性；很少变更；不断增加的值；较窄；一般按范围筛选；集成排序或分组；常用于JOIN子句中。考虑上述特点，可以范县对于聚集键选择来说有三个首选项，强烈建议考略下列选项之一：主键；事务日期和外键。许多数据库人员常犯的错误是将GUID作为聚集键，千万不要这么做！首先，GUID是唯一的，不变更的，坏的一方面是它是宽的，随机的，不用于排序或分组并且一般不安范围筛选。这些因素决定了不能将GUID作为据聚集，但是还有一个更大的问题。GUID是随机的这一事实将使他成为聚集键的最差选择。聚集GUID列将导致索引范围几乎完全碎片化，因为SQL Server不知道如何为随机值建立有效范围。随着记录的增加，当SQL Server移动数据为新值腾出空间时会发生额外的页拆分。另外SQL Server将聚集键包含为每个非聚集索引的一个隐藏部分，因此，不仅聚集索引会碎片化，非聚集索引也会碎片化。在处理GUID是，更好的选择是为表添加IDENTITY，使其作为主键和索引键，然后在GUID列上创建唯一约束，允许它充当另一唯一键。

　　有关一般索引的指导原则要比主键或聚集索引的宽松些，通常如果能将索选择的索引归为下列一个或多个类别，就可以获得最大的性能优势：窄索引；可选择性；覆盖索引；唯一约束。

　　索引视图和筛选索引：索引视图和筛选索引是更高级的索引技术，可以用来更进一步改进和优化数据库。索引视图实际上是一个有着聚集索引的普通视图；筛选索引是一个基于简单的比较逻辑，值应用于数据子集的表索引。相比于索引视图，他们更可能被查询优化器用于比较宽范围的查询。在所有可能性中，筛选索引为在可空列上强制唯一项提供了一个直接而简单的解决方案。素以应该既可能使用筛选索引而不是索引视图，筛选索引必须满足一下条件：单个表；无计算列；简单的比较逻辑。下面的例子中，假定需要创建一个用于唯一标识某客户的别名，但是不是每个客户都有别名。有三种方法可以用于强制实施这一条件，触发器，索引视图和筛选索引，我们对每个选项进行评估并确定最佳选项：

USE AdventureWorks2008
GO
ALTER TABLE Sales.Customer ADD Alias varchar(10) null
UPDATE Sales.Customer SET  Alias='NJSP' WHERE CustomerID=1
UPDATE Sales.Customer SET Alias='DCJS' WHERE CustomerID=2

--Option1, TRIGGER IMPLEMENTATION
CREATE TRIGGER Sales.trigUniqueAlias ON Sales.Customer FRO INSERT,UPDATE AS
SET NOCOUNT ON;
IF (SELECT count(CustomerID) FROM inserted WHERE exists (SELECT CustomerID FROM Sales.Customer sc WHERE sc.Alias=inserted.Alias AND sc.CustomerID!=inserted.CustomerID))>0
BEGIN;
ROLLBACK TRANSACTION;
END;
GO
--DROP TRIGGER Sales.trigUniqueAlias
--GO

--Option 2, INDEXED VIEW IMPLEMENTATION
CREATE VIEW Sales.vCustomerAlias WITH SCHEMABINDING AS
SELECT CustomerID, Alias FROM Sales.Customer WHERE (Alias IS NOT NULL);
GO
CREATE UNIQUE CLUSTERED INDEX CK_vCustomerAlias ON Sales.vCustomerAlias(Alias);
GO
--DROP VIEW Sales.vCustomerAlias
--GO

--OPTION 3, FILTERED INDEX IMPLEMENTATION
CREATE UNIQUE NONCLUSTERED INDEX FICustomerAlias ON Sales.Customer (Alias) WHERE Alias IS NOT NULL;
GO
--DROP INDEX FICustomerAlias ON Sales.Customer
--GO

　　在性能方面，筛选索引方法明显要好，事实上，筛选索引方法的速度比索引视图方法要快4倍，比用触发器快100倍。

　　为了支持多个并发用户和更新，SQL Server实现了一个复杂的资源锁定，为了尽可能最小化这些类型的锁定和阻塞问题，要尽量做到以下几点：保持事务简短；先准备，然后进行事务处理；使用存储过程；使用规范化架构；将报表与事务分离

　　锁定导致阻塞，阻塞导致超时，而超时就会带来麻烦。在存储过程中就会有超时的漏洞，特别是，如果事务中任何一句话被其他进程阻塞超过30秒，那么客户端将超时，当发生这一情况时，客户端告诉服务器取消它正在做的工作。SQL Server只是放弃命令，所以没有错误来触发CATCH块。避免在超时后留下打开的事务的一个方法是在存储过程中使用SET XACT_ABORT ON命令，默认情况下，SQL Server关闭XACT_ABORT，这意味着由客户端负责在超时后执行清理工作。如果在存储过程的开始部分启用XACT_ABORT，那么如果出现超时，SQL Server将负责清理。

查询优化

　　SQL Server 2008中的查询优化器用于处理一个极其困难的任务，即找到执行查询的最有效方式。SQL Server将只生成一次计划，然后将它保存在内存中的计划缓存中。后续执行将使用该缓存的计划，直至其过期被换出内存或无效。执行计划描述了查询优化器所选择的用于处理查询的步骤顺序。它是数据库管理员用于排除性能较差的查询的主要手段。执行计划有两种形式，估计的和实际的。估计的计划是由查询优化器生成，并被创递给存储引擎进行处理，实际计划是存储引擎实际执行的计划。大多数情况下，估计的计划和实际的计划是一样的。唯一出现不同的时候是当统计信息过期或者存储引擎想在多CPU环境中进行并行处理时。这样的情况下，存储引擎可能忽略建议的计划，而使用自己的计划。

　　在查询优化器提交了估计的执行计划后，存储引擎会将它与计划缓存已有的实际计划相比较。如果在计划缓存中发现了匹配的计划，那么就重用该计划，从而节省了每次处理查询时都要创建新的世纪计划的开销。缓存中的计划不会永远存在；在一般情况下，会慢慢将他们换出内存，一下操作也会导致计划无效或重新编译：低内存；表或架构更改；索引更改；统计信息更改；大量行计数更改；混合DDL和DML；更改SET选项。

　　在内部，每个查询都有一个查询计划，但SQL Server 提供了一些查看计划的不同选项：图形化计划，用于快速了解情况非常有用，但是提供的细节有限；文本计划，难以理解一些，但是包含了更多信息，可以在批处理中添加下列SET语句之一来包含文本计划，SHOWPLAN_ALL,SHOWPLAN_TEXT,STATISTIC_PROFILE；XML计划，非常完整的数据集，描述了执行计划的所有细节，可以使用下列SET语句之一来包含XML计划，SHOWPLAN_XML,STATISTICS_XML。

　　在捕获并显示执行计划后，可以对其进行分析来找到可能改进性能的潜在更改，这些更改可能涉及添加新索引、改变排序选项、分区、使用查询提示或是以新的方式重新编写原查询，分析计划的第一步是寻找常与性能相关的一些特定操作，如果看到下列任何操作，就应该进一步检查他们，确定是否存在问题：聚集索引扫描或表扫描；交叉联接；键查找或RID查找；筛选器；排序。在分析计划时应首先关注开销最高的操作。右击屏幕选择相应的命令，可以看到系统给出的建议的详细信息。

　　查询优化器使用统计信息帮助确定最佳的执行计划，默认情况下，统计信息被设置为自动创建和更新，这是一个可以接受的折中，因为查询性能的收益要大于自动计算统计信息所需的时间。如果选择对指定表禁用自动统计信息功能，还需要定期执行UPDATE STATISTICS命令手动更新统计信息。当对表的内容进行重大修改时，这是必须的。如果想要确保数据库中的每个表的统计信息都是最新的，可以使用sp_updatestats存储过程，该过程对目标数据库中统计信息过期的每个表调用UPDATE STATISTICS。统计信息是否过期可以通过sys.sysindexes服务器视图中的rowmodctr列确定，下面的实例显示了如何为整个AdventureWorks2008数据库更新统计信息：

USE AdventureWorks2008;
GO
EXEC sp_updatestats

管理索引

　　识别缺少的索引：有一些不同的方法可以iyongyu识别可能有潜在好处的索引，第一种方法是使用Missing Index动态管理视图：

-- Use this query to identify missing indexes
SELECT migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
migs.last_user_seek, mid.[statement] 'database_schema_table',
mid.included_columns, mid.equality_columns, mid.inequality_columns,
migs.unique_compiles
FROM sys.dm_db_missing_index_group_stats migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects so on mid.[object_id]=so.[object_id] 
INNER JOIN sys.databases sd on mid.[database_id]=sd.[database_id]
WHERE so.is_ms_shipped=0
-- and sd.name='AdventureWorks2008'  -- uncomment to limit to specific db


-- Sample statements that will result in an index recommendation
USE AdventureWorks2008
GO
-- Run a query over some fields that are not indexed
SELECT sd.SalesOrderID,sd.ProductID,sd.OrderQty
FROM Sales.SalesOrderDetail sd
WHERE sd.LineTotal>1000
GO

　　使用自动统计信息功能识别缺少的索引，如果启用了自动创建和自动更新统计信息功能，那么SQL Server将自动维护在查询表达式中用于限制结果的所有列的统计信息。如果其中任何一列没有索引，那么SQL Server会在其内部索引表中创建一个占位符，并计算该列的统计信息供查询处理器使用。如果SQL Server为未索引的列创建了统计信息，那么很可能是因为该列上的索引将提高性能，下列查询可以显示已为其生成统计信息的未索引列的列表，通过调整WHERE自居，这个查询也会显示所有索引的信息

-- Using automatic statistics to identify missing indexes
SELECT so.name 'object_name', i.index_id,
  isnull(i.type_desc, '[Not Indexed]' ) 'index_type',
  stuff((SELECT ', ' + c.name FROM sys.stats_columns as sc
    INNER JOIN sys.columns as c on c.[object_id] = sc.[object_id]
      and c.column_id = sc.column_id
   WHERE ss.[object_id] = sc.[object_id] and ss.stats_id = sc.stats_id
   ORDER BY c.column_id FOR XML PATH('')), 1, 1, '') 'columns',
i.is_unique 'UQK',i.is_primary_key 'PK',i.is_unique_constraint 'UQC',
ss.auto_created 'auto',ss.user_created 'user',
ss.name as statsname,
CASE WHEN i.name is null then stats_date(t.[object_id], ss.stats_id)
 ELSE stats_date(t.[object_id], i.index_id)
 END 'stats_date'
FROM sys.stats as ss INNER JOIN sys.objects so on ss.[object_id]=so.[object_id]
 INNER JOIN sys.tables t on so.name = t.name
 LEFT OUTER JOIN sys.indexes as i on ss.name = i.name
WHERE t.is_ms_shipped = 0  -- filter out system tables
   and ss.auto_created=1        -- use this for just auto created stats
-- and i.type_desc='CLUSTERED'  -- use this to display just cluster keys
-- and i.is_primary_key=1       -- use this to display just primary keys
-- and so.name='Customer'       -- use this to display a specific table
ORDER BY t.name,i.index_id

　　使用XML显示计划识别缺少的索引，该方法采用与Missing Index视图同样的过程，但直接以XML格式显示结果

USE AdventureWorks2008
GO
SET STATISTICS XML ON
GO
-- Run a query over some fields that are not indexed
SELECT sd.SalesOrderID,sd.ProductID,sd.OrderQty
FROM Sales.SalesOrderDetail sd
WHERE sd.LineTotal>1000
GO

这将生成一个额外的查询执行计划的XML结果集。单击ShowPlan XML将打开执行计划，从中可右击并选择缺少索引详细信息命令，查看查询优化器的建议。

　　索引碎片管理：随着对底层做出更改，索引会变得碎片化，这回在为填充数据的数据页中留下空隙，而在进行表扫描或分区表扫描时，这又会导致额外的页读取。如果碎片变得严重，就会显著降低查询性能，对于聚集索引来说更是如此，因为它实际表示了数据在磁盘上的物理顺序。下面的脚本将会对AdventureWorks2008数据库中碎片化程度超过10%的所有索引进行碎片整理，堆被忽略。REORGANIZE选项用于碎片化程度轻的索引，而REBUILD选项在碎片化严重时使用

-- Identify all indexes in AdventureWorks2008 with more than 10% fragmentation
SELECT OBJECT_SCHEMA_NAME(ps.object_id) 'schemaname',
  OBJECT_NAME(ps.object_id) 'objectname', si.name 'indexname',ps.*
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),
  null,null,null,'DETAILED') ps
LEFT OUTER JOIN sys.indexes si on ps.[object_id]=si.[object_id] and
  ps.index_id=si.index_id
WHERE ps.avg_fragmentation_in_percent>10


-- The following script will automatically de-fragment every index in the
-- AdventureWorks2008 database that is more than 10% fragmented.
-- <BEGIN SCRIPT>
USE AdventureWorks2008;
SET NOCOUNT ON;
GO

-- Get work table, all indexes >10% fragmented
DECLARE @work TABLE ([objid] int, idxid int, pnum int, avgfrag real);
INSERT @work ([objid],idxid,pnum,avgfrag)
SELECT [object_id],index_id,partition_number,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),
  null,null,null,'LIMITED')
WHERE avg_fragmentation_in_percent>10 and index_id>0;

-- loop through worklist
Declare @objfullname nvarchar(261), @indexname nvarchar(130), @pcount int,
  @cmd nvarchar(1000);
Declare @objid int, @idxid int, @pnum int, @avgfrag real
Declare worklist CURSOR FOR SELECT * from @work;
OPEN worklist
WHILE(1=1) BEGIN;
  FETCH NEXT FROM worklist into @objid,@idxid,@pnum,@avgfrag;
  IF @@FETCH_STATUS < 0 BREAK;

  -- Get the object full name, index name, and total partition count
  SELECT @objfullname=OBJECT_SCHEMA_NAME(@objid) + '.' + OBJECT_NAME(@objid);
  SELECT @pcount=(SELECT COUNT(*) FROM sys.partitions
    WHERE [object_id]=@objid and index_id=@idxid);
  SELECT @indexname=name FROM sys.indexes
    WHERE [object_id]=@objid and index_id=@idxid;

  -- Create the command, if fragmentation is over 25 then REBUILD
  SELECT @cmd=N'ALTER INDEX ' + @indexname + N' ON ' + @objfullname +
    case when @avgfrag>25 then N' REBUILD' else N' REORGANIZE' end

  -- Execute the command to defrag the index
  Exec (@cmd);
  Print 'Executed ' + @cmd;      
END;

-- Clean up
CLOSE worklist;
DEALLOCATE worklist;
DELETE FROM @work;
-- <END SCRIPT>

　　确定索引有效性：索引会有开销，一个表上有太多索引会严重损害插入和更新性能，如果一个索引很少或从不使用，按么删除该索引将能提高整体性能，sys.dm_db_index_physical_stats管理视图报告了索引使用情况。下面的实例将显示自最近一次启动以来还未被用户或系统使用的非聚集，非主键索引的列表

-- Display all non-clustered, non-primary key indexes that have not been used by
-- a user or by the system since the last restart
SELECT DB_NAME([database_id]) 'dbname', so.name 'objname', si.name 'indexname',
  si.type_desc 'indextype', si.is_primary_key 'PK',
  ius.user_seeks+ius.user_scans+ius.user_lookups 'userusage',
  ius.system_seeks+ius.system_scans+ius.system_lookups 'sysusage'
FROM sys.dm_db_index_usage_stats ius
inner join sys.objects so on ius.[object_id]=so.[object_id]
inner join sys.indexes si on ius.[object_id]=si.[object_id]
  and ius.index_id=si.index_id
WHERE so.is_ms_shipped=0
-- comment out the following restrictions to see all indexes
  and ius.user_seeks+ius.user_scans+ius.user_lookups+ 
    ius.system_seeks+ius.system_scans+ius.system_lookups = 0
  and si.type <> 1 and si.is_primary_key = 0

数据库引擎优化顾问

　　如前所述，可以使用DMV分析索引使用情况，但分析起来可能有点困难并会占用大量的时间，可以使用数据库引擎优化顾问（Database Engine Tuning Advisor，DTA）来分析现有索引，而不必投入大量时间来分析索引使用情况并测试各种配置。DTA可以返回有关添加索引，索引视图以及表分区的建议，它还会建议删除对性能不利的现有数据结构。要进行尝试，首先需要创建3个表，NewContact，NewSalesOrder和NewTransactionHitory表

-- STEP 1, create three tables to experiment with
USE AdventureWorks2008;
GO
SELECT * INTO dbo.NewContact
FROM Person.Person;

SELECT * INTO dbo.NewSalesOrder
FROM Sales.SalesOrderDetail;

SELECT * INTO dbo.NewTransactionHistory
FROM Production.TransactionHistory;

这些表没有索引，所以针对他们的任何差序你都会导致全扫描。下面将在NewSalesOrder表的rowguid列上创建一个非聚集索引，以查看优化顾问的建议

-- STEP 2, Add an index on the rowguid to see what the
-- advisor thinks of it
USE AdventureWorks2008;
GO
CREATE NONCLUSTERED INDEX ix_RowGUID ON dbo.NewSalesOrder (rowguid);
GO

在创建了表和索引之后，可以分析工作负荷，看对这些表执行查询的性能如何。下面的脚本把所有的脏页刷新到磁盘，清空了过程缓存和缓冲区缓存，并将STATISTICS IO设为ON来捕获IO性能数据，最后，它查询这三个新表

-- STEP 3, Analyze a workload
-- SAVE this section as a seperate query file named AWWorkLoad.sql
-- <BEGIN AWWorkLoad.sql>
CHECKPOINT; 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
USE AdventureWorks2008;
GO

SELECT LastName FROM dbo.NewContact
WHERE LastName BETWEEN 'A' AND 'C';

SELECT LastName FROM dbo.NewContact
WHERE LastName LIKE 'M%';

SELECT ProductID, SUM(OrderQty) AS SumQty, SUM(UnitPrice) AS SumPrice
FROM dbo.NewSalesOrder GROUP BY ProductID;
  
SELECT ProductID, TransactionDate, SUM(Quantity) AS TotalQty
  , SUM(ActualCost) AS SumCost
FROM dbo.NewTransactionHistory 
WHERE TransactionDate BETWEEN '2003-11-12' AND '2004-01-31'
GROUP BY ProductID, TransactionDate;
GO
-- <END AWWorkLoad.sql>

在执行这个查询之前，选择包括之际的执行计划命令来返回图形化查询计划，该查询的消息结果显示了大量的读取数和用于聚合的工作表的创建，要分析这个查询批处理，可以打开DTA（工具-数据库引擎优化顾问），并启动一个新会话，在会话名称区域中，可以保留默认名称或者可以自己输入一个描述性的名称，接下来选择刚才的文件（AWWorkLoad.sql）为工作负荷文件，设置用于分析的数据库为AdventureWorks2008，并只选择文件中引用的三个表，在优化选项选项卡上，选中索引，不分区和不保留任何现有PDS单选按钮。单击开始分析按钮分析AWWorkLoad.SQL文件，DTA会搜集有关对象的元数据，并检查可能的索引策略来提高查询的性能。分析完成好似，DTA会返回其建议，单击操作菜单中的应用建议选项可以立即应用DTA的建议，或者可以单击保存建议选项把他们保存至文件，然后可以在此运行查看前后两次的对比。

　　DTA也可以通过分析Profiler或SQL跟踪存储过程搜集到的跟踪数据来提出建议。当创建一个ieheDTA一起使用的Profiler跟踪时，可以使用Tuning模板，或者配置自己的时间设置，然后，要使用DTA分析跟踪文件，必须返回性能数据，返回数据的事件是Completed事件，如SP:Completed和SQL:Completed。

　　使用系统监视器查看性能计数器或使用Profiler查看SQL Server活动都可以帮助监视和检测高开销的活动。现在Profiler可以基于时间导入并关联性能数据，这在寻找导致系统瓶颈的进程时特别有用，使用前面创建的三个表和一个新的工作脚本试试这个功能，首先，打开一个新的查询窗口，输入下列代码并保存为AWPerformance.SQL：

-- STEP 1, Script to correlate performance data with profiler data
-- SAVE THIS SCRIPT AS AWPerformance.SQL 
-- <BEGIN AWPerformance.sql>
USE AdventureWorks2008;
GO
DECLARE @Iterations AS int;
SET @Iterations = 0;
WHILE @Iterations < 10
BEGIN
  SELECT ProductID, SUM(OrderQty) AS TotalProduct, SUM(UnitPrice)
  FROM dbo.NewSalesOrder GROUP BY ProductID
    WAITFOR DELAY ?0:00:02?
  UPDATE dbo.NewSalesOrder SET UnitPrice = UnitPrice * 1.25
  WHERE ProductID % 2 = 0
  SET @Iterations = @Iterations + 1
END
  WAITFOR DELAY '00:00:02'

IF EXISTS (SELECT * FROM sys.indexes 
WHERE name = 'ix_TransactionID')
DROP INDEX ix_TransactionID ON dbo.NewTransactionHistory
CREATE NONCLUSTERED INDEX ix_TransactionID ON dbo.NewTransactionHistory
(TransactionID);

SET @Iterations = 0;
WHILE @Iterations < 5
BEGIN
  INSERT dbo.NewTransactionHistory 
  (ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate
  , TransactionType, Quantity, ActualCost, ModifiedDate)
  SELECT ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate
  , TransactionType, Quantity, ActualCost, ModifiedDate
  FROM dbo.NewTransactionHistory WHERE TransactionID % 21 = 1
    WAITFOR DELAY '00:00:02'
  UPDATE dbo.NewSalesOrder SET UnitPrice = UnitPrice / 1.25
  WHERE ProductID % 2 = 0
  SET @Iterations = @Iterations + 1
END
  WAITFOR DELAY '00:00:02'
ALTER INDEX ix_TransactionID 
ON dbo.NewTransactionHistory REBUILD;
-- <END AWPerformance.sql>

现在打开可靠性和性能监视器，创建一个新的名为AwCollector的类型为性能计数器的数据收集器集，向计数器日志中添加Pages/sec，Avg.Disk Queue Length，%Processor Time和Page Split/sec计数器，将采样间隔设置为1秒，单击确定按钮保存该数据收集器并开始采样。打开SQL Server Profiler使用空白模板创建一个叫做AWPerformance的新跟踪，将该跟踪保存到一个最大文件大小为15MB的，名为AWPerformance.trc的文件中，在事件选择选项卡上，展开TSQL事件，选择SQL:BatchCompleted和SQL:StmtCompleted事件。单击组织列按钮并将Text Data列移至ApplicationName之下，单击确定按钮，然后单击运行按钮。返回到之前创建的AWPerformance脚本并运行它，脚本完成之后停止Profiler跟踪和数据收集器，在Profiler中，打开其创建的SWPerformance.trc跟踪文件，女人啊后通过单击文件-导入性能数据选项导入数据收集器日志，这样就可以查看相关的性能数据了。

T-SQL优化技巧

　　代码注意优化的一些小技巧：SET NOCOUNT ON；去安全限定对象名称；避免使用DISTINCT；使用UNION ALL而不是UNION；避免不必要的排序

　　限制结果集：使用TOP N和SET ROWCOUNT是两种限制返回的行数的方法，即使当查询本身会生成额外数据时也是。

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- No limit on rows returned
SELECT ProductID,Name,ListPrice 
FROM Production.Product
ORDER BY ListPrice desc
-- (504 row(s) affected)
-- Table 'Product'. Scan count 1, logical reads 15, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 280 ms.
GO

-- Limit rows returned using TOP N
SELECT TOP 5 ProductID,Name,ListPrice 
FROM Production.Product
ORDER BY ListPrice desc
-- (5 row(s) affected)
-- Table 'Product'. Scan count 1, logical reads 15, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 6 ms.
GO

-- Limit rows returned using SET ROWCOUNT
SET ROWCOUNT 5
SELECT ProductID,Name,ListPrice 
FROM Production.Product
ORDER BY ListPrice desc
SET ROWCOUNT 0
-- (5 row(s) affected)
-- Table 'Product'. Scan count 1, logical reads 15, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 7 ms.
GO

　　通常，在链接中越早应用筛选器，结果就越好，因此必须放在HAVING自居中的筛选器只是那些基于计算结果的筛选器，其他情况下，筛选器应该放在WHERE子句中

　　在构造WHERE子句时一个常见的错误是将列包含在一个函数中，而不是对常量使用函数。SQL Server的性能很大程度上取决于查询优化器使用索引的能力。将表列包装在函数中会使查询优化器看不到该列，导致使用较慢的全表扫描而不是索引

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- The where clause uses a function that hides the column from
-- the query optimizer.  An ineffecient SCAN is used.
SELECT Name
FROM Production.Product
WHERE substring(Name,1,2)='BE'
-- Index Scan
-- (1 row(s) affected)
-- Table 'Product'. Scan count 1, logical reads 5, physical reads 0.
-- CPU time = 10 ms,  elapsed time = 38 ms.
GO

-- An alternative that accomplishes the same result using the 
-- built-in Like operator.  Now the optimizer sees the column
-- and chooses an efficient SEEK.
SELECT Name
FROM Production.Product
WHERE Name like 'BE%'
-- Index Seek
-- Table 'Product'. Scan count 1, logical reads 2, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 2 ms.
GO

　　ANSI风格的联接语法;ANSI联接语法基本上要求在联接表时显示为每个联接表生命联接条件，而不是通过WHERE子句中的比较隐士派生。

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- This query is missing a join - try to find it!
-- SQL-style joins make it difficult to find problems
SELECT poh.PurchaseOrderID,poh.OrderDate,poh.EmployeeID,pod.ProductID,
  pp.Name 'ProductName',pv.Name 'VendorName' 
FROM Purchasing.Vendor pv, Person.BusinessEntity pbe, Production.Product pp,
 Purchasing.PurchaseOrderHeader poh, Purchasing.PurchaseOrderDetail pod
WHERE pv.BusinessEntityID=poh.VendorID and poh.PurchaseOrderID<10
  and poh.PurchaseOrderID=pod.PurchaseOrderID   and poh.PurchaseOrderID>5
  and pp.ProductID=pod.ProductID 
-- (290878 row(s) affected)
-- Table 'BusinessEntity'. Scan count 1, logical reads 62, physical reads 0.
-- Table 'Product'. Scan count 0, logical reads 28, physical reads 0.
-- Table 'PurchaseOrderDetail'. Scan count 4, logical reads 8, physical reads 0.
-- Table 'Vendor'. Scan count 0, logical reads 8, physical reads 0.
-- Table 'PurchaseOrderHeader'. Scan count 1, logical reads 2, physical reads 0.
-- CPU time = 871 ms,  elapsed time = 12919 ms.
GO

-- The problem is clear with the ANSI-sytle joins
SELECT poh.PurchaseOrderID,poh.OrderDate,poh.EmployeeID,pod.ProductID,
  pp.Name 'ProductName',
pv.Name 'VendorName'
FROM Purchasing.PurchaseOrderDetail pod
 inner join Purchasing.PurchaseOrderHeader poh on
            pod.PurchaseOrderID=poh.PurchaseOrderID 
 inner join Production.Product pp on pod.ProductID=pp.ProductID 
 inner join Purchasing.Vendor pv on pv.BusinessEntityID=poh.VendorID
 --,Person.BusinessEntity pbe  -- This table should not have been included
WHERE poh.PurchaseOrderID>5 and poh.PurchaseOrderID<10      
-- (14 row(s) affected)
-- Table 'Product'. Scan count 0, logical reads 28, physical reads 0.
-- Table 'Vendor'. Scan count 0, logical reads 28, physical reads 0.
-- Table 'PurchaseOrderDetail'. Scan count 4, logical reads 8, physical reads 0.
-- Table 'PurchaseOrderHeader'. Scan count 1, logical reads 2, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 2 ms.
GO

　　处理NULL值：NULL的实际定义是表明它是个表示数据值不存在的特殊值，不是0，不是空字符串，不是最小日期值，不意味着不可应用，也不意味着不知。NULL实际上表示没有。它表示一个不存在或缺少的值。SQL Server中没有将两个NULL视为不同的值，但同时他们也不是相等的。在SQL Server中，分组基于不同的值而不是不相等的值，结果就将所有的NULL值分组在一起。有关NULL处理的另一个特例就是在列表上的CHECK约束中。当评估CHECK约束时，SQL Server将接受条件评估为NULL的数据。而WHERE自居中的相同表达式将排除评估为NULL的数据。

　　游标的替代选择：直接上例子

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Cursor based approach
-- Generate a comma-delimited list of vendors that supply each product
DECLARE @results TABLE
 (ProductID int primary key,Vendors varchar(max))

DECLARE @pid int, @vname varchar(50), @vlist varchar(max)
DECLARE product_cursor CURSOR FOR SELECT ProductID FROM Production.Product pp
WHERE EXISTS(SELECT 1 FROM Purchasing.ProductVendor pv
   WHERE pv.ProductID=pp.ProductID)
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @pid
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @vlist=''
  DECLARE vendor_cursor CURSOR FOR
    SELECT v.Name FROM Purchasing.ProductVendor pv
  inner join Purchasing.Vendor v on pv.BusinessEntityID=v.BusinessEntityID
  WHERE ProductID=@pid
  OPEN vendor_cursor
  FETCH NEXT FROM vendor_cursor into @vname
  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @vlist = @vlist + '; ' + @vname
    FETCH NEXT FROM vendor_cursor into @vname
  END
  CLOSE vendor_cursor
  DEALLOCATE vendor_cursor
  
  INSERT @results (ProductID,Vendors)
  VALUES (@pid,substring(@vlist,3,datalength(@vlist)))
  FETCH NEXT FROM product_cursor INTO @pid
END
CLOSE product_cursor
DEALLOCATE product_cursor
SELECT * FROM @results
-- CPU time = 891 ms,  elapsed time = 4394 ms.
GO

-- The same operation using a set based approach
SELECT p.ProductID,
stuff((SELECT '; ' + v.name FROM Purchasing.Vendor v
 inner join Purchasing.ProductVendor pv
on v.BusinessEntityID=pv.BusinessEntityID 
WHERE p.ProductID=pv.ProductID 
ORDER BY pv.BusinessEntityID FOR XML PATH('')), 1, 1, '') 'vendors'
FROM Production.Product p
WHERE EXISTS(SELECT 1 FROM Purchasing.ProductVendor xv
   WHERE xv.ProductID=p.ProductID)
-- CPU time = 10 ms,  elapsed time = 503 ms.
GO

　　合并联接：新的合并联接符可以在一个表上同时执行插入、更新和删除操作。在SQL Server中，首次可以通过一条语句有效的将一个表或查询的内容合并到另一个表中。在内部，合并过程被优化为最小化必须的扫描数。相比于以前对每个操作使用单独语句的方法，这使合并具有明显的性能优势。下面的例子显示了合并联接的许多功能。首先，下列代码创建了两个匹配表，然后对其中一个表执行一些更新操作

-- Merge Join Example
USE AdventureWorks2008
GO

-- Setup a baseline to test from
SELECT TOP 10 BusinessEntityID 'ID',AccountNumber,Name
  INTO dbo.VendA FROM Purchasing.Vendor
  ORDER BY BusinessEntityID 
SELECT * INTO dbo.VendB FROM dbo.VendA
GO

-- Make some changes to VendA
INSERT dbo.VendA (ID,AccountNumber,Name)
  VALUES (5000,'ACME0001','ACME Corporation')
UPDATE dbo.VendA SET Name='Zen Master' WHERE ID=1502
DELETE dbo.VendA WHERE ID=1510
GO

为了对VendB执行与VendA同样的更改，可以使用单独的更新语句或合并联接。使用单独的更新语句将需要采用下列代码

-- Update VendB using separate statements, this must be wrapped in a 
-- transaction to keep the operation atomic.
-- Don't actually run this, it is just here to show the alternative.
/*
BEGIN TRY
  BEGIN TRAN
  -- UPDATE
  UPDATE VendB
  SET Name=VendA.Name, AccountNumber=VendA.AccountNumber
  FROM VendB inner join VendA on VendB.ID=VendA.ID
  -- INSERT 
  INSERT VendB (ID,AccountNumber,Name)
  SELECT VendA.ID,VendA.AccountNumber,VendA.Name
  FROM VendA left join VendB on VendA.ID=VendB.ID
  WHERE VendB.ID is null      
  -- DELETE
  DELETE VendB
  WHERE NOT EXISTS(SELECT * FROM VendA WHERE VendA.ID=VendB.ID)
  COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
END CATCH
GO
-- Table 'VendB'. Scan count 3, logical reads 34, physical reads 0.
-- Table 'VendA'. Scan count 3, logical reads 43, physical reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 18 ms.
GO
*/

现在看一下如何使用合并联接完成同样的操作

-- Now use merge to populate VendB with the same data as VendA
MERGE INTO VendB
USING VendA
  ON VendA.ID=VendB.ID
WHEN MATCHED THEN
  UPDATE SET Name=VendA.Name, AccountNumber=VendA.AccountNumber
WHEN NOT MATCHED BY TARGET THEN
  INSERT (ID,AccountNumber,Name)
  VALUES (VendA.ID, VendA.AccountNumber, VendA.Name)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;
GO
-- Table 'VendB'. Scan count 2, logical reads 33, physical reads 0.
-- Table 'VendA'. Scan count 2, logical reads 22, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 6 ms.
GO

　　分组集：分组集是SQL Server 2008的另一项新功能，他们允许同时聚合多个字段分组，将所有不同的结果一起返回。假定想从AdventureWorks2008数据库中返回按年份，按客户，按地区分区和全部的销售总量，在以前版本的SQLServer中，可以运行四个不同的查询，然后组合他们来得到汇总数据

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Multiple groupings using individual queries
SELECT CustomerID,null 'TerritoryID',null 'Year',SUM(TotalDue) 'Total'
FROM Sales.SalesOrderHeader 
GROUP BY CustomerID
UNION ALL
SELECT null,TerritoryID,null,SUM(TotalDue) 'Total'
FROM Sales.SalesOrderHeader 
GROUP BY TerritoryID
UNION ALL
SELECT null,null,Year(OrderDate),SUM(TotalDue) 'Total'
FROM Sales.SalesOrderHeader 
GROUP BY Year(OrderDate)
UNION ALL
SELECT null,null,null,SUM(TotalDue) 'Total'
FROM Sales.SalesOrderHeader 
ORDER BY CustomerID desc,TerritoryID desc,[Year] desc
-- Table 'SalesOrderHeader'. Scan count 4, logical reads 2744, physical reads 0.
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
-- CPU time = 280 ms,  elapsed time = 1973 ms.
GO

通过分组集，可以使用一个更加简短、更易于理解的语法实现同样的结果

-- The same multiple groupings using grouping sets
SELECT CustomerID,TerritoryID,Year(OrderDate) 'Year',SUM(TotalDue) 'Total'
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS ((CustomerID),(TerritoryID),(Year(OrderDate)),())
ORDER BY CustomerID desc,TerritoryID desc,Year(OrderDate) desc
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
-- Table 'SalesOrderHeader'. Scan count 3, logical reads 2058, physical reads 0.
-- CPU time = 301 ms,  elapsed time = 1845 ms.
GO

　　非重复值聚合：非重复聚合就是以COUNT的形式使用DISTINCT关键字计算唯一值数。SQL Server难以优化这类语句，特别是如果在单个查询中使用了多个非重复值计数。如果涉及了一个有用的查询，但是当添加非重复值聚合时，它的速度就被减慢下来，这回让人很有挫败感。问题的根本在于为了让SQL Server 2008计算非重复值聚合，它必须基于输入数据流创建和读取中间结果表。对于同一查询中的多个非重复聚合来说，SQL Server会重复这一过程，销毁并重新创建数据流，为每个总计生成新的中间结果。这笔旨在子查询中计算总计的开销要大得多，例如，考虑下面这个查询

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Counts using distinct aggregation
SELECT COUNT(distinct sd.SalesOrderID) 'Orders',
  COUNT(distinct sd.SalesOrderDetailID) 'Details',
  COUNT(distinct sh.CustomerID) 'Customers'
FROM Sales.SalesOrderDetail sd
  inner join Sales.SalesOrderHeader sh on sd.SalesOrderID=sh.SalesOrderID 
GO
-- Table 'Worktable'. Scan count 3, logical reads 246112, physical reads 0.
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 228, physical reads 2.
-- Table 'SalesOrderHeader'. Scan count 1, logical reads 45, physical reads 2.
-- CPU time = 1822 ms,  elapsed time = 2036 ms.
GO

注意，工作表中需要大量读取数，以及必须扫描它三次来生成三个非重复值总计。在这类情况下，我所采取的另一种方法是利用子查询分组和计数唯一值，然后将子查询联接到一个最终的结果集中。下面的查询使用这一技术生成了同样的结果，但是只使用了少量资源和时间

-- Distinct Counts using sub-queries
WITH OrderCounter (Orders,Details) AS
( SELECT COUNT(1) 'Orders',SUM(Lines) 'Details' FROM
  (SELECT SalesOrderID,COUNT(SalesOrderDetailID) 'Lines'
   FROM Sales.SalesOrderDetail GROUP BY SalesOrderID) s
),
CustomerCounter (Customers) AS
( SELECT COUNT(1) 'Customers' FROM 
  (SELECT CustomerID FROM Sales.SalesOrderHeader GROUP BY CustomerID) c 
)
SELECT Orders,Details,Customers
FROM OrderCounter,CustomerCounter
GO
-- Table 'SalesOrderHeader'. Scan count 1, logical reads 45, physical reads 0.
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1240, physical reads 16.
-- CPU time = 100 ms,  elapsed time = 250 ms
GO

　　如何查询表中的记录数：很多使用使用COUNT(*)计算记录总数，这一方法是通过对主键索引扫描或是在没有主键的情况下执行全表扫描来计数表中的记录数。对于小型表，这一方法很迅速，但是对于大型表，需要花一些时间来计数所有的记录数。有一种更快的方法，利用SQL Server自动为每个表和索引手机的统计信息。但是要注意，对于有大量插入或删除的操作表来说，这一方法返回的记录数并不能保证完全正确。

-- Get record count using partition statistics
select sum(row_count) 'TotalRows'
FROM sys.dm_db_partition_stats
WHERE object_id=object_id('Production.TransactionHistory')
and index_id<=1
-- Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0.
-- CPU time = 0 ms,  elapsed time = 0 ms.
GO

可以将它修改为通过单个操作返回数据库中每个表的记录计数

-- Get Record counts for every user table in the current database
select so.name 'TableName', so.type, sum(row_count) 'TotalRows'
FROM sys.dm_db_partition_stats ps inner join sys.objects so
on ps.[object_id]=so.[object_id]
WHERE index_id<=1 and so.[type]='U'
GROUP BY so.name,so.type
ORDER BY SUM(row_count) DESC
-- Table 'sysschobjs'. Scan count 0, logical reads 294, physical reads 0.
-- Table 'sysidxstats'. Scan count 1, logical reads 11, physical reads 0.
-- CPU time = 10 ms,  elapsed time = 37 ms.
GO

如果有必要，可以运行UPDATEUSAGE命令手动更新统计的行总计

-- Update statistics for every table and index in the database
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
GO

-- Update statistics for a single table
DBCC UPDATEUSAGE(0,'Production.TransactionHistory',1) WITH COUNT_ROWS 
GO

　　临时表与表变量：在编写存储过程时，通常需要在过程的作用于内临时存储表格式数据，在SQL Server 2008中主要有两种存储临时数据的方法：临时表和表变量。两者在功能上有很多重叠之处，很难说清楚哪个更好。他们用于解决不同类别的问题，因此，在临时表和变量表之间进行选择是，考虑一下因素：临时表总是在tempdb中创建，而表变量则是在内存中创建；临时表具有会话作用域，而表变量只有过程/批处理作用域；不能再表变量上使用DDL；表变量上的统计信息不会被计算；表变量不参与事务或锁定，不受回滚影响；可以从用户定义函数中创建和访问表变量，但临时表不可以；从SQL Server 2008开始，表变量可以作为参数传递

资源调控器

　　SQL Server 2008中的资源调控其可以区分工作负荷并为其设置优先级。当需要确保特定的关键进程有足够资源有效运行，同时又能与其他不太重要的作业共享资源时，这种功能特别有用。它还可用于将关键的进程与时空的查询相隔离，以及设置查询优先级。资源调控其由3个关键组件组成：区分工作负荷的分类器函数、描述对共享服务器资源的限制的资源池和用于对类似工作负荷强制实施策略的工作负荷组。

　　要想通过有意义的方式使用资源调控其，必须首先对其进行配置。配置过程由下列4步组成：配置资源池；配置工作负荷组；创建分类器函数；指派分类器函数并重新配置。

　　配置资源池：资源池用于为物理资源定义逻辑编辑。可以将资源池看作一种虚拟的服务器实例，每个池允许指定CPU和内存的最小和最大利用率百分比。最小值表示最少保证可用资源，而最大值表示工作资源限制。SQL Server自动创建两个资源池;内部池和默认池。内部池被用于关键性系统任务，不能以分和方式更改；默认池用于其他所有没有显示放入自定义池中的人物。不能创建或删除默认池，但可以重新配置它来限制资源使用。可以根据需要创建额外的资源，但是所有最小资源之和不能超过100%

CREATE RESOURCE POOL poolFinance WITH
(MAX_CPU_PERCENT=25,MAX_MEMORY_PERCENT=50)

　　工作负荷组用于定义应用于由分类器函数确定的类似请求的策略。存在两个内置的工作负荷组，内部组和默认组，分别指派给内部池和默认池。内部组用于系统任务，不能更改；默认组用于其他所有没有显示指派给用户定义组的任务。不能移动或删除，但是可以重新配置。可以自定义工作负荷组，并将其指派给默认池或用户定义值：

CREATE WORKLOAD GROUP wrkgroupFinance WITH
(IMPORTANCE=MEDIUM,REQUEST_MAX_MEMORY_GRANT_PERCENT=50,REQUEST_MAX_CPU_TIME_SEC=0,REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,MAX_DOP=4,GROUP_MAX_REQUESTS=25)
USING poolFinance;

　　创建一个分类器函数：资源调控器使用分类器函数确定将哪个工作负荷组用于传入的会话。如果启用了资源调控器，这个函数将在身份验证和任意登录触发器后运行。它必须返回要向其指派传入的会话的工作负荷组的名称。如果分类器函数因为某种原因失败或是返回无效的工作负荷组，那么会话将被指派到默认组。对于分类器函数有一些特殊要求：应该总是在服务器范围内创建他们，这意味着他们应该驻留在master数据库中；在某个时刻，只能有一个分类器函数是活动，活动的函数不能从数据库中删除；分类器函数必须快速完成以避免导致联接超时。下面的例子演示了如何创建分类器函数

USE master
GO

CREATE FUNCTION fnTestClassifier() RETURNS SYSNAME WITH SCHEMABINDING AS
BEGIN
DECLARE @grpName SYSNAME 
　　IF(SUSER_SNAME()='sa')
　　　　SET @grpName='wrkgroupAdmin'
　　ELSE IF (APP_NAME() like '%Logistics%')
　　　　SET @grpName='wrkgroupLogDep'
　　ELSE IF (APP_NAME() like '%REPORT SERVER%')
　　　　SET @grpName='wrkgroupReports'
　　ELSE
　　　　SET @grpName='default'
　　RETURN @grpName
END;

　　指派分类器函数并重新配置：

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fnTestClassifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE

　　监视资源调控器:监视各个工作负荷组和资源池是维护平稳运行的服务器的一个关键部分.监视可以使管理员在配置问题影响关键业务操作之前就识别并纠正他们.有三种获取资源调控器的统计信息的方法:性能计数器,事件和系统视图。性能计数器是报告资源调控器性能统计信息的首选方式，有两个新的计数器可以用于此目的：SQLServer:Workload Group Stats，报告工作负荷组的通信及信息，如活动请求数和接受量小于最优资源的请求数；SQLServer: Resource Pool Stats，报告各种资源池内存和CPU统计信息，包括池的总CPU使用率和使用内存总量。

　　假定面临这样一种情况，即允许一些报表用户执行即席查询，同时又能保证业务应用程序用户不会受到过度的影响。这个练习演示了如何使用资源调控器实现这一目的。在这之前需要创建两个用户和业务应用程序用户，在SSMS中打开命令窗口，执行下列代码创建这些用户

-- STEP 1
-- PREP Task 1, create test users
USE AdventureWorks2008 
GO
CREATE LOGIN AdHocUser WITH password='P@ssw0rd', 
    DEFAULT_DATABASE=AdventureWorks2008
exec sp_grantdbaccess 'AdHocUser'
exec sp_addrolemember 'db_datareader', 'AdHocUser'
GO
CREATE LOGIN FinanceUser WITH password='P@ssw0rd',
    DEFAULT_DATABASE=AdventureWorks2008
exec sp_grantdbaccess 'FinanceUser'
exec sp_addrolemember 'db_datareader', 'FinanceUser'
GO

必须在master数据库中创建一个分类器函数，它将把每个用户指派到不同的工作负荷组。可以提前创建这个函数，但是知道将它绑定到资源调控器时才会实际使用这个函数：

-- STEP 2
-- PREP Task 2, create a classifier function
USE master
GO
CREATE FUNCTION fnClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @wrkGroup sysname
  
  IF SUSER_SNAME()='AdHocUser' BEGIN
    SET @wrkGroup='wrkgrpAdHoc'
  END ELSE IF SUSER_SNAME()='FinanceUser' BEGIN
    SET @wrkGroup='wrkgrpFinance'
  END ELSE BEGIN
    SET @wrkGroup='default'
  END
  RETURN @wrkGroup
END
GO

在有了一个分类器函数和两个测试用户后，就可以配置资源调控器了。在对象资源管理器中展开管理节点，右击资源调控器并选择属性命令，单击资源调控器复选框启用编辑功能。资源调控器中的大部分属性是区分大小写的，因此在输入配置时小心

资源池	相关的工作负荷组
poolAdHoc     Maximum CPU%=10     Maximum Memory%=25	wrkgrpAdHoc     Maximum Requests=15     Degree of Parallelism=4
poolFinance     (use default settings)	wrkgrpFinance     (use default settings)

确保选择分类器函数名称，单击确定按钮保存配置并启用资源调控器，对象资源管理中的图标应该变成了表明资源调控器在运行。如果运行的是多CPU系统，那么需要限制SQL Server可用的CPU数，强制工作负荷竞争同一个CPU。只要可用的CPU数多于请求数，就不会有CPU资源争用。在SQL Server中，可以在不重启的情况下通过执行下列代码设置CPU关联

-- STEP 6
-- PREP Task 3, set CPU Affinity (only for testing!)
sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'affinity mask', 1;  -- default value of 0 means use all CPUs
GO
sp_configure 'affinity i/o mask', 1;
RECONFIGURE WITH OVERRIDE;
GO

接着需要模拟两个竞争的CPU密集型工作负荷来确认资源调控器在起作用。必须为上面创建的每个用户打开单独的查询会话。在SSMS中，选择文件-新建-数据库引擎查询选项，然后将身份验证模式更改为SQL Server身份验证，并未AdHocUser输入用户名和密码。对FinanceUser重复这一过程。创建了两个查询会话后，可以在一个新的查询窗口中运行下列代码，确认他们被指派给正确的资源池

-- STEP 7
-- Confirm users are connected to the correct pools
select rp.name 'PoolName', wg.name 'WrkGrpName',xs.session_id,
xs.login_name,xs.login_time,xs.[program_name]
  from sys.dm_exec_sessions xs
  inner join sys.dm_resource_governor_workload_groups wg on xs.group_id=wg.group_id
  inner join sys.dm_resource_governor_resource_pools rp on wg.pool_id=rp.pool_id 
WHERE wg.pool_id>=256
GO

假定正确指派了资源池，那么接下来就是在运行一些CPU密集型工作负荷时监视那些池。要监视池，需要使用可靠性和性能监视器。从控制面板打开这一工具，添加计数器来监视每个资源池的CPU使用率百分比，输入计数器完毕后，立即开始监视，最后在为AdHocUser和FinanceUser打开的两个查询窗口中输入下列代码并等待执行

-- STEP 9
-- Copy this into the query windows for both AdHocUser and FinanceUser
-- CPU Intensive Workload
USE AdventureWorks2008
GO

-- This query will peg the CPU at 100%
SELECT MAX(p1.r1 + p2.r2) mv FROM
 (SELECT ROW_NUMBER() OVER (ORDER BY ProductID) r1
  FROM Production.TransactionHistory) p1
CROSS JOIN
 (SELECT ROW_NUMBER() OVER (ORDER BY ProductID DESC) r2
  FROM Production.TransactionHistory) p2
OPTION (MAXDOP 1)
GO

-- EXTRA, when you are done, this will reset the CPU Affinity (use all CPUs)
sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'affinity mask', 0;  -- default value of 0 means use all CPUs
GO
sp_configure 'affinity i/o mask', 0;
RECONFIGURE WITH OVERRIDE;
GO

上面的代码将生成一个大量使用CPU的工作负荷，首先执行AdHocUser的工作负荷代码，等待约10秒，然后执行Financeuser的工作负荷代码。两个查询持续运行较长时间，在运行时，可切换至性能监视器屏幕观察其结果。注意，当AdHocUser运行查询时，他们接受所有的CPU资源，因为没有其他工作负荷竞争。不过，在FinanceUser开始查询时，资源调控器会将AdHocUser接受的资源限制为配置的最大CPU使用率10%。

　　在查看结果结束后，可以停止查询并关闭相关的查询窗口。一旦关闭查询窗口，可以使用SSMS禁用资源调控器。最后，如果对SQL Server配置了CPU关联，那么可以重新配置affinity mask和affinity i/o mask来清除，不过此时使用的参数为0