January 18, 2017 at 3:24 am
Hi folks
what happens when you execute same query with multiple times
when i set statistics io & time on why it shows diffent duration /time
please reply<
January 18, 2017 at 3:27 am
I'm not sure what you're looking for, When a query is run multiple times, it runs multiple times, nothing more complicated than that.
Durations and CPU will vary slightly for the same query, with stats time I suspect that's more measuring errors than anything else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2017 at 7:11 am
Assuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 18, 2017 at 7:56 am
Eric M Russell - Wednesday, January 18, 2017 7:11 AMAssuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).
And possibly additional time for compiling the query if there's not already a plan in cache.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2017 at 9:23 am
Grant Fritchey - Wednesday, January 18, 2017 7:56 AMEric M Russell - Wednesday, January 18, 2017 7:11 AMAssuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).And possibly additional time for compiling the query if there's not already a plan in cache.
Yeah, I always hate sitting there waiting an extra 100ms for my query to compile the first time through. 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 18, 2017 at 10:47 am
Eric M Russell - Wednesday, January 18, 2017 9:23 AMGrant Fritchey - Wednesday, January 18, 2017 7:56 AMEric M Russell - Wednesday, January 18, 2017 7:11 AMAssuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).And possibly additional time for compiling the query if there's not already a plan in cache.
Yeah, I always hate sitting there waiting an extra 100ms for my query to compile the first time through. 🙂
Yours take that long? Ha!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2017 at 11:27 am
Also, if your SQL contains DDL statements, they'll be run each time you execute. If you have INSERTs, you'll probably have duplicate rows.
If you have DML to alter procedures, etc, any replaced versions will have all plans associated with them invalidated. Then you have to wait that extra 100 ms all over again.
Anything you run gets written to the transaction log.
Like Gail started out with, when you run it multiple times, it runs multiple times. Are you looking for something specific?
January 18, 2017 at 5:54 pm
Grant Fritchey - Wednesday, January 18, 2017 10:47 AMEric M Russell - Wednesday, January 18, 2017 9:23 AMGrant Fritchey - Wednesday, January 18, 2017 7:56 AMEric M Russell - Wednesday, January 18, 2017 7:11 AMAssuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).And possibly additional time for compiling the query if there's not already a plan in cache.
Yeah, I always hate sitting there waiting an extra 100ms for my query to compile the first time through. 🙂
Yours take that long? Ha!
I'm fighting a problem right now where some lovely ORM generated code adds something like 30 different criteria to the WHERE clause. Because of its complexity and the constantly changing data it's provided, it ALWAYS recompiles and it takes 2 to 15 SECONDS to do so. It get's called several thousand times every hour. We're replacing it with a stored procedure. The real key is that one of the items that it passes to the WHERE clause is the PK for the table. One of the reasons why it takes so long to recompile is because the idiotic stats maintenance code that my predecessors used built stats on every single column of every single table and this particular table has 139 columns. Yeah... working on that lovely issue, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply