Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Wierd query Elapsed Time in SQL SERVER EXPRESS 2008

Posted on 2011-09-15
11
Medium Priority
?
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 5

Accepted Solution

by:
25112 earned 832 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Assisted Solution

by:deighton
deighton earned 168 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
 

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 832 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 832 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 832 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 832 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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