Post reply

Query Tunning with each run of Fresh/Blank CACHE in SQL Server 2012 Express

  • June 4, 2015 at 5:07 am

    #304713

    Hi Folks,

    1-

    I am using SQL Server 2012 Express.

    I am doing performance tuning of SP/Query in Dev-Test environment.

    with help of Query execution plan i am getting some idea/help.

    But while reading various articles, i found that SQL Server caches plan between successive executions.

    so if i test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...

    Means i am not getting correct answer...

    2-

    I found this 2 commands:

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS

    But they say that executing above command might interfere/bother other people executing other query/sp on this server.

    They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

    3-

    Part of query was using Dynamic-SQL executed with EXEC command.

    I replaced that with SP_EXECUTESQL.

    4-

    So can you suggest that how can i start testing of each SP-run with Fresh/Blank CACHE ?

    Any article/thought/help would be appreciated.

    Thanks

    devsql

  • June 4, 2015 at 5:31 am

    #1802946

    You can, but personally I don't like that approach. It means you may see different behaviour to in prod, when the plans are cached.

    Instead just ignore durations/times of the first execution (the one that has the overhead of plan generation)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply