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

Start off by capturing stats for the usp_peopleInsert stored procedure with an empty people table.
To make sure the server is in a cold state, use the following commands before each cold run to flush
memory to disk, and make sure you get a full stored procedure compile cycle on the first run. This is
much faster than restarting SQL Server every time and gives good repeatable results.
dbcc dropcleanbuffers
dbcc freeproccache
Now run the stored procedure and see how fast it goes by using this script:
use People
go
truncate table people
go
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
set statistics io on
go
-- Cold run
exec usp_peopleInsert
go
-- first warm run
exec usp_peopleInsert
go
-- second warm run
exec usp_peopleInsert
go
-- third warm run
exec usp_peopleInsert
go
set statistics time off
set statistics io off
go
-- we ran the SP to insert 2 people 4 times, so we should have 8 people in the DB
select count (*) from people
go
The following are the results of executing the procedure several times:
Cold Run
SQL Server parse and compile time: