Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 370

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 36 ms.
There is some very useful information in this output. Looking at both the cold and warm run outputs,
you can see that they are both pretty fast, with a cold run elapsed time of 217 milliseconds (ms) and a
warm run elapsed time of 36 ms.
Looking at the cold run stats and focusing on the number of physical reads, you can see that there were a
total of 10 physical reads, 1 to read the boysnames table, 1 to read the girlsnames table, and 8 to read the
lastnames table into memory.
The warm run stats show that there were no physical reads, only logical reads. The parse and compile
time was also greatly reduced. This tells you that the query didn't need recompiling, which is good, as it
will save you a lot of time each time it's called.
The warm run stats also show that it's taking about 30 to 40 ms for each insert.
Given that you are only issuing 10 reads to execute the query, and that repeated calls don't invoke addi-
tional physical reads, it's going to be hard to improve performance by further reducing these already low
numbers. It's also going to be hard to see any small time-based improvements when the time taken is
already so small at just 30 to 40 ms.
To make it easier to observe small changes in performance, you are going to need to execute the queries
hundreds or thousands of times and then look at the overall stats for a very large number of executions.
This will help highlight any small changes.
To do this you need to put the script into a loop and run it thousands of times to see if that gives a better
measurement. Use the following command line statement:
sqlcmd -E -d people -Q"exec usp_loopPeopleInsert 3000, 500"
These are the results of the usp_loopPeopleInsert stored procedure:
Inserted 1000 people in 670mS at a rate of 1492.54 per Second
Inserted 1000 people in 720mS at a rate of 1388.89 per Second
Inserted 1000 people in 656mS at a rate of 1524.39 per Second
Inserted 1000 people in 686mS at a rate of 1457.73 per Second
Inserted 1000 people in 720mS at a rate of 1388.89 per Second
The inserts are going fast enough that you are getting between 1400 and 1500 inserts per second.
Now you should look to see what you are waiting on. To do that, you modify the sqlcmd line as follows
so it would run for considerably longer. Then you can query the sys.processes table to see what the
wait types are for your query. Here is the modified cmd line:
sqlcmd -E -d people -Q"exec usp_loopPeopleInsert 20000, 1000"
This is the query that will let you monitor what you are waiting on:
set nocount on
while 1 > 0