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
<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
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.
,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]
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
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.
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? I'm not able to reproduce consistent results.