Category Archives: Indexing

When was the Last Time You Removed an Index Because it was Never Used?

Posted on by

image

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.

Continue reading →

Creating the SSC Maintenance Plan: Part 3

Posted on by

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.

Continue reading →

What Would You Like to See in a New Book on Indexing?

Posted on by

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.

Don’t Forget to Maintain Your Indexes

Posted on by

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…