管理索引时需要注意的问题：
A、标识出索引碎片。指出了应该对哪些索引进行重新生成或重新组织。
B、显示出索引的使用情况。可以指出哪些索引没有使用过。
建立索引的基本规则：
A、针对优先级高的、使用频繁的查询来增加索引，同时测试查询看索引是否被使用，并且不要同时增加多个索引。
B、除非有非常好的原因，否则在每一个表都增加一个聚集索引。
C、选择很少改变的、高度唯一的、数据类型占用字节少的列做为聚集索引键。非聚集索引应该建立在包含高度唯一数据的列上，基于查询中的应用挑选列，特别是在join和where子句中的列，为非键列考虑使用include。基于使用的查询来预估需要哪些索引，然后通过数据库引擎优化顾问工具来评估这些索引。
D、随着数据和应用程序活动的改变，索引的性能和效能也会改变，所以要监视查询性能。特别是索引碎片化会降低查询性能，返回相同的结果需要更多的IO操作，通过重新生成、重新组织索引来保持索引碎片最小化。
E、为只读文件组或数据库上的索引使用100%填充因子，这样使得完成查询需要访问的数据页更少，相应的IO操作也更少，效率自然得到提升。
1、索引碎片
/*======================================================= 注意：所有的参数都是以当前数据库来计算的，所以必须指定完全限定。 模式影响如何收集碎片数据： LIMITED:扫描堆所有的页，对于索引，只扫描叶级别上面的父级别页 SAMPLED:收集堆或索引中1%采样率的数据 DETAILED:扫描所有页，最精确但速度最慢 当制定null或default时，等同于limited模式。 ===================================================*/ select DB_NAME(d.database_id), OBJECT_NAME(d.object_id), i.name, --索引名称 d.index_type_desc, d.alloc_unit_type_desc, d.index_depth, --索引的深度 d.index_level, --索引当前级别 --索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片 d.avg_fragmentation_in_percent, --IN_ROW_DATA分配单元的叶级别中的碎片数 d.fragment_count, --IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数 d.avg_fragment_size_in_pages, --对于索引，平均百分比应用于 IN_ROW_DATA 分配单元中 b 树的当前级别 --对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比 d.avg_page_space_used_in_percent, d.page_count, --索引或数据页的总数 d.record_count, --总记录数 d.min_record_size_in_bytes, --最小记录大小（字节） d.max_record_size_in_bytes, --最大记录大小（字节） d.avg_record_size_in_bytes, --平均记录大小（字节） --压缩页的数目 d.compressed_page_count, --分配单元中将被虚影清除任务删除的虚影记录数 d.ghost_record_count, --由分配单元中未完成的快照隔离事务保留的虚影记录数 d.version_ghost_record_count, --堆中具有指向另一个数据位置的转向指针的记录数 d.forwarded_record_count from sys.dm_db_index_physical_stats (db_id('wcc'), --数据库id null, --对象id：数据库名称.架构.对象名称 null, --索引id null, --分区号 '' --模式 )d inner join sys.indexes i on d.object_id = i.object_id and d.index_id = i.index_id
2、索引使用情况
create table txt(id int primary key,v varchar(10)) create index idx_txt_v on txt (v) insert into txt values(1,'a'), (2,'b'), (3,'c') select v from txt --idx_txt_v索引中的user_scans会显示1 --user_updates会显示1 select DB_NAME(d.database_id), OBJECT_NAME(d.object_id), i.name, user_seeks, user_scans, user_lookups, user_updates --通过用户查询执行的更新次数 from sys.dm_db_index_usage_stats d inner join sys.indexes i on d.object_id = i.object_id and d.index_id = i.index_id where database_id = DB_ID('test2') --添加数据后，user_updates会显示2 insert into txt values(4,'a'), (5,'b'), (6,'c')丢失的索引
SELECT TOP 30 ROUND(s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks + s.user_scans ), 0) AS [Total Cost] , s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks + s.user_scans ) AS Improvement_Measure , DB_NAME() AS DatabaseName , d.[statement] AS [Table Name] , equality_columns , inequality_columns , included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle WHERE s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks + s.user_scans ) > 10 ORDER BY [Total Cost] DESC , s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks + s.user_scans ) DESC
3、统计信息
一个问题：
SQL Server的统计信息
我知道统计信息是为查询引擎做查询计划用的，现在有以下请教大家，谢谢！
1.统计信息是什么时候会自动建立与更新,Insert、update、delete时候会自动更新统计信息吗？
2.当我把数据库设置中的"自动创建统计信息"与"自动更新统计信息"设置为True，我还需要建立维护计划中建立计划维护统计信息吗？此处的自动建立与更新在什么时候会自动更新？
3.当我修改表结构与dbcc dbreindex时都会自动更新表或view的统计信息吗？
4.统计信息更新与建立时是生成一些什么信息？
我的回复：
1、只要你在数据库设置中的"自动创建统计信息"与"自动更新统计信息"设置为True，那么在语句执行时，不管是update、delete、还是select语句，就会根据where条件中的字段，自动设置统计信息。
但insert 语句运行时，应该不会建立统计信息。
2、虽然设置了自动更新或创建统计信息的设置为true，但是所谓自动更新统计信息，是按照你对数据修改的数量来决定的，sql server有一些内部的判断条件，比如修改的数据占到整个表的百分之多少后，才会更新统计信息，这个不是很准确的。
所以还是需要你通过，update statistics 表 来更新统计信息。
3、如果你没有删除那个统计信息涉及到的列，应该不会重新生成。
4、通过：dbcc show_statistics('表',统计名称)
呵呵，我做了一个实验，你可以看看。
下面的代码，需要一步一步运行，前后对比：
--1.建表 select * into tb_object from sysobjects --2.查询统计信息,发现还没有创建 select * from sys.stats where object_id = object_id('tb_object') --3.通过where条件查询1条记录 select * from tb_object where ID = 3 --4.发现,已经创建了统计信息 select * from sys.stats where object_id = object_id('tb_object') /* object_id name stats_id auto_created user_created no_recompute has_filter filter_definition 389576426 _WA_Sys_00000002_173876EA 2 1 0 0 0 NULL */ --5.这个统计信息,到底是什么东东呢? dbcc show_statistics('tb_object', --表名 _WA_Sys_00000002_173876EA --统计信息的名称 ) /* Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows _WA_Sys_00000002_173876EA 11 11 2013 1:14PM 59 59 42 1 4 NO NULL 59 All density Average Length Columns 0.01694915 4 id RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS 3 0 1 0 1 7 1 1 1 1 8 0 1 0 1 17 0 1 0 1 19 0 1 0 1 23 0 1 0 1 25 1 1 1 1 29 1 1 1 1 34 0 1 0 1 41 0 1 0 1 44 0 1 0 1 46 0 1 0 1 49 0 1 0 1 51 1 1 1 1 54 0 1 0 1 55 0 1 0 1 58 0 1 0 1 60 1 1 1 1 64 0 1 0 1 65 0 1 0 1 69 2 1 2 1 73 2 1 2 1 75 1 1 1 1 78 0 1 0 1 82 0 1 0 1 85 0 1 0 1 90 0 1 0 1 92 1 1 1 1 94 1 1 1 1 96 1 1 1 1 98 1 1 1 1 53575229 0 1 0 1 69575286 0 1 0 1 181575685 0 1 0 1 229575856 0 1 0 1 373576369 0 1 0 1 389576426 0 1 0 1 1977058079 0 1 0 1 2009058193 1 1 1 1 2041058307 1 1 1 1 2073058421 1 1 1 1 2089058478 0 1 0 1 */其实，统计信息中存放的就是一个关于某列的“统计”的信息，你看上面的输出，就是一个直方图，每个id值，会出现多少次，也就是有多少行信息，以利于优化器，做出正确的判断。
比如，id这列，假设，当你的语句是select * from tb_object where id = 3
而id = 3的记录，有100w条，而整个表的记录就500w条，那么sql server优化器知道后，可能就会采用表扫描的执行计划了。
相应的，如果id = 3的记录只有1000条，而表有100w条记录，那么sql server会考虑采用索引查找的方式，来获取数据。
--创建有主键的表,会自动建立聚集索引,自动生成索引所对应的统计信息 create table txt(id numeric(10,0) primary key, v varchar(20), vv int ) --建立非聚集索引后自动生成索引所对应的统计信息 create index txt_v on txt(v) insert into txt select object_id, type_desc, schema_id from sys.objects where LEN(type_desc) < 20 --1.1创建统计信息,通过扫描表或索引视图中的所有行来计算统计信息 create statistics txtStats on dbo.txt(v,vv) with fullscan --1.2采用为10%的行 create statistics txtStatsPercent on dbo.txt(v,vv) with sample 10 percent --1.3采样为100行 create statistics txtStatsRow on dbo.txt(v,vv) where id < 1000 --使用筛选谓词创建的统计信息 with sample 100 rows, norecompute --覆盖数据库选项选项AUTO_STATISTICS_UPDATE --查询优化器将完成statistics_name的任何正在进行中的统计信息更新 --并禁用将来的更新 --2.更新表或索引视图的统计信息 --2.1更新表中为某些列建立的统计信息 update statistics txt(txtStats) update statistics txt(txtStats) with sample 50 percent update statistics txt(txtStatsRow) with resample, --使用最近的采样速率更新每个统计信息 norecompute --查询优化器将完成此统计信息更新并禁用将来的更新 --2.2更新索引的统计信息 update statistics txt(txt_v) with fullscan --2.3更新表的所有统计信息 update statistics txt with all --2.4更新表中所有为某一些列建立的统计信息 update statistics txt(txt_v) with columns --2.5更新表中所有为索引建立的统计信息 update statistics txt(txt_v) with index /*============================================== 调用CREATE STATISTICS语句以便对于不是统计信息对象中第一列的列创建单列统计信息。 创建单列统计信息会增加直方图的数目，这可能会改进基数估计、查询计划和查询性能。 统计信息对象的第一列具有直方图；其他列不具有直方图. 在查询执行时间很重要并且不能等待查询优化器以生成单列统计信息时， sp_createstats对于基准确定之类的应用程序十分有用。 在大多数情况下，无需使用sp_createstats, 而是由查询优化器根据需要生成单列统计信息， 以便在AUTO_CREATE_STATISTICS选项为ON时改进查询计划. ========================================================*/ --3.1参数值默认都是NO. --@indexonly：仅对位于现有索引中并且不是任何索引定义中的第一列的列创建统计信息 --@fullscan： 将CREATE STATISTICS语句与FULLSCAN选项一起使用 --@norecompute：将CREATE STATISTICS语句与NORECOMPUTE选项一起使用 exec sp_createstats @indexonly ='indexonly', @fullscan ='fullscan', @norecompute = 'norecompute' --3.2对当前数据库中所有用户定义表和内部表运行UPDATE STATISTICS --将使用默认的抽样来更新统计信息 exec sp_updatestats @resample = 'no' --使用UPDATE STATISTICS语句的RESAMPLE选项来更新统计信息 exec sp_updatestats @resample = 'resample' /*==================================================== 4.显示表或索引视图的当前查询优化统计信息 查询优化器使用统计信息来估计查询结果中的基数或行数， 查询优化器就可以创建高质量的查询计划。 例如，查询优化器可以使用基数估计，在查询计划中选择索引查找运算符而不是索引扫描运算符， 避免消耗大量资源的索引扫描，提高查询性能。 对于表，统计信息是根据索引或表列的列表创建的。 统计信息包含一个带有统计信息的相关元数据的标题、 一个用于度量各列之间的相关性的密度向量、 一个带有统计信息第一个键列中的值的分布的直方图。 数据库引擎可以使用统计信息中的任何数据计算基数估计。 DBCC SHOW_STATISTICS 根据统计信息对象中存储的数据显示标题、直方图和密度向量。 使用以下语法，您可以指定表或索引视图以及target(目标索引名称、统计信息名称或列名) DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) [ WITH [ NO_INFOMSGS ] < option > [ , n ] ] < option > :: = STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM ======================================================*/ DBCC SHOW_STATISTICS('dbo.txt','txtStats') DBCC SHOW_STATISTICS('dbo.txt','vv') with HISTOGRAM --5.删除某个表的统计信息 drop statistics txt.txtStatsRow, txt.txtStatsPercent --表.统计信息名4、查询调整
create table txt(id numeric(10,0) primary key, v varchar(20), vv int ) create index txt_v on txt(v) insert into txt select object_id, type_desc, schema_id from sys.objects where LEN(type_desc) < 20 /*================================================= 1.1动态sql语句很容易导致SQL注入，与存储过程不同的是， 动态sql、常规即席查询在每次执行时都会生成新的执行计划， 所以查询的性能不稳定. ===================================================*/ exec ('select * from txt'); /*=============================================== 1.2创建可重复使用的、只有查询参数不同的查询计划, 来处理动态sql的性能问题. 参数是类型安全的,不能以非指定的数据类型使用, 也就是参数类型与字段类型必须一致,否则会报错. =================================================*/ exec sp_executesql @statement = N'select * from txt where id > @id and vv > @vv', @params = N'@id numeric(10,0), @vv int', --定义为@id int时会报错 @id = 10000, @vv = 2 --2.强制SQL Server使用特定的查询计划 set statistics xml on select * from txt where id > 1000 and vv > 2 set statistics xml off select * from txt option( use plan '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1"> <BatchSequence><Batch><Statements> <StmtSimple StatementText="SELECT * FROM [txt] WHERE [id]>@1 AND [vv]>@2" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00429444" StatementEstRows="42.2945" StatementOptmLevel="TRIVIAL" QueryHash="0xA4E0AA4B0A87F88B" QueryPlanHash="0x3325250D8A42F500"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/> <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="136"><RelOp NodeId="0" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="42.2945" EstimateIO="0.00386574" EstimateCPU="0.0004287" AvgRowSize="34" EstimatedTotalSubtreeCost="0.00429444" TableCardinality="292" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList><ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="id"/><ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="v"/> <ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="vv"/> </OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1"/> </RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues> <DefinedValue><ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="id"/> </DefinedValue><DefinedValue><ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="v"/></DefinedValue><DefinedValue> <ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="vv"/></DefinedValue></DefinedValues> <Object Database="[test2]" Schema="[dbo]" Table="[txt]" Index="[PK__txt__3213E83F4D1564AE]" IndexKind="Clustered"/> <SeekPredicates><SeekPredicateNew><SeekKeys> <StartRange ScanType="GT"><RangeColumns> <ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(numeric(10,0),[@1],0)"><Identifier> <ColumnReference Column="ConstExpr1003"><ScalarOperator> <Convert DataType="numeric" Precision="10" Scale="0" Style="0" Implicit="1"> <ScalarOperator><Identifier><ColumnReference Column="@1"/> </Identifier></ScalarOperator></Convert></ScalarOperator> </ColumnReference></Identifier></ScalarOperator> </RangeExpressions></StartRange></SeekKeys></SeekPredicateNew> </SeekPredicates><Predicate><ScalarOperator ScalarString="[test2].[dbo].[txt].[vv]>CONVERT_IMPLICIT(int,[@2],0)"><Compare CompareOp="GT"> <ScalarOperator><Identifier><ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="vv"/></Identifier></ScalarOperator><ScalarOperator> <Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator> <Convert DataType="int" Style="0" Implicit="1"><ScalarOperator> <Identifier><ColumnReference Column="@2"/></Identifier> </ScalarOperator></Convert></ScalarOperator></ColumnReference> </Identifier></ScalarOperator></Compare></ScalarOperator> </Predicate></IndexScan></RelOp><ParameterList> <ColumnReference Column="@2" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"/> <ColumnReference Column="@1" ParameterCompiledValue="(1000)" ParameterRuntimeValue="(1000)"/></ParameterList></QueryPlan> </StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>') --3.不修改应用程序，应用查询提示 exec sp_create_plan_guide @name = 'plan_guide_txt', @stmt = 'select * from txt inner join sys.objects o on o.object_id = txt.id', @type = 'sql', @module_or_batch = null, @params = null, @hints = 'option(merge join)' --当下面的这段代码与上面的代码完全一样(包括空格,回车)时,系统才会应用查询提示 select * from txt inner join sys.objects o on o.object_id = txt.id --4.从缓存创建计划指南 begin select * from txt inner join sys.objects o on o.object_id = txt.id select * from sys.objects end go --找到计划句柄 select e.text, d.statement_start_offset, d.plan_handle --计划句柄 from sys.dm_exec_query_stats d cross apply sys.dm_exec_sql_text(d.sql_handle)e where e.text like '%from txt%' /*======================================================== 从计划句柄建立计划指南 参数@statement_start_offset：在批处理中可能会有多条语句， 此参数指出批处理中创建计划指南的语句，在批处理中的开始偏移。 如果指定为NULL，那么会为批处理中的所有语句建立计划指南 ==========================================================*/ exec sp_create_plan_guide_from_handle @name = 'plan_guide_txt_handle', @plan_handle = 0x060018007BA32F074021425A020000000000000000000000, @statement_start_offset = 14 --5.使用计划指南把非参数化查询参数化 --5.1运行多条类似的查询 select * from txt where id = 8 select * from txt where id = 9 --5.2通过查询缓存的计划所对应的sql文本，发现很多都是相同的 --而且大部分的objtype都是proc,adhoc,prepared. SELECT * FROM SYS.dm_exec_cached_plans E CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE WHERE EE.text LIKE '%select * from txt where id =%' declare @sqltext nvarchar(max) declare @parameter nvarchar(max) --5.3获取查询的参数化形式以及查询的参数,放入变量中 exec sp_get_query_template @querytext = N'select * from txt where id = 8', @templatetext= @sqltext output, @parameters = @parameter output --5.4使用模板来创建计划指南 exec sp_create_plan_guide @name = 'plan_guide_txt_template', @stmt = @sqltext, @type = 'template', @module_or_batch = null, @params = @parameter, @hints = 'option(parameterization forced)' --5.5再次查询发现多条执行计划已经变为一条,usecounts计数增加 SELECT * FROM SYS.dm_exec_cached_plans E CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE WHERE EE.text LIKE '%select * from txt where id =%' --6.显示计划指南的元数据 select p.plan_guide_id, p.name, p.create_date, p.modify_date, p.is_disabled, --是否禁用计划指南 p.query_text, --创建计划指南所依据的查询文本 p.scope_type, --3个类型:object,sql,template p.scope_type_desc, p.scope_object_id , --如果类型为object,那么指出对象id scope_batch, --如果scope_type为SQL，则为批处理文本。 --如果批处理类型不是SQL，则其值为NULL p.parameters, --定义与计划指南关联的参数列表的字符串 p.hints --与计划指南关联的 OPTION 子句提示 from sys.plan_guides p /*============================================================= 7.验证指定计划指南的有效性。返回计划指南应用于其查询时遇到的第一条错误消息。 如果计划指南有效，则将返回一个空的行集。在更改数据库的物理设计后，计划指南可能会变为无效。 例如，如果计划指南指定了特定索引并且随后将该索引删除，则查询将不能再使用该计划指南。 通过验证计划指南，可确定优化器是否能够在不进行修改的情况下直接使用该指南。 例如，基于函数的结果，可决定删除该计划指南并重新调整查询或修改数据库设计, 例如，重新创建计划指南中指定的索引。 ===============================================================*/ select pp.msgnum, --错误消息的 ID pp.severity, --消息的严重级别 pp.state, --错误的状态号，用于指示发生错误的代码位置 pp.message --错误的消息正文 from sys.plan_guides p cross apply sys.fn_validate_plan_guide(p.plan_guide_id) pp /*============================================== 8.删除或者禁用计划指南 disable,disable all:禁用(所有)计划指南 enable,enable all:启用(所有)计划指南 drop,drop all:删除(所有)计划指南 注意：当指定all时，不能指定计划指南名称。 ================================================*/ exec sp_control_plan_guide @operation = 'disable', --禁用计划指南 @name = 'plan_guide_txt' --删除所有计划指南 exec sp_control_plan_guide @operation = 'drop all'
5、资源调控
在SQL Server 2008中引入了使用资源调控器来限制工作负荷的资源消耗。在SQL Server内部包含了两个资源池：默认和内部。内部资源池不能修改，并且使SQL Server的活动不受资源的限制。默认资源池可以把连接请求，连接、配置资源调控器，默认情况下没有限制，之后可以修改。
一个或多个工作组可以绑定到一个资源池，使用工作负荷组可以定义资源池中重要的请求、最大授予内存比、最大以秒为单位的cpu时间、最大授予内存超时时间、最大并行度、同时执行的请求的最大数量。工作负荷组也包含：内部和默认工作组。默认工作组用在没有被任何分类器用户定义函数覆盖的任何请求上。多个工作负荷组可以关联到一个资源池，但是一个工作负荷组不能关联到多个资源池。
在创建用户定义工作负荷组和绑定到资源池后，可以创建一个帮助确定：进入的SQL Server连接和请求属于哪个工作负荷组的分类器用户定义函数。
--1.创建资源池 --创建应用程序资源池 create resource pool app_query with ( MIN_CPU_PERCENT = 25, MAX_CPU_PERCENT = 75, MIN_MEMORY_PERCENT = 25, MAX_MEMORY_PERCENT = 75 ) --创建即席查询资源池 CREATE RESOURCE POOL adhoc_query with ( min_cpu_percent = 5, max_cpu_percent = 25, min_memory_percent = 5, max_memory_percent = 25 ) --修改即席查询资源池 alter resource pool adhoc_query with ( min_memory_percent = 10, max_memory_percent = 50 ) --2.创建工作负荷组 --创建应用程序a的工作负荷组 create workload group app_a with ( importance = high, request_max_memory_grant_percent = 75, request_max_cpu_time_sec = 75, request_memory_grant_timeout_sec = 120, max_dop = 8, group_max_requests = 8 ) using app_query --使用应用程序资源池 --创建应用程序b的工作负荷组 create workload group app_b with ( importance = low, request_max_memory_grant_percent = 50, request_max_cpu_time_sec = 50, request_memory_grant_timeout_sec = 360, max_dop = 1, group_max_requests = 4 ) using app_query --使用应用程序资源池 --创建即席查询工作负荷组 create workload group adhoc_user with ( importance = low, request_max_memory_grant_percent = 100, request_max_cpu_time_sec = 120, request_memory_grant_timeout_sec = 360, max_dop = 1, group_max_requests = 5 ) using adhoc_query --修改工作负荷组 alter workload group app_b with ( importance = medium ) --3.分类器函数必须在master数据库中创建 use master go --返回工作负荷组名称，系统会根据工作组名称，将连接定位到工作组所对应的资源池 create function dbo.wc_classifier() returns sysname with schemabinding as begin declare @resource_group_name sysname if SUSER_SNAME() in ('sa') set @resource_group_name = 'app_a' if SUSER_SNAME() in ('sa') set @resource_group_name = 'app_b' if HOST_NAME() in ('abc') set @resource_group_name = 'adhoc_query' if @resource_group_name is null set @resource_group_name = 'default' return @resource_group_name end go --设置资源调度器的分类器函数，并且重新配置 alter resource governor with ( classifier_function = dbo.wc_classifier ) go --为了启用配置，需要执行reconfigure alter resource governor reconfigure go --4.查看元数据 --资源池的元数据 select pool_id, min_cpu_percent, max_cpu_percent, min_memory_percent, max_memory_percent from sys.resource_governor_resource_pools --负荷工作组元数据 select group_id, name, pool_id, importance, request_max_memory_grant_percent, request_max_cpu_time_sec, request_memory_grant_timeout_sec, max_dop, group_max_requests from sys.resource_governor_workload_groups --显示资源调控器的配置信息,包含了:分类器函数,是否启用 select classifier_function_id, OBJECT_NAME(classifier_function_id, DB_ID('master') ), --函数名 is_enabled --是否启用 from sys.resource_governor_configuration --5.删除资源池，工作负荷组，分类器函数 use master go drop workload group app_a drop workload group app_b drop workload group adhoc_user drop resource pool adhoc_query drop resource pool app_query --禁用设置 alter resource governor disable --设置不在使用分类器函数 alter resource governor with ( classifier_function = null ) --删除分类器函数 drop function dbo.wc_classifier
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
