Covered index overhead question

Question

Covered index overhead question

Robert Frasca

SSCertifiable

Points: 6026
More actions
April 14, 2015 at 10:48 am

#296888

Suppose I have an index on columnA and columnB with columnX, columnY, and columnZ as included columns.
columnY gets updated.
What is the impact of the update on the index? Is the index simply updated or is there something more dramatic occurring that could cause page splitting etc? In other words, is it a major performance hit to update included columns of an index?
I know updating columnA and/or columnB would cause serious overhead but I'm not sure how updating included columns works under the covers.
"Beliefs" get in the way of learning.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

Page splits are dependent on workload. Also checked around and everyone seems to say the same thing. Not a valid argument, i.e. Argumentum ad populum, but could be used as a guide of people's practical experience.

This means that whether you have index yay wide or yay wide doesnt matter unless you run a workload through it.

It's a 'piece of string' statistic.

I would probably care more about the balance between covered index space used and the increase in lock footprint of an update on any one of those columns, and the lock footprint of a non-covering index doing lookups to the table, and potential deadlocks that non-covered indexes can cause.

A test case is to maybe

store a trace of requests to the DB,

then restore a backup,

Create a covering index.

Then run the saved trace against the restore.

Check the index statistics, i.e. rowlocks, page locks, total updates, total deletes on the entire table, page splits etc.

Then rinse repeat with a non covering index.

Get same information.

Then compare the info, Using excel is probably easiest as a stats dump.

Not sure if you can but may be worth doing a trace of your trace update to the restored DB to look out for deadlocks.

Probably not the answer you looking for, but there are no absolute rules to follow as far as I'm aware as everything depends on workload.

Though you can look for absolute rules in the same place people look for absolution 🙂

--http://sqlmag.com/sql-server/evaluating-page-splitssec-value

--http://en.wikipedia.org/wiki/Argumentum_ad_populum

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Robert Frasca (4/14/2015)
columnY gets updated.
What is the impact of the update on the index?

The index gets updated.

Yes, it can cause page splits if ColumnY is updated to something that requires more storage space, but to be honest you can't prevent page splits. Something, somewhere will split sooner or later. The question is how often. And you can always reduce fill factor slightly if there are more splits than you like.

If ColumnY gets updated 300 times a second it's a very different scenario to if it gets updated once a day

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 3

Any individual row might get updated, on average, a couple to three times in its lifetime so it's probably not that big a concern; however, I'll keep an eye on it. This is a third party app where the only options available to me for performance tuning are at the database level as I can't tune individual queries. I'm far more paranoid, in this scenario, about the unseen performance ripples an additional index or two can cause as I have no visibility into the app workflow. I also need to convince the DBA that I'm not going to cause him new headaches by fixing existing headaches. I think I'm just going to ask him to add the indexes and monitor closely. The good news is that if new problems surface it's easy to back out the change.

GilaMonster (4/15/2015)
Robert Frasca (4/14/2015)
columnY gets updated.
What is the impact of the update on the index?
The index gets updated.
Yes, it can cause page splits if ColumnY is updated to something that requires more storage space, but to be honest you can't prevent page splits. Something, somewhere will split sooner or later. The question is how often. And you can always reduce fill factor slightly if there are more splits than you like.
If ColumnY gets updated 300 times a second it's a very different scenario to if it gets updated once a day

"Beliefs" get in the way of learning.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

Are the updates to a row in batch (lots of rows) or singletons? If it's the latter, I wouldn't worry here. There could be page splitting, but it's relatively few. It can be combated (a bit) with reindexing, and potenially reducing the fill factor, as Gail mentioned.