Tag Archives: script rebuild indexes

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.

I. Fragmentation

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 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:

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.

How to know if you have unused indexes

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.

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.

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.

About me…

I am from Bucharest, Romania and I am working with SQL Servers since 2006. I have been working closely with all SQL versions starting with SQL Server 2000. I am a senior DBA at OpenSky company and spend my free time with my husband and my son.
See more about me and why I started this blog.