Expensive logical reads on INSERT - SQL 2005

Hi, I have an insert that is taking about 500ms to execute.
This is on a table with about 1,000,000 rows. The table has a number of
indexes, a PK, one default value but no relationships.
The profile displays logical reads=31 writes=6.
The statement is SQL in an app rather than a stored procedure.
Would anyone know why I have so many writes on a simple insert statement?
Many thanks,
RobertEach index requires a write, as well as the table entry.
How many indexes?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
> Hi, I have an insert that is taking about 500ms to execute.
> This is on a table with about 1,000,000 rows. The table has a number of
> indexes, a PK, one default value but no relationships.
> The profile displays logical reads=31 writes=6.
> The statement is SQL in an app rather than a stored procedure.
> Would anyone know why I have so many writes on a simple insert statement?
> Many thanks,
> Robert
>|||Hi Arnie, thank you for the answer.
I pretty much understand the writes - will be the data and the overhead of
updating the indexes. The table has a primary key index and a few
non-clustered indexes spanning one or more columns.
I do not understand the reads though. Are these to do with the indexes too?
Other insert/updates do not seem to have this overhead.
Regards,
Robert
"Arnie Rowland" wrote:

> Each index requires a write, as well as the table entry.
> How many indexes?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
>
>|||I believe that Profiler includes information such as reading system catalogs
(like sysobjects and
syscolumns, syspermissions). This might explain some of those reads. Also, l
ook ups for
check-constraints. Just a guess, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DC5309E8-85FC-4D46-9C73-9DB90F0A7347@.microsoft.com...[vbcol=seagreen]
> Hi Arnie, thank you for the answer.
> I pretty much understand the writes - will be the data and the overhead of
> updating the indexes. The table has a primary key index and a few
> non-clustered indexes spanning one or more columns.
> I do not understand the reads though. Are these to do with the indexes too
?
> Other insert/updates do not seem to have this overhead.
> Regards,
> Robert
> "Arnie Rowland" wrote:
>|||On Thu, 2 Nov 2006 11:50:02 -0800, Robert wrote:

>Hi Arnie, thank you for the answer.
>I pretty much understand the writes - will be the data and the overhead of
>updating the indexes. The table has a primary key index and a few
>non-clustered indexes spanning one or more columns.
>I do not understand the reads though. Are these to do with the indexes too?
>Other insert/updates do not seem to have this overhead.
>Regards,
>Robert
Hi Robert,
In addition to what Tibor writes, the DB will also have to read root and
intermediate level pages of the index in order to find the correct leaf
page to insert the new row.
Hugo Kornelis, SQL Server MVP