Why does same query return different runtimes

[Tison]

I am running the following query on the same database 3 times in a row.
Why does it give different results ?
update testtable
set yoda_shut_dt = getdate()
where lob_fund = "LIFE"
and lob_org_risk_ind <> &quot;RP&quot;

The results ;
1st run;
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: steve_test scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: steve_test scan count 1, logical reads: (regular=16019 apf=0 total=16019), physical reads: (regular=1763 apf=14256 total=16019), apf IOs used=14256
Total writes for this command: 3516

Execution Time 12.
SQL Server cpu time: 1200 ms. SQL Server elapsed time: 8076 ms.
8550 row(s) affected.

2nd run;
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: steve_test scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: steve_test scan count 1, logical reads: (regular=16019 apf=0 total=16019), physical reads: (regular=3005 apf=13014 total=16019), apf IOs used=13014
Total writes for this command: 5002

Execution Time 14.
SQL Server cpu time: 1400 ms. SQL Server elapsed time: 7690 ms.
8550 row(s) affected.

Third run ;
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: steve_test scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: steve_test scan count 1, logical reads: (regular=16019 apf=0 total=16019), physical reads: (regular=1268 apf=14751 total=16019), apf IOs used=14751
Total writes for this command: 3588

Execution Time 10.
SQL Server cpu time: 1000 ms. SQL Server elapsed time: 8056 ms.
8550 row(s) affected.

 

[r937]

because when you update a row, it is written back to some place else on the disk? just a guess

you'll probably get a better answer in the microsoft sql server forum forum183

rudy

 

[fluteplr]

Is sql server the only application running on the server? Are you the only user?

And frankly you are talking about a difference of < 400 millisecond, who cares?

Even if you are the only user and sql server is the only application, the server pc will be doing housekeeping periodically which will make for small differences in run times.

If I were you I would be wondering why updating < 10000 records took approx 8 seconds, considering I just did a similar update on 250,000 records that completed in 24 seconds.

 

[tlbroadbent]

There are a number of factors that affect the time and IO statistics. If the requested data is in cache, then physical IO will be less than if the data must be read from disk and the query will be faster. It is common to run a query twice in a row and see dramatic speed improvement because data was loaded to memory by the first query so the 2nd simply read from cache.

If SQL Server must grow the transaction LOG file in order to write the transactions, more time will be required. If the server is busy and paging occurs - OS paging and SQL Server data cache paging - then queries can run slower. An update might be blocked momentarily by another process resulting in greater elapsed time.

In other words, if the server has any level of activity other than your query, the statistics will vary from execution to execution. Often, the statistics vary even when your process is the only user process because SQL Server runs processes in the background.

I also recommend forum183 for SQL Server questions. Read faq220-1073 (What is ANSI SQL? Why This Forum?) to find out why. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.