Applies to
Summary
The following explains the importance of not allowing duplicate SQL Server instance indexes in a database and ways of easily removing them within the SQL index software.
Description
When it comes to SQL instance indexes, lot of issues may occur when constant work is put on them. Among those are a simple fragmentation or some other problem that is the direct consequence of a database being under heavy workload caused by lot of different clients in a short or, even worse, a long period of time without a proper index maintenance. Some of those “SQL” client users might not have clear idea of what they are doing. For example, one may think that creating more SQL Server instance indexes will make things go faster. And for their specific situation, it might as well be the case. However, those new indexes, although is solving one, may end up causing a lot more problems, thus doing more harm than good to the database overall performance.
One of the mentioned problems that will most surely arise by using the above approach is the duplicate indexes phenomenon. So, what are duplicate indexes and how can they occur?
It goes without saying that SQL instance indexes need to be maintained by a professional DBA knowing exactly what they are doing and is solely responsible for any stutter or butter smooth performance of the database in charge.
With all previously mentioned, one of the reasons for having duplicate SQL Server instance indexes in the first place is the wrongdoing without thinking about the possible side effects.
By definition, duplicate indexes are those that exactly match the primary key and all included columns.
To further illustrate, when there are duplicate indexes, any time there is an insert, delete or update statement on one index, all of its duplicate indexes are changed. This directly affects the system resources by taking a good portion of CPU, RAM memory and a log space. When there are databases with tables counting millions of rows, SQL Server doing extra work to keep these indexes fresh and updated.
This results in database engine requiring more time when updating data in the table and when maintaining and calculating these duplicate indexes.
Delete duplicate SQL Server instance index
Removing SQL duplicate indexes is very easy and straightforward task with SQL index application.
There are three ways to search for duplicate indexes:
- SQL database level – search all duplicate indexes for selected SQL database
- SQL instance level – search all duplicate indexes for selected SQL instance
- All instances – scan for duplicate indexes across all SQL instances that are added in the server explorer
SQL database level
To begin with the duplicate index clean-up, start the ApexSQL Defrag and from the Fragmentation tab of the main menu, choose the desired database from the server explorer then click the Duplicates button. In the Duplicate indexes window choose the duplicate SQL Server instance index that needs to be deleted, or simply choose all of them. Afterwards click the Delete script button in the right bottom of the window:
The previous action will open the Delete Script editor with the generated T-SQL script that will remove selected duplicate indexes when used. Click the Execute button and this script will be performed on the previously selected SQL instance database. The Messages section in the bottom of the editor, will show the SQL Server messages generated during the script run and notify if the script is finished successfully or show errors during execution:
Now, once the same database is chosen for duplicate indexes, the previously deleted indexes are gone from the grid. Info about duplicate indexes is presented in following columns:
- Index – name of the index
- Table – table the duplicate index belongs to
- Schema – SQL Server instance index schema
- Database – the database index belongs to
- Instance – the SQL instance duplicate index is part of
- Type – type of the index
SQL Instance level
To delete duplicate indexes at SQL instance level i.e., from all databases attached to that instance, choose the target SQL instance from the server explorer and repeat steps from the previous section:
All instances
Again, same steps from two previous sections can be performed here as well with one small exception of choosing the All instances entry as target for duplicate SQL Server instance indexes scan:
Conclusion
Since SQL Server has no protective measures against having and piling up duplicate indexes, it is very important to frequently check for these indexes along with doing other defragmentation operations, rebuild and reorganize, on target database indexes.
Practising this regularly ensures the optimum performance of a SQL Server.
