Microsoft SQL Server forum - Using DiskIO in sp

Author

Using DiskIO in sp_who2

Thomas R. Hummel

2005-06-08, 11:23 am

Is there any way to calculate (just an approximation is fine) the
amount of work that is done for an insert statement?

For example, can I calculate the approximate row size of my table and
then compare that with the DiskIO for my SPID to determine
approximately how many rows have already been written? Or, does the
DiskIO include shuffling data around or other DiskIO that makes this
kind of comparison impossible?

Thanks,
-Tom.

Erland Sommarskog

2005-06-09, 3:23 am

Thomas R. Hummel (tom_hummel@hotmail.com) writes:
> Is there any way to calculate (just an approximation is fine) the
> amount of work that is done for an insert statement?
>
> For example, can I calculate the approximate row size of my table and
> then compare that with the DiskIO for my SPID to determine
> approximately how many rows have already been written? Or, does the
> DiskIO include shuffling data around or other DiskIO that makes this
> kind of comparison impossible?

First of all, you need to take indexes in account. And you would really
have an idea of how many page splits your INSERT till cause. If you
insert rows at the end of the clustered index, there is no so much
page splits (but could still be in the NC indexes). If your INSERT is
not correlated with the clustered indexes, then there can be a lot more
page splits.

One more thing to account for is checking of FK constraints. I don't
really know when they set in.

And, um, you need to get the rows from somewhere. If you are doing
INSERT SELECT, then the SELECT eats disk IO too.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links