SQL Server Performance

Query Elapsed Time confusion

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by anonymous2009, Sep 15, 2011.

  1. anonymous2009 New Member

    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!
  2. Luis Martin Moderator

    Welcome to the forums!.
    Did you test after or before sql server take all available memory?
  3. anonymous2009 New Member

    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
  4. preethi Member

    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.
  5. anonymous2009 New Member

    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.
  6. anonymous2009 New Member

    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
  7. Shehap MVP, MCTS, MCITP SQL Server

    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
    FrankKalis likes this.
  8. satya Moderator

    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.

Share This Page