拓狼
-
个人空间
- 组别:管理员
- 性别:
- 来自:北京 海淀
- 积分:1121
- 帖子:619
- 注册:
2007-05-23
|
怎样分辨SQLServer不起作用的索引
怎样分辨SQLServer不起作用的索引--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址
作为DBA，检查数据库看是否有任何提升性能的方法是我们的常规工作。虽然给数据库添加新的或更好的索引是我们能提升性能的主要方法，然而帮助提升数据库性能的一个经常被遗忘的方法是删除那么不再被使用的索引，或者找出花费大量SQLServer资源的过度庞大的索引。不起作用的索引会降低INSERT、UPDATE和DELETE的性能，增加不必要的磁盘I/O。所以为了更好的性能，我们要随时删除不必要的索引。 另外，经常分辨非常大的索引看看它们是否被正确使用是一个好的想法。一个缺乏设计又大的索引也能引起你有不起作用的索引时看到的同类问题。 问题在于，我们怎么知道哪个索引有用哪个没用呢？怎样能轻易分辨出很大的索引呢？在这一点上，SQLServer没有提供太多自动化的方法，要分辨不起作用或非常大的索引常常必须依靠我们自己。 本文提及的分辨不起作用和很大索引的方法是简单的，但要达到目的却需要时间和耐心。 方法 我们的关键工具是事件探查器。我们必须使用它来收集至少4、5天的数据库活动。虽然这个时间段不是至关重要的，但它有助于确保很少使用的索引不被标识出来从而被意外地删掉。 我们使用事件探查器必须收集的事件有： Stored Procedures RPC:Completed SP:StmtCompleted TSQL SQL:BatchCompleted SQL:StmtCompleted 至于列，建议收集的有： ApplicationName EventClass TextData Duration LoginName Reads NTUserName 为了帮助减少随着时间的推移事件探查器跟踪所收集的事件数量，建议你仅仅收集那些duration为50毫秒或更长的事件。这仍然会产生相当大的文件，所以你要确保不要把这些数据存在SQLServer所在的服务器上，也要为存储跟踪文件留足空间。 产生用到的索引的报告 接下来，要使用索引调优向导分析跟踪文件。确保使用“Keep all existing indexes”选项并使用“Thorough”调优模式。在“Advanced Options”高级选项下，不选“Limit number of workload queries to sample”。 正如你所料，运行这个分析将对你的SQLServer产品服务器带来负荷。确保在不是很忙的时候运行。 一旦索引调优向导完成了，我们要看看索引使用报告，如下所示。注意这个报告告诉我们索引是否有用，它也告诉我们每个索引的大小。  此时，我们可以停止使用这些结果作为我们的基础来移除未使用的索引和分辨大索引。但如果有大量要处理的表，那么你可以考虑下面另外的步骤。 假定我们要使这些另外的分析自动化，我们所要做的就是使用保存按钮用文本格式来保存这个报告，然后将这个文本文件导入到SQLServer的表里。 导入报告到SQLServer表里 选择任意一个数据库，创建下面的表： CREATE TABLE [dbo].[Analysis ] ([ Table ] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, [ Indice ] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, [ Uso ] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, [ Peso ] int NOT NULL, [ Base ] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, [ Date ] datetime NOT NULL ) ON [PRIMARY] 一旦创建了表，就可以使用DTS导入向导来把文本文件导入到表里了。当导入这个文本文件时，选择TAB作为列分隔符，表Analysis为目标表。 一般导入了文本文件，就能使用下面的存储过程来列出不起作用的索引了。 CREATE PROCEDURE Indices_Inactivos AS declare it @Base varchar(15), @Tabla varchar(50), @Indice varchar(100), @BaseAnt varchar(15), @TablaAnt varchar(50) IndicesInactivos declares for cursor select Base, Table, Indice from Analysis where Indice like ' [ [ ]IX% ' group by Base, Table, Indice having sum(convert(money, replace(Uso,',','.'))) = 0 they open IndicesInactivos fetch next from IndicesInactivos into @Base, @Tabla, @Indice while (@@FETCH_STATUS < > -1) begin if @Base < > @BaseAnt begin Print ' BASES: ' + @Base select @BaseAnt = @Base select @TablaAnt = '' end if @Tabla < > @TablaAnt begin Print ' TABLE: ' + @Tabla Print ' INDICES: ' select @TablaAnt = @Tabla end Print ' ' + @Indice fetch next from IndicesInactivos into @Base, @Tabla, @Indice continue end close IndicesInactivos deallocate IndicesInactivos GO 在查询分析器里不带参数执行这个过程。输出如下的未使用索引的报告： BASE: EXAMPLE TABLE: [ dbo].[APLICACIONES ] INDICES: [ IXC03.10.31_APLICACIONES_NroTrans ] TABLE: [ dbo].[AWItemsAcumHistoricos ] INDICES: [ IX_AWItemsAcumHistoricosFecha ] [ IX_AWItemsAcumHistoricosItem ] [ IXC_AWItemsAcumHistoricos_Fecha_CodItm ] TABLE: [ dbo].[CAJASREG ] INDICES: [ IXCP04.01.16_CAJASREG_CodCaj2_NroTrans ] TABLE: [ dbo].[CHEQUES ] INDICES: [ IXC04.01.09_CHEQUES_FechaVto ] [ IXCP04.01.16_CHEQUES_CodCtacte_NroTrans_Secuencia_NroTransegr_Tipo_Directo ] 下面是另一个存储过程。它用来列出在跟踪里使用的索引。用这些结果来列出那些非常大的索引。 CREATE PROCEDURE Indices_Usados AS declare it @Base varchar(15), @Tabla varchar(50), @Indice varchar(100), @BaseAnt varchar(15), @TablaAnt varchar(50), @Uso varchar(50) IndicesUsados declares for cursor select Base, Table, Indice, to convert(varchar, max(convert(money, replace(Uso, ', ', '.'))))as Uso from Analysis where Indice like ' [ [ ]IX% ' group by Base, Table, Indice having sum(convert(money, replace(Uso,',','.'))) > 0 Order by Base, Table, to convert(varchar, max(convert(money, replace(Uso,',','.')))) desc, Indice they open IndicesUsados fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso while (@@FETCH_STATUS < > -1) begin if @Base < > @BaseAnt begin Print ' BASES: ' + @Base select @BaseAnt = @Base select @TablaAnt = '' end if @Tabla < > @TablaAnt begin Print ' TABLE: ' + @Tabla Print ' INDICES: ' select @TablaAnt = @Tabla end if len(@Uso) = 4 begin select @Uso = ' 0 ' + @Uso end Print @Uso + ' ' + @Indice fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso continue end close IndicesUsados deallocate IndicesUsadosGO 一旦执行了，将包含下面的报告。例如： BASE: EXAMPLE TABLE: [ dbo].[APLICACIONES ] INDICES: 79.20 [ IXCY04.05.03_APLICACIONES_NroTransegr_NroTrans_RefVto ] 33.30 [ IXCY04.05.03_APLICACIONES_Aplicaciones_NroTransegr_RefVto_NroTrans_FechaVto_Importe ] 20.50 [ IXC03.11.24_APLICACIONES_NroTransegr_AplNrotrans_AplRefvto ] 02.10 [ IXCP03.11.12_APLICACIONES_Nrotrans_NroTransegr_Importe_FechaVto_AplrefVto ] 100.00 [ IXC04.05.27_APLICACIONES_NroTrans_NroTranselim_AplNroTrans_FechaVto_AplRefvto ] 00.10 [ IXC04.05.27_APLICACIONES_AplNrotrans ] 00.10 [ IXCY04.05.07_APLICACIONES_AplNrotrans_AplRefvto_FechaVto_NroTrans_NroTransing ] TABLE: [ dbo].[AWItemsAcumHistoricos ] INDICES: 00.10 [ IXC03.05.16_AWItemsAcumHistoricos_CodItm_Fecha ] TABLE: [ dbo].[BANCOSCOD ] INDICES: 100.00 [ IXCY04.05.14_BANCOSCOD_CodBan_Descripcion ] 两个结果都重要。第一个告诉我们哪一个索引未使用，第二个列出每个索引的大小，或许给我们一个线索任何索引都要倍加小心的检查。尺寸非常大的索引也许表明有潜在的压力去降低速度。 综述
一旦删除了所有不必要或过大的索引，你要紧盯你数据库的性能，以确保你删掉的索引是真正需要的，但又很少用到以至于在你的跟踪里没有显示。
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。
|
