Query Elapsed Time confusion | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Elapsed Time confusion

Hello,
Below is what I’m doing to figure out the elapsed time for a query:
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
<Execute the query> –Takes 7 secs
<Execute the query> –Takes 4 secs
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
<Execute the query> –Takes 4 secs

– As shown above, when I run the query very first time it took 7 seconds.
– When I reran the same query it took 4 seconds.
– Then I did DBCC FREEPROCCACHE, CHECKPOINT, and DBCC DROPCLEANBUFFERS, and then reran the same query. I was hoping it to take 7 seconds. But it took only 4 seconds.
– Does that mean that the DBCC FREEPROCCACHE, CHECKPOINT, and DBCC DROPCLEANBUFFERS did not free anything at all. Can somebody please clarify? Thanks!
Welcome to the forums!.
Did you test after or before sql server take all available memory?
Not sure.
How do I find that out? More Details:
I’m running SQL SERVER 2008 EXPRESS EDITION in Windows 7 operating system.
I see some vague results in elapsed time. I’m calculating the elapsed time for a query using the following approach:
DECLARE @dt DATETIME
SET @dt=GETDATE()
<your query here>
SELECT DATEADIFF (s,@dt,GETDATE()) Following is what I did:
1. Disconnected from the database instance.
2. Reconnected to the database instance.
3. DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
4. Checked master.dbo.syscacheobjects for tables referenced from the query.
I dis not see anything cached.
5. Ran the below sql to see if EXECUTION PLANS are fully flushed.
I did not see any execution plan cached.
SELECT [sdecp].[refcounts]
,[sdecp].[usecounts]
,[sdecp].[objtype]
,db_name([dest].[dbid]) as database_name
,object_schema_name([dest].[objectid], [dest].[dbid]) as [schema_name]
,object_name([dest].[objectid], [dest].[dbid]) as [object_name]
,[dest].[text]
,[deqp].[query_plan]
FROM sys.dm_exec_cached_plans sdecp
CROSS APPLY sys.dm_exec_sql_text(sdecp.plan_handle) dest
CROSS APPLY sys.dm_exec_query_plan(sdecp.plan_handle) deqp
where [dest].[dbid] is not null
order by database_name, usecounts desc
6.Executed my query which come back in 10 seconds.
7. Again executed the same query immediatedly and the results come back in 6 seconds.
8. Again executed the same query immediatedly and the results come back in 3 seconds.
9. DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
10. Executed the query and the results come back in 5 seconds.
11. Disconnected from database instance
12. Reconnected to the database instance
13. Executed the query and the results came back in 5 seconds. Question 1:
If in step 9 all stored procedure execution plans are flushed and the Buffers are wiped, shouldnt the query execution in step 10 take 10 seconds similar to step 6 to return results back?
How come the query all of a sudden only takes 5 seconds in step 10. Question 2:
In step 13, shouldnt the query take 10 seconds to return back results.
In step 11 and 12, I disconnected from the databse instance and reconnected back, which is similar to step 1 and step 2.
If step 6 (followed by db instance disconnect and reconnect) took 10 seconds, then shouldnt step 13, which is similar to step 6, also take 10 seconds. Am I doing something wrong or Is myunderstanding wrong? Thanks
Few observations:
  1. Disconnecting from and reconnecting to a database instance does little change in cached plans, cached data etc.
  2. For each query execution, SQL Server allocates memory before the execution. If SQL Server does not have enough memory, it will request from OS. Once requested, it will not be returned unless OS asks some memory. (When OS gets memory pressure it will request memory from other applications including SQL Server) Allocating memory may take some time.( but 3-4 seconds is far too much)
  3. If your query is simple, SS may think that keeping the plan is costlier than recreating the plan, and it will throw away the plan.

Thanks. Latest elapsed time:
1. Left my SSMS unused for 1/2 hour
2. DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
3. Executed my query. It takes 7.6 seconds.
So elapsed time went from 5 seconds to 7.6 seconds.
4. DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
5. Executed my query. It now takes 3.3 seconds. One thing I have observed many times is that whenever I run my query after not using SSMS/running any other query for a while, the elapse time goes up. That was the case in step 3.
It seems like some sort of flushing happens slowly.
Because nothing happened between my previous post and this post.
Addition to the above questions: Below query to identify dirty pages, returns back with count as 5.
select count(page_id) as ‘dirty pages’
from sys.dm_os_buffer_descriptors
where is_modified =1; How to flush those dirty pages? These dirty pages remain even after doing the following,
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL; Thanks
As a base line for caching techniques , they are multiple:
  • Process Cach
  • System Cach
  • Session cach
  • Query execution plan cach
  • ..etc

Therefore , if you have a scheduled memory Managment process for memory recycling like : 1- Preserving the maximum Memory of server almost like 120 GB of 128 GB
2- Then running all of Cach recycling using
  • DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;
  • DBCC FREEPROCCACHE
  • DBCC FREESESSIONCACHE
3- Then reducing Maximum memory to a proper percent like 30 % ( 30 GB) . 4- Then waiting for a while until all last preserved memory to O.S 5- Then returning back the maximum memory setting 120 GB You would notice that this small practice takes more time if running Every 24 hours due to much cach there and so small time every few minutes Since the button line here , it is accroding to how buffer size and so how buffer recycle time should take
Mind you the edition of SQL that you are using is limited by the features to take advantage of physical resources, such as memory and CPU power in this case.
So irrespective of DBCC statements that you are generating its always depends on how many rows/data you are trying to recover with the queries. The best baseline is to test on server based editions such as workgroup/standard/web in thsi case.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |