We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Does a clustered index optimize an update process?

FatalErr asked
Medium Priority
Last Modified: 2012-05-05
I'm trying to understand how SQL Server will actually access the data during an update.  I've read that clustered indexes are fast for reading ranges but individual reads do not have a performance boost over a non-clustered index.  In an update, does the "read head' (or whatever) move between the source and target for each record regardless of being clustered so there is no performance gain over a regular index?

Background: I'm developing a system with retiree information.  I get an updated Retiree file each month from HR's mainframe with updated address etc.  HR uses RetireeID as their key field which is a composite of several numbers and letters.  I update my Retiree table which contains the same fields as the HR file.  I'm proposing using the RetireeID as the Primary key on my table as a clustered index rather than an identity field.

Note: my system does not capture data through a GUI.

Watch Question

How many records are you talking about? In general, it is a good idea to use a clustered index to support the most common queries.

There is a update hit. especially on large updates. In the cluster, the hit is due to the fact that the data is organized along the lines of the index elements and that physical ordering has to be maintained. In a nonclustered, it inserts into the table and then updates the index - two separate acts, as you surmise.

If you are replacing all of the records in a table, drop all indexes before inserting, then add indexes. That's a general rule.

but how many records are you talking about? And whats the hardware platform. As I often quote "premature optimization is the root of all evil." - attributed to alan turing


I've got about 250K records.
I don't know how much ram.  At least a gig.  It is dual processor.  I don't know more right now.

Inserts are slowed by a clustered index.  But, I'm focusing just on the update process.  

The cluster would really increase performance if multiple records are updated sequentially rather than one at a time.   I don't know if that's what happens.
I have been tuning SQL Server for 20 years and I could not make that statement about update performance. I have always found that my assumptions about "what the database should do fast" or slow are consistently inaccurate to the point where I only rely on carefully run benchmarks.

The impact of the cluster on performance has nothing to do with sequentially updating the records, but rather how much time the optimizer has to spend finding those records prior to the update.  So, as long as the columns in the where clause are indexed, you should get great performance. And the type of index will not make much of a difference. Most of my current understanding of SQL is based on actual benchmarks on a stable SQL 2000 system that had 30 million to 500 million records.  As far as I know, records are updated one at a time regardless of whether there is a cluster or not.  Remember that 99% of the operations you do in SQL are going to happen in memory, not on the disk. The statement written to the disk at the time of commit is the update statement itself, not the data being updated.  

A tremendous amount of performance in updates will relate to your log writing. Get an extra controller to write the log file and updates will fly. If you have disk contention between the log writer and the process that is looking for your records, you will probably see slower performance.  

I just did a little benchmark... created 200,000 test records on my P4 2G duo with 2G ram...
an update statement that updated 16,000 of the records (about 8%)

newly created table with no indexes at all
   update column1 from 'A' to 'B'  6 seconds
create clustered index on column1
   update column1 from 'A' to 'B'  71 seconds (wow! didn't expect that! - did the whole thing a second time to make sure - 67 seconds!!)
   update column2 where column1 = 'B' 6 seconds
   Rerun for a different column without clearing the buffer pool - unmeasurable (instantaneous)
create nonclustered index on column1 (had to drop cluster of course)
   update column1 from 'B' to 'A'  8 seconds

So you see - it is really hard to predict even for an expert. Do the benchmarks and once you have a performance problem, use query analyzer to figure out what might help. A great resource, besides ee, is  http://www.sql-server-performance.com

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
One error in my post - the queries were updating 160,000 records - almost 80% of the database! Not typical of course. The "shape" of your queries will be a factor deciding what is important.


1. Indexes will significantly improve seek times when trying to find data.
2. They will slow performance when doing inserts and updates (as SQL also has to maintain the index as well as the data)
3. Sequential clustered inserts are OK (e.g. 1,2,3,4,5).
4. Non-sequential inserts on data pages with high fill factors will result in page splits (think of the page as where the data is stored, and an insert in the middle causes SQL to shuffle the data around to fit the new data in) - this can be a significant overhead on busy servers.
5. Non-clustered covering indexes (i.e. indexes that contain all the data that you are trying to retrieve) will improve performance (as SQL only has to go down to the index, or leaf level, to retrieve the data, and does not have to touch the actual data page).


Thanks for the good info.  

Aaron - You said get an extra controller to write the log file.  Will SQL Server automatically do that, or do you have to direct it in some way?  Thanks,
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.