?
Solved

cluster index and update and delete

Posted on 2012-08-27
11
Medium Priority
?
459 Views
Last Modified: 2012-09-04
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?
0
Comment
Question by:wasabi3689
  • 7
  • 3
11 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 38336910
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
 

Author Comment

by:wasabi3689
ID: 38337004
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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 38337025
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wasabi3689
ID: 38337070
Yes, it has currently non-clustered index(es) in place over the same columns. What's your other suggestion?
0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 38337107
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
 

Author Comment

by:wasabi3689
ID: 38337223
I wonder if dropping the original non-cluster index will cause problem for the application?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 38337274
It will if you don't have the clustered index in place first.
0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 38337279
... but you certainly don't need both over the same column(s).
0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 38337284
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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 38337290
... 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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38337912
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question