cluster index and update and delete

We have a performance issue for a large database. I plan to place a cluster index to a key table which involved a lot of update and delete activity. I want to know if cluster index will slow down the update and delete activities. What is the best way to do that? any other alternative?
wasabi3689Asked:
Who is Participating?
 
knightEknightCommented:
It depends on what column(s) you include in the clustered index.  I recommend that you create the index over the column or columns most likely to be used in WHERE clauses of your select/delete statements.  This is often the primary key of the table, but not always.  With the right columns in the index, performance can be improved.
0
 
wasabi3689Author Commented:
my question is still I want to know if having cluster index will help faster update or delete in the table if I implement the column(s) correctly?
0
 
knightEknightCommented:
I believe so, yes, but the question is to what degree.  This will depend on the data and whether there are currently non-clustered index(es) in place over the same columns.  If there are, then the non-clustered index should be dropped after a clustered index over the same column is created.  But this will likely mean that performance won't improve that much, since the column(s) were already indexed.  But if no such index currently exists (clustered or non-clustered) then I expect the performance gain will be significant.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wasabi3689Author Commented:
Yes, it has currently non-clustered index(es) in place over the same columns. What's your other suggestion?
0
 
knightEknightCommented:
By making the index a clustered index instead of a non-clustered index, you will save a lot of disk space, since non-clustered indexes are stored separately from the data.  Therefore you may see some improvement by creating a clustered index and dropping the non-clustered index on the same column.  Beyond this I don't have enough information about your data to offer further specific suggestions.

However, you might find this query insightful ... SQL Server will suggest indexes for you based on the meta stats:
-- Missing Indexes - run once at the server level
SELECT  mig.index_group_handle,
        mid.index_handle,
        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure,
        mid.statement AS obj_name, 
/*  'CREATE NONCLUSTERED INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + ' ON ' + */
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
        migs.*,
        mid.database_id,
        mid.[object_id]
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE   CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC

Open in new window

0
 
wasabi3689Author Commented:
I wonder if dropping the original non-cluster index will cause problem for the application?
0
 
knightEknightCommented:
It will if you don't have the clustered index in place first.
0
 
knightEknightCommented:
... but you certainly don't need both over the same column(s).
0
 
knightEknightCommented:
Note that in order to create the clustered index, you will need about as much free disk space as the primary data file currently holds.
0
 
knightEknightCommented:
... but this space will all be freed up again once the index is fully created -- and then some once you drop the non-clustered version.
0
 
Scott PletcherSenior DBACommented:
You should capture and save all existing index stats as well as the missing index stats before changing the indexes, otherwise the current info will be lost.

You should also review that info to verify that you are creating the clustered index with the correct column(s).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.