Solved

Wierd query Elapsed Time in SQL SERVER EXPRESS 2008

Posted on 2011-09-15
11
428 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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