Adding a clustered index to a SQL table: what dangers exist for a live production system?

Question

I've been put in charge of a 10-year old transactional system where the majority of the business logic is implemented at the database level (triggers, stored procedures, etc). Win2000 server, MSSQL 2000 Enterprise. No immediate plans for replacing or updating the system are being considered.

The core process is a program that executes transactions - specifically, it executes a stored procedure with various parameters; let's call it sp_ProcessTrans. The program executes the stored procedure at asynchronous intervals.

By itself, things work fine, but there are 30 instances of this program on remotely located workstations, all of them asynchronously executing sp_ProcessTrans and then retrieving data from the SQL server. Execution is pretty regular - ranging 0 to 60 times a minute, depending on what items the program instance is responsible for.

Performance of the system has dropped considerably with 10 years of data growth: the reason is the deadlocks, specifically deadlock wait times, on the Employee table.

I have discovered:

• In sp_ProcessTrans's execution, it selects from an Employee table 7 times
• The select is done on a field that is NOT the primary key
• No index exists on this field. Thus a table scan is performed 7 times per transaction

So the reason for deadlocks is clear. I created a non-unique ordered clustered index on the field (almost unique, NUM(7), very rarely changes). There was immediate improvement in the test environment. The problem is that I cannot simulate the deadlocks in a test environment. I'd need 30 workstations, and I'd need to simulate 'realistic' activity on those stations, so visualization is out.

I need to know if I must schedule downtime. Creating an index shouldn't be a risky operation for MSSQL, but is there any danger (data corruption, extra wait time, etc.) in creating this field index on the production database while the transactions are still taking place? I can select a time when transactions are fairly quiet through the 30 stations.

Are there any hidden dangers I'm not seeing? (I'm not looking forward to restoring the DB if something goes wrong. It would take a lot of time with 10 years of data.)

Answer 1

Data corruption shouldn't be an issue, but if you try adding an index to a live production table you are likely to experience problems as the table will not be responsive to queries during the index creation. Creating an index will apply an exclusive table lock until it is complete, and the time this takes will depend on numerous factors (especially the number of rows).

scheduled downtime is strongly recommended and also a good habit to get into. And obviously backup taken, and a plan in case you have to undo what you're intending.