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)
https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16
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
sys.objects.name as table_name,
sys.indexes.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
sys.objects.name,
sys.indexes.name
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 S.name as 'Schema', T.name as 'Table', I.name 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 I.name 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
ALTER TABLE table_name ADD CONSTRAINT PK_index_name_ID PRIMARY KEY CLUSTERED (Id);
To list Unused index
select OBJECT_NAME(us.object_id) as tableName,
i.name 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,
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,
us.last_user_seek,
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,
us.last_user_scan,
us.user_scans,
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,
us.last_user_LookUp,
us.user_LookUps,
us.last_user_update
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
GO
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
SELECT TOP 25
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,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ 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
IS NOT NULL THEN ',' ELSE
'' 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
GO
