Solved

Wierd query Elapsed Time in SQL SERVER EXPRESS 2008

Posted on 2011-09-15
11
419 Views
Last Modified: 2012-05-12
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? I'm not able to reproduce consistent results.

Thanks.


0
Comment
Question by:sath350163
  • 6
  • 4
11 Comments
 
LVL 5

Accepted Solution

by:
25112 earned 208 total points
ID: 36546765
good observation.

The reason is buffers were removed with your commands, but further 'clean-up' is required. (You need yet to free system cache.)

add this command:
      DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;
basically cleared unused cache entries (clean -up process)

http://msdn.microsoft.com/en-us/library/ms178529.aspx

also if you have distributed queries, you will need this too.
http://msdn.microsoft.com/en-us/library/ms187781.aspx
DBCC FREESESSIONCACHE

Add these 1 or 2 commands, depending on your query and you will see 100% exec plans from scratch each time.
0
 

Author Comment

by:sath350163
ID: 36546910
Thanks.

I do not have distributed transaction.
I tried your solution and still query comes back in 4 seconds.
Basically this is what I did,
1. I disconnected from the database instance
2. Reconnected to the database instance
3. DBCC FREEPROCCACHE
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;
4. Disconnected from the database instance
5. Reconnected to the database instance
6. Executed my query which returns results in 4 seconds.


So the issue still seems to be there.
Is there something else that may be causing this?
0
 

Author Comment

by:sath350163
ID: 36547328
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;




0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 42 total points
ID: 36548206
there could be other physical buffering going on, disk buffers etc, not just the buffering that SQL does.

Many things on a PC seem to work faster the second time you use them.
0
 
LVL 5

Expert Comment

by:25112
ID: 36548696
could you afford to restart sql service or even better could you physically  reboot your server and then try the query, so that your physical drive buffers are also cleared up, in reference to the SQL instance?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sath350163
ID: 36551518
1. I shut down all SQL SERVER related services and restarted my machine.
I just had outlook and excel running.
Before executing the query, I ran the below sql to check if the dirt pages were cleared.
But I still see 35 of them.

select count(page_id) as 'dirty pages'
from sys.dm_os_buffer_descriptors
where is_modified =1;
--O/p:
dirty pages
35

2. Then did the below,
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

3. Ran my query. It comes back in 4 seconds.

Questions 1:
Are the dirty page the reason for the fast response time?
Are the results coming right from those dirty pages.

Question 2:
Is there a way to clear those dirty pages?

Question 3:
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.
For instance, after running the my query in step 3 which took 4 seconds, I stepped out for 1/2 hours and re-ran the same query. It took 8 seconds.
Is there a reason for this?
It seems like some sort of flushing happens slowly.
Because nothing happened between my previous post and this post.

Question 4:
Does SQL SERVER EXPRESS go into idle mode when left unused for some period of time?
If so, will that have some impact on the query response time?

Question 5:
What is the best way to baseline the query response time? Is there a standard set of steps that can be followed? I get pretty inconsistent results each time the same query is executed.

I'm very new to sql server. My apologies in case I'm asking too many obvious questions.

Thanks.
0
 
LVL 5

Assisted Solution

by:25112
25112 earned 208 total points
ID: 36560760
can you check if your query is still cached in the DMVs:

SELECT text FROM  sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(SQL_HANDLE)
where text like '%SELECT * FROM ABCTABLE WHERE XYZ = 4%'

in the last sentence, substitute part of your query in there..
0
 
LVL 5

Assisted Solution

by:25112
25112 earned 208 total points
ID: 36560895
>>Questions 1:
Are the dirty page the reason for the fast response time?
Are the results coming right from those dirty pages.

Question 2:
Is there a way to clear those dirty pages?
-------------------------

run the following query.. you will see that the dirty pages are from the system databases and not yours which you just cleared.. the system is always working on itself! can you confirm?
select distinct db_name(database_id),object_name(object_id)
from sys.dm_os_buffer_descriptors a join sys.allocation_units b 
on a.allocation_unit_id = b.allocation_unit_id 
join sys.partitions c on b.container_id = c.hobt_id and type in ( 1,3)
where is_modified =1
union
select distinct db_name(database_id),object_name(object_id)
from sys.dm_os_buffer_descriptors a join sys.allocation_units b 
on a.allocation_unit_id = b.allocation_unit_id 
join sys.partitions c on b.container_id = c.partition_id and type in ( 2)
where is_modified =1
order by 1

Open in new window

0
 

Author Comment

by:sath350163
ID: 36563939
I do not see the query cached in DMV and you are right, dirty pages are from tempdb.

Is SQL SERVER 2008 EXPRESS service restart the only option?
The database instance is in my local PC and I can afford to do that.

Thanks.
0
 
LVL 5

Assisted Solution

by:25112
25112 earned 208 total points
ID: 36573956
>>Is SQL SERVER 2008 EXPRESS service restart the only option?
The database instance is in my local PC and I can afford to do that.

Yes, restart of instance alone resets so many aspects of SQL Server.. even tempdb is recreated at service restart..
also i would encourage you to check out of one of your queries that takes much longer.. maybe a minute or so.. (if you such complex queries) and then try to baseline it.. with the above methods.. it will be more prominent with the big queries..

0
 
LVL 5

Assisted Solution

by:25112
25112 earned 208 total points
ID: 36573961
also, beyond this simple way to testing, you should also consider using profiler and perfmon together to weed it out too..
http://www.brentozar.com/sql-server-training-videos/perfmon-and-profiler-for-sql-server/
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now