SQL Indexes – what a great subject to discuss on! I just love them. It is incredible to find out each time, what a proper index can do to your database…
As far as I can think of there are 3 issues related to Indexes:
- Fragmentation
- Query Optimization
- Storage problems – I already blogged about it and if you follow the link you will see the whole story.
Inside each DBA’s maintenance tasks, should be included a step which will collect fragmentation on all Production most critical databases.
You can schedule the Index part once per month, or as frequent as you consider necessary.
The SQL query is rather simple:
- Create a table with structure:
use tempdb go CREATE TABLE #indexes ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL)
- Open a cursor that contains 2 parameters: schema name and table name selected from INFORMATION_SCHEMA.TABLES and execute for each:
INSERT INTO #indexes EXEC ('DBCC SHOWCONTIG ('schemaname.tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
- The execution output will be stored in a table with same structure as the output’s columns, used for future analysis.
I only focus on Indexes that have a fragmentation higher than 5% and have more than 10000 pages. In theory, there is no point in rebuilding an index that occupies less than 24 pages. In practice, it’s generally recommended not to do anything for indexes with less than 10000 pages.
Next step:
delete from #indexes where LogicalFrag <= 5 or CountPages <= 10000
The list resulted can be split in two action strategies:
- Reorganize Indexes that have a fragmentation rate > 5% and < 30%
- Rebuild Indexes that have a fragmentation rate > 30%
Check this also for more info on fill factor importance and Alter Index syntaxes:
Managing SQL Server Database Fragmentation
How to check Fragmentation on SQL Server 2005
I have been asked so many times, questions like: "Why this procedure is running so slowly?" or "why so many blockages?", "I have a new script and it takes ages until it is completed!" and I can go on. Almost every time, in a bigger or a smaller part - a lack or surplus of one/many indexes is involved.
Indexes in surplus - causes SQL to do as many index updates as many indexes are there on a table that include the same column which is modified in a single statement.
I saw many cases with tables having tens of indexes. Some of them were doubled and some of them were created just following SQL hints from SQL Query Optimizer. There is more damage than good, when you follow literary SQL's hints. First of all, multiple updates on those many indexes which slow down the query, secondarily index disk usage which can get substantial.
To understand which indexes are really unused and should be dropped at next maintenance window, SQL Server must not be restarted for some time (1 month - 6 months), considering that it will have exhausted all possible recurrent user queries, recurrent stored procedures, application transactions that run on it. This is because SQL will hold the statistics until next reboot. In order to have valuable statistics the SQL should have run for an enough period of time.
- Look at existing Indexes in your database:
The script will avoid Primary Keys, Unique keys and already Disabled indexes.
use your_database go select i.name index_name, o.name [object_name], i.type_desc, o.type_desc , i.is_disabled, i.is_unique from sys.indexes i inner join sys.objects o on i.[object_id] = o.[object_id] where o.type_desc = 'USER_TABLE' and i.name is not null and i.is_primary_key = 0 and i.is_disabled = 0 and i.is_unique = 0 order by o.name
- Check how many seeks/scans are done on above extracted indexes. You can filter the query by specifying one or more object names and index names. This select will display also each table's row count.
Example 1: you see a number of 1867777 scans on a 2000000 rows table and close to 0 seeks. Big problem. It means the index is not well designed. It is used but not as it should be.
Example 2: you see a number close to 0 both for seeks and scans columns. Why? The index has never been used since SQL was restarted.In conclusion, seeks column should filled with numbers. If both seeks and scans have numbers, the indexes should be left there. user lookups are used mostly by primary keys or clustered indexes, so it is unlikely you will something there.
use your_database go IF (object_id('tempdb..#temp') IS NOT Null) DROP TABLE #temp CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],S.[OBJECT_ID], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES, t1.row_count FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID inner join #temp t1 on S.object_id = object_id (t1.table_name) WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 and is_disabled = 0 and is_unique = 0 and i.name is not null -- and user_seeks =0 and OBJECT_NAME(s.[OBJECT_ID]) in ('object1') and I.[name] IN ('index1', 'index2','index3') order by OBJECT_NAME(s.[OBJECT_ID]) ,i.name
- Check leaf updates, inserts and deletes on indexes identified in previous step with zero scans/seeks.
This is the ultimate proof that an index which is not used in optimization, is used to be updated at insert/update/delete operations --> bad performance.use your_database go SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], A.LEAF_INSERT_COUNT, A.LEAF_UPDATE_COUNT, A.LEAF_DELETE_COUNT FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1 and OBJECT_NAME(A.[OBJECT_ID]) in ('object1') order by I.[NAME]
- At last, identify Indexes that don't do any harm, but overcrowd the Indexes list and use space, these are indexes completely useless and should be dropped.
DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID, i.object_id FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN ( SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbid) and I.NAME is not null and is_unique =0 and is_disabled = 0 ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC GO