I was asked to review a very busy table and identify any areas of improvement.
I have very limited power to change the table just the indexes.
Table information
- 240 columns
- ~5 million rows
- Read and updated by about 30 applications, which range from websites to polling applications.
- Each row represents a contract and its state based on three flags (varchars of size 5, 8 and 8).
- A row's life cycle goes from start to end through these three flags, and eventually is done.
- A row typically is updated or changed 10 to 30 times throughout this lifecycle.
- The primary key is a combination of an id column, a guid column and a company column.
- Table has 40+ indexes, the majority of which are duplicates and unused. This is based on the
sys.dm_db_index_usage_stats
DMV, running twice a week for the past 7 weeks.
The current clustered index on this table has five columns:
- Company column (50 distinct values)
- Region column (21 distinct values)
- FlagA column (8 distinct values)
- FlagB column (24 distinct values)
- FlagC column (5 distinct values)
My understanding is a clustered index should adhere to the following properties. Source
- Unique
- Static
- Narrow
- Ever increasing.
The current clustered index is none of those.
- There is no unique Id.
- The three flags are updated constantly.
- There can be 5000 rows any given moment that have the same values for all 5 columns.
So my assumption was to correct this with a clustered index on the Id
column - an integer that is NOT an identity but maintained through a counter table (read value, add 1, update counter table).
I created a clustered index on the Id
, not using the primary key because I believed adding the guid and company column would not give me any benefit.
I then created a nonclustered index which holds the Company, Region and 3 flags.
In a test environment the stats were looking good, user_updates
were lower etc. But the overall performance of applications against this table was terrible. The most common queries against this table are:
SELECT *
FROM table
WHERE ID = 1234;
And
SELECT *
FROM table
WHERE Company = 'company'
AND Region = 'region'
AND flagA= 'A'
AND flagB = 'B'
AND flagC = 'C';
- What I have missed? Are there exceptions to the above clustered index rules?
- Would the clustered index benefit from adding the company and region to the id?
It is my understanding if the clustered index is on non-static values it needs to constantly reorganize itself and the other nonclustered indexes, which at this point we still have 40 plus...to be later removed. Wouldn't I have seen a gain there?
Added information.
- Both environments are SQL Server 2008 R2
- Have the same OS, memory etc.
- The only difference was the clustered index.
- I wrote the
SELECT *
to be concise in the above example. - Test and production environment are "the same", as best they can be: Production was facing a heavier load, but both were running same applications, load balanced etc. Test was significantly slower.
- We ran with the new clustered for a week and a half before we rolled back to the original setup. Initially, stats were updated and specific stored procs were recompiled as slow performance started to crop up.
- I do agree that with the multiple column index at least the table was organized better than just on
Id
. I want to reattempt with Company, Region, and Id as the CI, but leave the three flags out of it.
What have I missed?