So I am sitting here doctoring (not nursing) a beer listening to Jimmy Eat World and Nerf Herder and thinking about a huge decision I had to make this week. The more I thought about it, the more I wanted to pose this scenario, this quote unquote real-world scenario to get some feedback on how other DBAs or managers might handle the same situation. I know from doing this for so long and the people I have met, that this happens quite a bit to DBAs.
The scenario:
You have a slow running front end application. It has been getting progressively slower over the past few months, however, it has always had issues with certain processes. You, as the DBA, know you are dealing with millions of records in some tables. These tables have a tossed salad approach to indexes, one clustered index and up to 30 or more non-clustered indexes. You discover that a former DBA has added non-clustered indexes based on the sound advise of the index tuning wizard or database tuning advisor (dta). When things run slow you analyze and see thousands of sustained shared locks on one of the dta indexes; no blocking but users complain of slowness. You also know that developers are working on a solution to address a potential bottleneck with code you can not see.
Business is...well...pissed. A decision is made to move to a new, bigger server. This always fixes problems with code, right? The other variable is that since we are moving to a new server, why not go ahead and go from a 32-bit SQL 2000 Enterprise install to a 64-bit Standard SQL 2005 install? It would be WAY easier to manage and should gain a 20 to 30% performance increase OOTB.
Okay, so User Acceptance Testing (UAT) has been performed on SQL 2005 but for functionality only.
You also learn late in the game that the server you want to move to is a single RAID 5 array of local disks, not SAN attached.
Wait...you also just learn the vendor does not officially support SQL Server 2005. They have customers there but do not know how it will work for us.
Two days prior to moving to this new server with double the processors with the same version of SQL (2000) the vendor responds and says they now will support 2005 and beckon us to go there.
Now you have to decide:
1.) Do you stall the upset users and work to address the slow running code by working with the developers and tuning/deleting the dta indexes in hopes that this will buy enough time to test SQL Server 2005 for a future upgrade (within two weeks)
2.) Do you go ahead with the SQL Server 2000 migration to the new server over the next weekend in hopes that it will address the users slowness and loss of productivity, though the problem may still exist on the new faster server.
3.) Do you move forward with SQL Server 2005 64 bit Standard, knowing, wow, knowing there is no easy way back if there are problems.
4.) Do you spend the next two days testing with the developers, delete the ferel index from dta, re-create all of the large clustered indexes late on a non-maintenance weeknight knowing that SQL 2000 does not have an online rebuild functionatliy and will require whatever is not completed to be rolled back and could impact production?
5.) Add you own decision here.
I will relay what I did (ed - will have to do) with this scenario in a reply.