What a great discussion for T-SQL Tuesday. Over the last week I have been debating all the things that we as SQL Server Professionals should automate, I was comparing the list to my actual list of what I have been able to do. However as I continued with my tasks the answer became obvious. I wanted to blog about something that I had been working on recently.
Whoops I forgot some of the important information on this post! This is a post that is part of a collection of posts from the SQL Server Community. The way this works is each month someone asks a question that is relevant to SQL Server, and a number of people answer it. This month’s question comes from Pat Wright. The question is: How can you as a database professional make your life easier though automation?
The question that I was going back and forth with in my mind was this: if in a specific situation could a table exist and accept transactions and not need to be re-indexed. The answer is not directly important at this time, but what is important is that there is maintenance that needs to be performed on the database from time to time. It could be re-indexing or it could be backups, maybe even some business processes that just need to happen. In my case I was looking at automating some of the processing for my indexes. This is where I am coming from with this month’s answer.
Should you re-index all the tables in your database all the time? The direct answer is no, you will from time to time be presented with tables that don’t need to be indexed. Should you spend time re-indexing these tables when you have very little processing time? Again I would answer no. So what’s the solution? Well, I did not generate the solution at all, I have downloaded scripts from SQL Fool (Michelle Ufford) and Ola Hallengren that will do just what I need. These scripts will allow my system to re-index the indexes that need it and leave the ones that don’t need it alone.