Re: Keylocks and updates

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Max Spectrum (anonymous_at_discussions.microsoft.com)
Date: 03/15/04 

Date: Sun, 14 Mar 2004 18:06:08 -0800

     
     ----- Ray Higdon wrote: -----
     
     First off if you are being inserted into that heavily then a composite
     clustered index is NOT the way to go. This can cause unpredictable page
     splitting.

**** No inserts - just lots of updates. Does page splitting occur on an update?

I would have a clustered index on a column with the IDENTITY
     property, then use a unique constraint on the columns to make a primary key.

**** clustered index does exist on the PK and it is an identity column

     And so you know, keylocks are not necessarily bad.

**** I agree. Key locks are not necessarily bad, but in my case it is affecting performance and limiting the frequency that a row can be updated.
     --
     Ray Higdon MCSE, MCDBA, CCNA
     ---
     "Max Spectrum" <anonymous@discussions.microsoft.com> wrote in message
     news:D5983173-633F-49E8-8358-F3F7D5DF713F@microsoft.com...
> Scenario: A table that is 6 columns wide, has a primary key with a
     clustered index. One of the columns is incremented approx. 250-300
     times/minute. At this rate key locks and some general performance
     degradation occurs on the server. Anything higher causes performance to drop
     dramtically.
>> Any suggestions as to how one might increase the rate that a single record
     can be updated? I need to get it up to 2500+ /minute.
     
     
     

Relevant Pages

  • Re: index fillfactor
    ... is page splitting, you use fill factor to prevent page splitting. ... example if you have a clustered index (clustered indexes are not pointing ... Streetnames that start with a-c ... > So far from reading books online, I can only assume that a fillfactor of 100% is> ok for read only tables, any other type of table and I'm unsure of what to set> the fillfactor %. ...
    (microsoft.public.sqlserver.setup)
  • Re: Need ammunition against clustered index hampers performance
    ... In the old days, before SQL Server 7.0, hotspots were a problem. ... typical example was a clustered index on an Identity column. ... The other behavior related to indexes is page splitting. ...
    (comp.databases.ms-sqlserver)