Indexes can be great for boosting the performance of a query, but if an index is never used, it can drag down an instance’s performance. This is because unused indexes must be maintained just like any index. For example, anytime a row is inserted, updated, or deleted, indexes must be maintained. If a lot of records are updated or inserted into a table, an index may experience page splitting. During index rebuilding or reorganizing, indexes are defragmented. During DBCC CHECK DB, indexes have to be checked for their integrity. During all of these processes, all data modifications have to be logged in the transaction log. And of course, indexes take up space in the data cache and on disk. All of these resources are wasted on unused indexes, and can hurt the overall performance of an instance of SQL Server.
Blogs are a great way to learn new things about SQL Server, and just in case you have missed them, I want to recommend the following two blog series that I feel every DBA should read.
Note: This is an in-depth article that exceeds 5,000 words, and provides a case-study of how a maintenance plan could be implemented on a single, plain vanilla, SQL Server instance.
As I have mentioned in previous parts of this article series (part 1, part 2), I act as a consultant DBA for the SQLServerCentral.com (SSC) and Simple-Talk.com (ST) backend databases, and I don’t perform any of the day-to-day tasks. But one of the things I was asked to do was to implement a database maintenance plan for the new, two-node, active/active cluster that has recently gone live. In this third part of this series, I am going to talk about how I created the maintenance plan, and why I made the choices I did.
I am currently in the early stages of writing an outline for a new book on indexing, targeted towards novice DBAs. While this topic has been done to death in hundreds of articles, presentations, and books, I think there is still room for a book that makes the topic easy to read and easy to understand for beginners. The focus on the book would be on the essential (not esoteric) indexing skills that DBAs need to perform their job on a day-to-day basis.
I would really appreciate your advice and feedback on what such a book should include. For example:
- What part(s) of indexing do you find very easy to understand?
- What part(s) of indexing do you find the most difficult to understand?
- What topics do you think I should include in the book that would be of the most help to you?
- What topics do you think I should not include in the book because they are not helpful for the typical DBA?
- Can you provide me URL’s of articles on indexing that you think are particularly good, or particularly bad?
- What’s the best book you have ever read on indexing? What’s the worst book? And why?
If you have any feedback on any of the above topics (you don’t have to answer all of the above question), please post them below. The more feedback I get, the better I will be able to write a book that will best fit the needs for novice DBAs. Once the book is done, it would become freely available as an eBook, just as all my recent books have been.
So long as data is inserted, updated and deleted from tables, indexes will become fragmented over time, and performance will suffer. Index maintenance is vital to keeping databases and their users happy. In this article, I go through the key index maintenance tasks and identify more essential areas that are a DBA’s duty to maintain. Read more…
There are many different types of index that exist in SQL Server. In this guide, I look at what a table and index are, before exploring how SQL Server 2005 and 2008 indexes work, including those that are filtered, special and compressed. Read more…
Just today, I was reviewing the indexing of a database used by a third-party application. As part of my review, I ran a script against it that is used to identify duplicate indexes. The script I used was written Paul Nielsen, the author of the SQL Server Bible; and Itzik Ben-Gan. The script is available here.
After running Paul’s script against the database to find duplicate indexes, I discovered that almost every table had a duplicate index. As I delved into this a little closer, I discovered what the designer of this database had done.
First, it looked like he created primary keys using non-clustered indexes on an identity column for most of the tables in his database. Second, he then added a clustered index on the same identity column for each of these same tables. By doing so, he ended up having two identical indexes on the same identity column, one clustered and one non-clustered. Instead, the designer should have created his primary keys using clustered indexes instead of using non-clustered indexes. This would give him the same end effect, but requiring only a single index, not two. Obviously, by having duplicate indexes on most of his tables, he was causing SQL Server a lot of additional work to maintain extra indexes that were not needed.
If you have never checked your databases for duplicate indexes before, you should do so, as you might find duplicate indexes that can be removed, helping to boost the performance of your existing databases.