Clustered index update

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Clustered index update

Clustered index update

Rate Topic

Display Mode

Topic Options

Author

Message

Matt-1034261

Posted Monday, February 07, 2011 1:06 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 5:40 AM
Points: 88, Visits: 277

Hi

On our website when someone views a members image we update a field called visits which is just a counter that shows how many times an image has been viewed.

The sql statement looks like this : -

update images set visits=visits+1 where (username=@username) AND (mykey=@mykey)

However I have noticed that looking at the execution plan a clustered index update is taking place which is taking up 86%.

Anyone know why this might be?

Thanks
Matt

Post #1059874

GSquared

Posted Monday, February 07, 2011 1:21 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

The "clustered index" is, for all intents and purposes, the table itself. I would expect that to the majority of the work on a simple update.

In other words, this is normal and expected.

You'll want to take a look at how clustered indexes work. If you'll be administering or performance tuning, you'll need to know about them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1059880

Steve Jones - SSC Editor

Posted Monday, February 07, 2011 1:33 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

Also be aware that the execution plan adds up to 100%, so something will take up resources. If you have the index on fields that the update can use, then the resource usage should be minimal.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1059887

Grant Fritchey

Posted Tuesday, February 08, 2011 7:31 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

Also, also, be cognizant of the fact that just becuase you have a high cost in an execution plan, doesn't mean that the process itself is very high cost. Execution plan costs are just estimates and do not represent measures that you can use to determine performance.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1060210

Matt-1034261

Posted Tuesday, February 08, 2011 8:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 5:40 AM
Points: 88, Visits: 277

Thanks for all your replies

Post #1060316

« Prev Topic | Next Topic »

Permissions