Re: How to monitor insert?

From: Frank Hickman (fhickman_nosp_at_m_noblesoft.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 14:25:34 -0400

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.
>
>

Relevant Pages

  • Re: Checkpoint causes need for better IO subsystem?
    ... excellent articles on clustered index selection and its impact on ... clustered index sql kimberly tripp ... Microsoft SQL Server MVP ... the random write capability of the drives comes into play, ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server Dual Processors INSERTS take long time
    ... Just because a server ... all you might try adding a clustered index on the table. ... Andrew J. Kelly SQL MVP ... > for other parts of the app, that need to be done in real time. ...
    (microsoft.public.sqlserver.server)
  • Re: Row of data interchaged automatically when import from MS xl 2
    ... CREATE TABLE OrderTest ... CREATE CLUSTERED INDEX IX_OrderTest_01 ON OrderTest ... Do not rely on the IDENTITY column in a SQL table to preserve the order ... Actually one customized tool has given for entering data to that sql ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Checkpoint causes need for better IO subsystem?
    ... Not sure about how to setup a monotonically increasing key. ... the Clustered index is on multiple cols... ... I learned this idea from SQL Server MVP Greg Linwood. ...
    (microsoft.public.sqlserver.tools)
  • Re: How to remove Not in or <> from where clause of the SQL statement.
    ... Thanks for suggesting me the alternative of the SQL statement. ... Actually the query plan of my query and ... TBLMASTERCUSTOMER and around 5000 in TBLMASTERORDERSTATUS table. ... > to do a clustered index scan than use the clustered index. ...
    (microsoft.public.sqlserver.programming)