CODEWITHSUNDEEP
sql-serverdatabaseperformanceindexing
Mark
Mark

Reputation: 57

Will adding a clustered index to an existing table improve performance?

I have a SQL 2005 database I've inherited, with a table that has grown to about 17 million records over the course of about 15 years, and is now horribly slow.

The table layout looks about like this:

id_column = nvarchar(20),indexed, not unique
column2 = nvarchar(20), indexed, not unique
column3 = nvarchar(10), indexed, not unique
column4 = nvarchar(10), indexed, not unique
column5 = numeric(8,0), indexed, not unique
column6 = numeric(8,0), indexed, not unique
column7 = nvarchar(20), indexed, not unique
column8 = nvarchar(10), indexed, not unique

(and about 5 more columns that look pretty much the same, not indexed)

The 'id' field is a value entered in a front-end application by the end-user.

There are no defined primary keys, and no columns that can be combined to make a unique row (unless all columns are combined). The table actually is a 'details' table to another table, but there are no constraints ensuring referential integrity.

Every column is heavily used in 'where' clauses in queries, which is why I assume there's an index on every one, or perhaps a desperate attempt to speed things up by another DBA.

Having said all that, my question is: could adding a clustered index do me any good at this point?

If I did add a clustered index, I assume it would have to be a new column, ie., an identity column? Basically, is it worth the trouble?

Appreciate any advice.

Upvotes: 0

Views: 755

Answers (2)

Code Different
Code Different

Reputation: 93181

I would recommend adding a clustered index, even if it's an identity column for 3 reasons:

  1. Assuming that your existing queries have to go through the entire table every time, a clustered index scan is still faster than a table scan.

  2. The table is a child to some other table. With some extra works, you can use the new child_id to join against the parent table. This enables clustered index seek, which is a lot faster than scan in some cases.

  3. Depend on how they are setup, the existing indices may not do much good. I've come across some terrible indices that cover 1 column each, or indices that don't include the appropriate columns, causing costly Key Lookups operations. Check your index stats to see if they are being used at all.

Upvotes: 1

Brad D
Brad D

Reputation: 762

I would say only add the clustered index if there is a reasoning for needing it. So ask these questions;

Does the order of the data make sense?

Is there sequential value to the way the data is inserted?

Do I need to use a feature that requires it have a clustered index, such as full text index?

If the answer to these questions is all "No" than a clustered index might not be of any additional help over a good non-clustered index strategy. Instead you might want to consider how and when you update statistics, when you refresh the indexes and whether or not filtered indexes make sense in your situation. Looking at the table you have as an example it tough to say, but maybe it makes sense to normalize the table further and use numeric keys instead of nvarchar.

http://www.mssqltips.com/sqlservertip/3041/when-sql-server-nonclustered-indexes-are-faster-than-clustered-indexes/ the article is a great example of when non-clustered indexes might make more sense.

Upvotes: 1

Related Questions