How many total index is possible to create?
SQL Server allows you to create up to 999 Non-clustered indexes and one Clustered indexes per each table. But it is not allowed to create huge number of index.
indexes help enhancing the performance of a large number of queries that try to retrieve data from the database table. The drawbacks of having too many indexes in the table include slowing down the data modification operations, due to the fact that all changes performed on the table should be replicated to the related indexes. In addition, this large number of indexes require extra storage and should be all maintained, although some of these indexes are not used, hurting the overall performance, instead of the expected enhancement from it.
SQL Server provides us with the sys.dm_db_index_usage_stats dynamic management view, that helps in tracking the usage of the database indexes since the last SQL Server service restart.
The T-SQL script below uses the sys.dm_db_index_usage_stats DMV, along with other system catalog views, to return a meaningful and useful cumulative information about each index and its usage since the last restart. This information includes the name of the table, on which the index is created, the name and type of that index, the size of the index, the number of seeks, scans, lookups and updates performs on the index and finally the last seeks, scans, lookups and updates date, as shown below:
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
,IX.name AS Index_Name
,IX.type_desc Index_Type
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,IXUS.user_seeks AS NumOfSeeks
,IXUS.user_scans AS NumOfScans
,IXUS.user_lookups AS NumOfLookups
,IXUS.user_updates AS NumOfUpdates
,IXUS.last_user_seek AS LastSeek
,IXUS.last_user_scan AS LastScan
,IXUS.last_user_lookup AS LastLookup
,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update
The number of Seeks indicates the number of times the index is used to find a specific row
The number of Scans shows the number of times the leaf pages of the index are scanned
The number of Lookups indicates the number of times a Clustered index is used by the Non-clustered index to fetch the full row
The number of Updates shows the number of times the index data is modified.
All zero values mean that the table is not used, or the SQL Server service restarted recently.
An index with zero or small number of seeks, scans or lookups and large number of updates is a useless index and should be removed, after verifying with the system owner, as the main purpose of adding the index is speeding up the read operations.
An index that is scanned heavily with zero or small number of seeks means that the index is badly used and should be replaced with more optimal one.
An index with large number of Lookups means that we need to optimize the index by adding the frequently looked up columns to the existing index non-key columns using the INCLUDE clause.
A table with a very large number of Scans indicates that SELECT * queries are heavily used, retrieving more columns than what is required, or the index statistics should be updated.
A Clustered index with large number of Scans means that a new Non-clustered index should be created to cover a non-covered query.
Dates with NULL values mean that this action has not occurred yet.
Large scans are OK in small tables.
Your index is not here, then no action is performed on that index yet.
- 238 reads