Auto-Indexing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Enable automatic tuning for a database
ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING
(
    CREATE_INDEX = ON,
    DROP_INDEX = ON
);
 
-- View automatic tuning recommendations
SELECT
    name,
    reason,
    score,
    valid_since,
    revert_action_sql,
    create_index_sql
FROM sys.dm_db_tuning_recommendations
WHERE type = 'CREATE_INDEX'
ORDER BY score DESC;
 
-- View automatic index actions history
SELECT
    object_name,
    index_action_start_time,
    state_desc,
    index_type_desc,
    index_name,
    estimated_space_change_kb
FROM sys.dm_db_automatic_index_stats
ORDER BY index_action_start_time DESC;
 
-- Check if index is performing as expected
SELECT
    index_name,
    user_seeks,
    user_scans,
    user_lookups,
    avg_estimated_impact,
    revert_action_sql
FROM sys.dm_db_automatic_index_stats
WHERE type = 'Automatic'
  AND datediff(day, index_action_start_time, GETDATE()) < 7;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- Step 1: Get the recommendation
-- (Using SQL Server missing index DMVs as example)
SELECT TOP 5
    improvement_measure,
    statement,
    equality_columns,
    inequality_columns,
    included_columns
FROM (
    SELECT
        CONVERT(DECIMAL(10,2), migs.avg_total_user_cost * migs.avg_user_impact *
                (migs.user_seeks + migs.user_scans)) AS improvement_measure,
        mid.statement,
        mid.equality_columns,
        mid.inequality_columns,
        mid.included_columns
    FROM sys.dm_db_missing_index_group_stats migs
    JOIN sys.dm_db_missing_index_groups mig
        ON migs.group_handle = mig.index_group_handle
    JOIN sys.dm_db_missing_index_details mid
        ON mig.index_handle = mid.index_handle
) AS recommendations
ORDER BY improvement_measure DESC;
 
-- Step 2: Find the queries driving this recommendation
-- Look in Query Store for queries matching the table
SELECT TOP 10
    qt.query_sql_text,
    rs.count_executions,
    rs.avg_duration / 1000000.0 AS avg_duration_s,
    rs.avg_cpu_time / 1000000.0 AS avg_cpu_s,
    rs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%YourTableName%'
ORDER BY rs.count_executions * rs.avg_duration DESC;
 
-- Step 3: Check existing indexes on the table
SELECT
    i.name AS index_name,
    i.type_desc,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns,
    i.is_unique,
    i.is_primary_key
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('YourTableName')
  AND i.index_id > 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key
ORDER BY i.index_id;
 
-- Step 4: Estimate new index size
SELECT
    SUM(datalength(equality_column) + datalength(inequality_column)) * row_count / 0.85 / 8192 * 8
        AS estimated_size_kb
FROM (
    SELECT
        -- Substitute actual column names
        customer_id AS equality_column,
        order_date AS inequality_column,
        1 AS row_count
    FROM YourTableName
) AS estimation;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

# Weekly index recommendation report
# Run as scheduled task or part of monitoring pipeline
 
param(
    [string]$Server = "your-server",
    [string]$Database = "your-database",
    [int]$TopN = 20,
    [string]$OutputPath = "C:\Reports\IndexRecommendations"
)
 
$date = Get-Date -Format "yyyy-MM-dd"
$reportFile = Join-Path $OutputPath "IndexReport_$date.html"
 
$missingIndexQuery = @"
SELECT TOP $TopN
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact *
          (migs.user_seeks + migs.user_scans), 2) AS improvement_score,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_score DESC
"@
 
$unusedIndexQuery = @"
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS total_reads,
    ius.user_updates AS total_writes,
    ps.row_count,
    CAST(ps.used_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 1  -- Skip heaps and clustered
  AND ius.user_seeks + ius.user_scans + ius.user_lookups = 0
  AND ius.user_updates > 1000
ORDER BY ius.user_updates DESC
"@
 
# Execute queries and generate report
$missing = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $missingIndexQuery
$unused = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $unusedIndexQuery
 
# Generate HTML report (simplified)
$html = @"
<html>
<head><title>Index Recommendations - $date</title></head>
<body>
<h1>Index Recommendations Report</h1>
<h2>Missing Index Recommendations</h2>
$($missing | ConvertTo-Html -Fragment)
<h2>Unused Indexes (Candidates for Removal)</h2>
$($unused | ConvertTo-Html -Fragment)
</body>
</html>
"@
 
$html | Out-File $reportFile
Write-Host "Report generated: $reportFile"