Working with Indexes

Working with Indexes

Index Update

When we create an index, the statistics of that index are also created. When we rebuild the index, the statistics are also updated.

What is bytes per index key

The maximum number of bytes in a clustered index key can't exceed 900. For a nonclustered index key, the maximum is 1,700 bytes. (for SQL Server 2016 (13.x) and later)

For example, it would be varchar(1700) or nvarchar(850)

Because nvarchar takes up 2 bytes per character

If you define over 1700 bytes for a nonclustered index you will encounter this error:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_Test' has maximum length of 1701 bytes. For some combination of large values, the insert/update operation will fail.

PS : Index will be generated despite this error

To drop index

DROP INDEX index_name ON table_name;

To disable index

ALTER INDEX index_name ON table_name DISABLE

To rebuild index

ALTER INDEX index_name ON table_name REBUILD WITH (ONLINE = ON)

To list index details of table

sp_helpindex table_name

To list all disabled index

select as table_name, as index_name
from sys.indexes
    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by,

To list size of all index

SELECT tn.[name] AS [Table name], ix.[name] AS [Index name],
SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]
FROM sys.dm_db_partition_stats AS sz
INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id] 
AND sz.[index_id] = ix.[index_id]
INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
GROUP BY tn.[name], ix.[name]
ORDER BY 3 desc

To list all fragmented indexes

SELECT as 'Schema', as 'Table', as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and is not null AND DDIPS.avg_fragmentation_in_percent > 30 ORDER BY DDIPS.avg_fragmentation_in_percent desc

Usual determination of the use of the equation :
When the Fragmentation percentage is between 15-30: REORGANIZE
When the Fragmentation is greater than 30: REBUILD

INDEX REBUILD always drops the index and reproduce it with new index pages. This activity can be run in parallel using an online option (Enterprise Edition) with the ALTER INDEX command, which does not affect the running requests and tasks of a similar table. REBUILD Index can be set online or offline. If a user performs the REBUILD INDEX offline, then the object resource (Table) of the index won’t be accessible till the end of REBUILD process completion. It affects numerous other transactions as well, which are associated with this object. Rebuild index operation recreates the index. Therefore, it generates new statistics and appends the log records of the index in the database transaction log file too.

REORGANIZE INDEX command reorders the index page by expelling the free or unused space on the page. Ideally, index pages are reordered physically in the data file. REORGANIZE does not drop and create the index but simply restructure the information on the page. REORGANIZE does not have any offline choice, and REORGANIZE does not affect the statistics compared to the REBUILD option. REORGANIZE performs online always.

To create a Clustered index

Create clustered index index_name on table_name (column name) include(columns in select statement)

To create Primary Key


To list Unused index

select OBJECT_NAME(us.object_id) as tableName, as indexName,
    us.user_seeks, us.user_scans, us.user_LookUps,
    (us.user_seeks+us.user_scans+us.user_LookUps) as TotalRead,
    us.user_updates*100/case (us.user_seeks+us.user_scans+us.user_LookUps) when 0 then 1 else (us.user_seeks+us.user_scans+us.user_LookUps) end as WritePercentage,
    CASE us.user_seeks WHEN 0 THEN 0
        ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
    CASE us.user_scans WHEN 0 THEN 0
        ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id 
WHERE us.database_id = DB_ID('dbName') 
AND i.type_desc = 'nonclustered'
AND i.is_unique_constraint = 0
order by 8 desc

user_seeks+user_scans+user_LookUps (TotalRead) must be lower than user_updates

Pay attention to the indexes in the log tables. They get lots of inserts but rarely seek. Think twice before removing such indexes. Otherwise, queries on the log pages on the ui side may crash.

To list missing index

dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC