Re: How to monitor insert?

From: Jerry Pisk (jerryiii_at_hotmail.com)
Date: 05/22/04 

Date: Fri, 21 May 2004 23:00:29 -0700

If you're doing a lot of data modification then you shouldn't use clustered
indexes at all, no matter if they're over a single column or over 20
columns. Identity column might be ok if you're only doing inserts (since the
table will mostly grow at the end) but still, once you start deleting or
updating rows a lot the data reorganizing is just not worth the little
performance gain you get, especially if you're selecting few records at a
time (as opposed to millions of records, then the slowdown will be
noticeable).

Jerry

"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:V5mdncNworem1DPdRVn-sA@comcast.com...
> I'd say your biggest problem is the number of fields you have specified in
> your clustered index not to mention the mixing of the data types. SQL has
> to convert all of the fields to a common type then sort through the
current
> data and move records as needed to insert the new record. The fill factor
> does make a difference but with all you have going on in there I don't
think
> it would make that much of a difference. If you need the records in that
> order I'd suggest creating a non-clustered index and using the clustered
> index only on the fields that uniquely identify a row in the table such as
> an identity column.
>
> --
> Frank
>
> "Microsoft Newsgroups" <scot.no.spam.curry@btopenworld.com> wrote in
message
> news:OcahcH0PEHA.252@TK2MSFTNGP10.phx.gbl...
> > When I am performing an Insert into a table using Query Analyzer, it is
> > taking 2 minutes 30 seconds. The table has 1,170,548 rows.
> >
> > There is a clustered Index that scripts out to
> >
> > CREATE CLUSTERED INDEX [IX_1_39_varcharIndex] ON BusinessDate,
> ContentType,
> > CTMapID, BZMapID, IndexMapID, Value, COMapID, COVID, DocID, Page ON
> > [Primary}
> >
> > We have a CONSTRAINT on:
> > BusinessDate, CoMapID, COVID, DocID, Page, PageOffset, Value
> >
> > I cannot seem to figure out how to determine what is happening during
the
> > insert. I am guessing that is has something to do with FILLFACTOR, but
I
> am
> > not sure how to find information in Performance Monitor or SQL Profiler.
> > Could someone please point me in the right direction?
> >
> > Thanks in advance for your help.
> >
> >
>
>