Suggest optimal indexes for a T-SQL query or table based on query patterns
Generates optimized T-SQL index recommendations by analyzing query patterns and table structures.
/plugin marketplace add JosiahSiegel/claude-plugin-marketplace/plugin install tsql-master@claude-plugin-marketplace[query or table name]Generate index recommendations for T-SQL queries or tables.
If input is a query:
If input is a table name:
For each table in query:
Equality Predicates (=)
Inequality Predicates (<, >, BETWEEN, LIKE 'x%')
JOIN Columns
ORDER BY Columns
SELECT Columns
For each recommended index:
-- Purpose: [What queries this index supports]
-- Expected benefit: [Seek vs scan, eliminated lookup, etc.]
CREATE NONCLUSTERED INDEX IX_TableName_Columns
ON SchemaName.TableName (KeyColumn1, KeyColumn2)
INCLUDE (IncludeColumn1, IncludeColumn2)
WHERE [FilterCondition]; -- If filtered index recommended
Query to check existing indexes:
SELECT
i.name AS IndexName,
i.type_desc,
STUFF((
SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 2, '') AS KeyColumns,
STUFF((
SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 2, '') AS IncludedColumns
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('TableName')
ORDER BY i.index_id;
SELECT
migs.avg_user_impact AS ImpactPercent,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks,
migs.user_scans
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
AND OBJECT_NAME(mid.object_id) = 'TableName'
ORDER BY migs.avg_user_impact DESC;
================================
Index Recommendations
================================
TABLE: dbo.Orders
QUERY PATTERN ANALYSIS:
- Filters: CustomerID (=), OrderDate (range)
- Joins: CustomerID -> Customers.CustomerID
- Order: OrderDate DESC
- Selects: OrderID, Amount, Status
RECOMMENDED INDEXES:
1. PRIMARY INDEX (High Impact)
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerID, OrderDate DESC)
INCLUDE (Amount, Status);
Benefit: Covers filter + order + select columns
Eliminates: Table scan, sort operation, key lookup
2. FILTERED INDEX (Medium Impact)
CREATE NONCLUSTERED INDEX IX_Orders_Active_Customer
ON dbo.Orders (CustomerID, OrderDate)
WHERE Status = 'Active';
Benefit: Smaller index for common filter pattern
EXISTING INDEX CONFLICTS:
- IX_Orders_CustomerID overlaps with recommendation 1
Consider: DROP or keep if other queries need it
MAINTENANCE IMPACT:
- New indexes will slow INSERT/UPDATE by ~5-10%
- Recommended maintenance: Weekly REORGANIZE
================================
# From query
/suggest-indexes SELECT * FROM Orders WHERE CustomerID = @ID ORDER BY OrderDate
# From table name
/suggest-indexes Orders
# Multiple tables
/suggest-indexes "Orders JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID"