主页 / dba / 问题 / 340914
Accepted
Anand Sowmithiran
Asked: 2024-07-12 22:42:52 +0800 CST

自动创建索引 - 太多

  • 772

当我们将自动调整设置为 ON 以创建索引时，Azure SQL Hyperscale db 已为我们的一些大型表创建了大约 10 个索引。在分析索引时，其中一些仅在“包含”列中有所不同，而索引列相同。这种索引激增将损害此类表上的 INSERT、UPDATE 操作。

Azure 中是否有合并/优化索引的选项，而不是我们手动删除并重新创建许多表的更好索引？截至目前，关闭自动索引创建似乎是唯一可行的方法。

还有其他建议吗？

performance

2 个回答

  1. Best Answer

    在分析索引时，有些索引仅在“包含”列上有所不同，而索引列相同

    如果您根本没有任何 DBA（开发人员、系统管理员等），那么此自动索引功能会很有用，DBA 可以通过检查缺失的索引 DMV 并确定如何将建议的索引组合组合成对大多数查询有益的索引来手动维护索引。

    Azure 中是否有合并/优化索引的选项，而不是我们手动删除并重新创建许多表的更好的索引？

    不幸的是，该功能目前还不够智能，无法完成“人性化”的工作。它会创建索引，正如您提到的那样，“其中一些仅在‘包含’列上有所不同，而索引列相同”。

    如果您觉得它创建的许多索引确实损害了 INSERT 和 UPDATE 操作，或者使用了太多的存储空间，请关闭该功能并手动维护（创建、修改、监控使用情况、删除）索引。

    • 3

  2. 使用此查询来查找使用最多读取的索引：

    --- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 45) (Overall Index Usage - Reads)
SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName]
    ,OBJECT_NAME(i.[object_id]) AS [ObjectName]
    ,i.[name] AS [IndexName]
    ,i.index_id
    ,s.user_seeks
    ,s.user_scans
    ,s.user_lookups
    ,s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads]
    ,s.user_updates AS [Writes]
    ,i.[type_desc] AS [Index Type]
    ,i.fill_factor AS [Fill Factor]
    ,i.has_filter
    ,i.filter_definition
    ,s.last_user_scan
    ,s.last_user_lookup
    ,s.last_user_seek
    ,'Show which indexes in the current database are most active for Reads' AS Info
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC
OPTION (RECOMPILE);-- Order by reads
    ------
    -- Show which indexes in the current database are most active for Reads

    然后这样找到使用最多写入的索引：

    --- Index Read/Write stats (all tables in current DB) ordered by Writes  (Query 46) (Overall Index Usage - Writes)
SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName]
    ,OBJECT_NAME(i.[object_id]) AS [ObjectName]
    ,i.[name] AS [IndexName]
    ,i.index_id
    ,s.user_updates AS [Writes]
    ,s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads]
    ,i.[type_desc] AS [Index Type]
    ,i.fill_factor AS [Fill Factor]
    ,i.has_filter
    ,i.filter_definition
    ,s.last_system_update
    ,s.last_user_update
    ,'Show which indexes in the current database are most active for Writes' AS Info
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY s.user_updates DESC
OPTION (RECOMPILE);-- Order by writes
    ------
    -- Show which indexes in the current database are most active for Writes

    您认为缺少索引吗？使用此查询查找缺少的索引：

    -- http://www.dharmendrakeshari.com/find-missing-indexes-sql-server-databases/

SELECT 
    migs.user_seeks as [Estimated Index Uses],
    migs.avg_user_impact [Estimated Index Impact %],
    migs.avg_total_user_cost[Estimated Avg Query Cost],     
    db_name(mid.database_id) AS DatabaseID,
    OBJECT_SCHEMA_NAME (mid.OBJECT_ID,mid.database_id) AS [SchemaName],
    OBJECT_NAME(mid.OBJECT_ID,mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID,mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') 
    + CASE
            WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL 
            THEN '_'
            ELSE ''
      END
    + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + mid.statement
    + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns 
    IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [Create TSQL],
    mid.equality_columns, 
    mid.inequality_columns, 
    mid.included_columns,
    migs.unique_compiles,
    migs.last_user_seek 
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
ORDER BY [Estimated Index Uses] DESC OPTION (RECOMPILE);

    不要在数据库中放置随机索引。他们告诉你人工智能可以自动为你做到这一点。但是这个应该找到正确缺失索引的 Azure 功能一直存在。这种人工智能现象很久以前就开始了。请放心，它永远不会帮助你。

    • 0

相关问题