主页 / dba / 问题 / 18943
Accepted
mehdi lotfi
Asked: 2012-06-08 04:59:37 +0800 CST

识别所需索引的工具

  • 772

我想为我的数据库中的每个表创建最佳索引。SQL Server 中是否有查询或工具可帮助完成此过程？

sql-server database-design

3 个回答

  1. Best Answer

    是的，有一些工具，比如数据库引擎优化顾问。它随 SQL Server 一起提供，并做了一些非常讨厌的事情：

    • 它允许您只考虑非常小的工作负载（甚至是单个查询）。然后，它会建议可能只帮助小工作负载的索引，而不考虑其他工作负载（索引有助于加快某些查询，但它们可以减慢其他查询，尤其是插入/更新/删除），别管其余的的商业周期。
    • 它通常会推荐仅因尾随列或包含列而异的冗余索引。
    • 它真的，真的，真的很喜欢包含的专栏——我认为这可能是一种迷信。

    索引调优的正确方法是考虑整个业务周期内的整个工作负载。有时，使用 3rd 方工具比重新发明轮子更好。DBSophic制作了一个名为 Qure Workload Analyzer 的免费产品，我认为它比 DTA 做得更好——但他们的许可工具（Qure Workload Optimizer）在它的作用上绝对是惊人的。去年，我在博客中介绍了该工具与 SQL Sentry 集成时的工作原理）。

    还有一些其他选项，例如 DMV 中报告缺少索引，您还可以发现正在写入哪些索引（因此它们占用空间并影响插入/更新/删除操作）但从不（或很少) 用户查询使用：

    但这些也需要注意，DMV 仅在上次 SQL Server 重新启动后才有效，并且它们不一定涵盖适当的业务周期（因此可能会丢失重要信息

    • 15

  2. 您所说的工具称为数据库引擎优化顾问（简称 DTA）。它可以接受大量输入，并就缺失索引等问题提供建议。但值得注意的是，这些只是建议，应慎重考虑，而不是盲目遵循 DTA 的建议。

    DTA 教程链接

    还值得注意的是，有用于收集此类信息的内置 DMV：

    • 6

  3. 这听起来好得令人难以置信，但我发现一个查询正是这样做的：

    USE YourDatabase
GO

SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);
    • 0

相关问题