Which indexes are NOT used in MSSQL
- Good performance management is making sure that you have the right indexes.
- Indexes will significantly speed database operations if used properly.
- Indexes significantly [that's an understatement] improve the speed of the SQL Server engine, but that's only if you have the right indexes.
- What about the indexes that are not used?
- The unused indexes add overhead to the SQL Server engine with extra disk space used by these indexes and disk IO by updating and maintaining these indexes that are not used.
- sys.dm_db_index_usage_stats keeps track of which indexes are used.
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Indexes that are NOT used
USE sql911;
go
SELECT CAST(OBJECT_NAME(idx.OBJECT_ID) AS VARCHAR(30)) AS 'Object',
CAST(idx.name AS VARCHAR(50)) AS 'Index',
idx.index_id AS 'Id',
CAST(idx.type_desc AS VARCHAR(20)) AS 'Type',
CAST(col.index_columns AS VARCHAR(80)) AS 'Columns'
FROM sys.indexes idx LEFT outer join sys.dm_db_index_usage_stats usg
ON usg.OBJECT_ID=idx.OBJECT_ID and idx.index_id=usg.index_id
and usg.database_id = DB_ID()
LEFT outer join
(SELECT DISTINCT OBJECT_ID, index_id,
STUFF((SELECT ','+COL_NAME(OBJECT_ID,column_id ) AS 'data()' FROM sys.index_columns idx_col2 WHERE idx_col1.OBJECT_ID = idx_col2.OBJECT_ID and idx_col1.index_id = idx_col2.index_id FOR XML PATH ('')),1,1,'')
AS 'index_columns' FROM sys.index_columns idx_col1 ) col ON
col.index_id = idx.index_id and col.OBJECT_ID = idx.OBJECT_ID
WHERE OBJECTPROPERTY(idx.OBJECT_ID, 'IsIndexable') = 1
and usg.index_id IS null and idx.type_desc not in ('heap','clustered')
go
Object Index Id Type Columns
------------------------------ -------------------------------------------------- ----------- -------------------- --------------------------------------------------------------------------------
(0 row(s) affected)
- Oops!, no result.
- All indexes defined are actually used.
- sys.dm_db_index_usage_stats only keeps track of what indexes are used since the start [the boot] of the SQL Server.
- Shutting down SQL Server will reset all the index usages in sys.dm_db_index_usage_stats to 0 [zero].
Indexes that are NOT used [again]
USE adventureworkslt;
go
SELECT CAST(OBJECT_NAME(idx.OBJECT_ID) AS VARCHAR(30)) AS 'Object',
CAST(idx.name AS VARCHAR(50)) AS 'Index',
idx.index_id AS 'Id',
CAST(idx.type_desc AS VARCHAR(20)) AS 'Type',
CAST(col.index_columns AS VARCHAR(80)) AS 'Columns'
FROM sys.indexes idx LEFT outer join sys.dm_db_index_usage_stats usg
ON usg.OBJECT_ID=idx.OBJECT_ID and idx.index_id=usg.index_id
and usg.database_id = DB_ID()
LEFT outer join
(SELECT DISTINCT OBJECT_ID, index_id,
STUFF((SELECT ','+COL_NAME(OBJECT_ID,column_id ) AS 'data()' FROM sys.index_columns idx_col2 WHERE idx_col1.OBJECT_ID = idx_col2.OBJECT_ID and idx_col1.index_id = idx_col2.index_id FOR XML PATH ('')),1,1,'')
AS 'index_columns' FROM sys.index_columns idx_col1 ) col ON
col.index_id = idx.index_id and col.OBJECT_ID = idx.OBJECT_ID
WHERE OBJECTPROPERTY(idx.OBJECT_ID, 'IsIndexable') = 1
and usg.index_id IS null and idx.type_desc not in ('heap','clustered')
go
Object Index Id Type Columns
------------------------------ -------------------------------------------------- ----------- -------------------- --------------------------------------------------------------------------------
Address AK_Address_rowguid 2 NONCLUSTERED rowguid
CustomerAddress AK_CustomerAddress_rowguid 2 NONCLUSTERED rowguid
Product AK_Product_rowguid 2 NONCLUSTERED rowguid
ProductCategory AK_ProductCategory_rowguid 2 NONCLUSTERED rowguid
ProductDescription AK_ProductDescription_rowguid 2 NONCLUSTERED rowguid
ProductModel AK_ProductModel_rowguid 2 NONCLUSTERED rowguid
ProductModelProductDescription AK_ProductModelProductDescription_rowguid 2 NONCLUSTERED rowguid
SalesOrderDetail AK_SalesOrderDetail_rowguid 2 NONCLUSTERED rowguid
SalesOrderHeader AK_SalesOrderHeader_rowguid 2 NONCLUSTERED rowguid
Address IX_Address_AddressLine1_AddressLine2_City_StatePro 3 NONCLUSTERED AddressLine1 ,AddressLine2 ,City ,StateProvince ,PostalCode ,CountryRegion
Customer AK_Customer_rowguid 3 NONCLUSTERED rowguid
Product AK_Product_Name 3 NONCLUSTERED Name
ProductCategory AK_ProductCategory_Name 3 NONCLUSTERED Name
ProductModel AK_ProductModel_Name 3 NONCLUSTERED Name
SalesOrderDetail IX_SalesOrderDetail_ProductID 3 NONCLUSTERED ProductID
SalesOrderHeader AK_SalesOrderHeader_SalesOrderNumber 3 NONCLUSTERED SalesOrderNumber
Address IX_Address_StateProvince 4 NONCLUSTERED StateProvince
Customer IX_Customer_EmailAddress 4 NONCLUSTERED EmailAddress
Product AK_Product_ProductNumber 4 NONCLUSTERED ProductNumber
SalesOrderHeader IX_SalesOrderHeader_CustomerID 5 NONCLUSTERED CustomerID
ProductModel PXML_ProductModel_CatalogDescription 32000 XML CatalogDescription
(21 row(s) affected)
- If those indexes are not used, do you need them? and more important should you drop them?
- Before making that decision:
- You should NOT drop anything after a reboot/start/shutdown, since sys.dm_db_index_usage_stats get reset after a reboot/start/shutdown.
- You should rerun that query at regular interval to see the real usage.
- You should wait for after important events such as: month-end and year-end!
