Re: When an update occurs, what happens to the indexes

From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 02/09/05 

Date: Wed, 9 Feb 2005 10:26:06 -0800

You should also look at the fragmentation state of each of the indexes - its
possible you're seeing lots of page splits which is decreasing the
performance of update operations. See the whitepaper below for more info.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Regards 

-- 
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@discussions.microsoft.com> wrote in message
news:25D9D7D1-E8AD-4AD4-83B9-B6602CDE9E3F@microsoft.com...
> Thank you Tibor.
>
> "Tibor Karaszi" wrote:
>
> > for each change you have in SET inside your UPDATE, SQL Server have to
reflect that in each index
> > that is affected. Say you have table with 10 indexes. And you have an
UPDATE which in SET changes 5
> > values. then SQL Server will have to "move" those 5 rows to the correct
place in the index tree.
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "fnguy" <fnguy@discussions.microsoft.com> wrote in message
> > news:02221714-E030-4D2F-9401-8F7A03882374@microsoft.com...
> > > Here's the scenario.  Very small table.  About 50MB.  It has 500
records.
> > > The vendor uses this table to retrieve status info about associates as
they
> > > do work.  Thus, Each record is updated every second or so.  Thus,
we're
> > > seeing about 16000 updates in a 60 minute period which appears to be
slamming
> > > the database.  I've run a profiler trace and can see the table easy
enough.
> > > There's a half dozen indexes including the clustered which is not
update.
> > > The question is, will dropping any of these indexes really do
anything.  Say
> > > for example I've got the associate last_picked item indexed and this
is the
> > > field constantly being updated.  What happens inside?  Is this moved
in the
> > > index tree?  Thanks for all help.
> >
> >
> >

Relevant Pages

  • Re: What should be my reply?
    ... Tibor Karaszi, SQL Server MVP ... "Chip" wrote in message ... >>find the recommendations to delete the tlog file. ... >>Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Indexes
    ... because we have a clustered index but I was expecting ... Only one row fit per page, so SQL Server ... You have 100000 rows in the table and 1000 rows fit per index page. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Auto Exiting Store Procedures
    ... "Peter" wrote in message ... SQL Server will terminate the batch. ... >>Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: problem with query moving data from 1 dbase to another.
    ... You register a SQL Server instance, where such an instance can have several databases. ... Tibor Karaszi, SQL Server MVP ... > you know how to connect to an existing database, ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: When an update occurs, what happens to the indexes
    ... then SQL Server will have to "move" those 5 rows to the correct place in the index tree. ... > The vendor uses this table to retrieve status info about associates as they ...
    (microsoft.public.sqlserver.server)
Loading