Automation and T-SQL Tuesday

Posted: February 8, 2011 in SQLServerPedia Syndication, T-SQL Tuesday

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.

Comments
  1. tomroush says:

    for those who want to see Microsoft’s version of this – a script is also available in books online – you can find it in section E. of the dbcc showcontig page here: http://msdn.microsoft.com/en-us/library/ms175008.aspx
    That was written for and works for sql 2000, it also works on the newer releases, – but for someone wanting to use the dynamic management views only available in 2005 and 2008, you can try this one http://msdn.microsoft.com/en-us/library/ms188917.aspx – (section D)
    I use Ola’s code myself – he’s done an absolutely remarkable job of codifying the art that is automated index maintenance.

  2. […] Chris Shaw poses the question of when do tables need re-indexing and suggests checking out Michelle Ufford and Ola Hallengren’s scripts on Re-indexing.  Having worked with these scripts before I would suggest checking them out!  Check out the post for the links. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s