SQL

SQL

Posted on: 27/03/14

When taken as a whole, the area of SQL Server Performance Tuning is quite literally massive. Over the next few articles I am going to concentrate on covering the basics of performance tuning.

This particular Article concentrates on Indexing, and asks the question ‘Do I have any Duplicate Indexes in my SQL Server Database’?

Unfortunately, SQL Server does not restrict you from ‘accidentally’ creating duplicate indexes, there are a few exceptions to this rule (for instance, you can only have a single Clustered index per table), but not many. I have recently been asked to take a look at a customer database and identified over 30 duplicate indexes across the database.

So what you might ask? Well, a query is only going to use one of the duplicate indexes, which means that the other one is simply taking up space. Worse than this though is that when data is written to a table, each index for that table is also written to as part of this process, with the potential to reduce overall performance.

Duplicate indexes generally start to creep in when people use tools like the database tuning advisor (DTA) without really knowing what they are doing. The tool is incredibly powerful, but takes a decent amount of understanding in order to get the best out of it. The DTA will only analyse the TSQL that is put through it, this can be a large dataset, or right down to a single TSQL query. It does not consider any activity that has not been captured and analysed.

The following script can be used to identify duplicated indexes on a database.

In my next Article, we’ll take a look at a further aspect of SQL Server indexing and ask the question ‘Do I have any potentially bad indexes in my SQL Server Database’?

Celerity have a number of consultants who are experts with a wide range of Microsoft products including SQL Server. If you have any concerns about the performance of your system, or are looking for a health check, contact us and we would be happy to discuss your requirements in more detail and tailor a solution to your environment. 

Mike Metcalf, Technical Consultant, Celerity Limited

Disclaimer:

The scripts used in this article may have been created by other people and/or gathered from specialist sites the internet, no implication of ownership should be inferred.

To view this article on the Celerity Website click here