Best Practice to design tables to support update one field faster using sql server

Question

I am working on Workflow like system. I have one task table and status field. Value for status can be one of New,ready,processing,error,abort,done.

I have about 7 processes which will be triggered based on different situation to change value of task status. Most time each process will work on its own data set and every time it only processes up to 5000 records. But I still see some deadlock if data reach around 2 million records. I check with SQL Profiler, looks like some page resource related. I am not good at sql server performance tuning and do no understand it very well.

Since inactive task will be archived every day, I am thinking to redesign the table to support around 10 Million records.

Few choice may be :

1. Create split tables based on status.
2. Create a master table with static data and supported table based on status

Is there any good practice for this kind of situation?

Thanks!

Answer 1

• You can partition the table based on status column. Active Records in one parition. Closed records in another parition
• On a monthly basis you can also clear the closed records (Delete if it is no longer required) or move to Archive Table
• Split table I don't think it would be a better choice (You do not need multuple tables for same functionality)
• To Avoid Deadlock, Which Version of SQL Server you are using
• If you are using SQL 2005 and above Use Read Committed Snapshot Isolation to read committed data. This would ensure you reads do not block writes

Answer 2

I know I should be posting an answer, but with these questions the answers may follow:

1.) Are table hints in place? If not, then experiment with applying those

2.) Are all available indexes utilized, and is the TaskId column the only acceptable index? Sometimes, certain situations when analyzed will bring about the need for a new index

3.) Are all 2 million records live/active at any given time?

4.) Have you checked for fragmented indexes? Daily archiving can cause index fragmentation, so you might want to add at the end of your archiving job a step to check and fix fragmentation

5.) Status field for new,ready,processing,error,abort,done is under what data type?

6.) Have you experimented on indexed views? If you already know that you're limiting certain data and want to avoid table scans, it might be able to help