Post reply

Inconsistent Reads after tuning the query

  • December 10, 2012 at 10:30 pm

    #271406

    Hi All,

    I am very new to Performance tuning hence any help provided will be appreciated.

    I hav a batch query which needed to be tuned for high Reads. I had the following observations -

    1. RID Lookup

    2. Key Lookup

    3. A lot of nested CASE statements used in SELECT.

    4. CASE Statement used in group by as well.

    5. IsNull used arbitrarily.

    I did the following -

    1. I created covering index for two tables to get rid of the lookups.

    2. Modified the CASE statements to reduce the nesting.

    3. removed CASE from the group by section.

    4. Removed unnecesary IsNull

    Now when I check the profiler for Old (old query w/o indexx) vs New performance (updated query with indexes), I see the results vary with every execution.

    The CPU time has reduced however not much difference in the Reads. In fact in the first run the CPU came down considerably bt the Reads shot up .

    Can someone let me know how I can evaluate the % increase/decrease in performance?

  • December 10, 2012 at 10:37 pm

    #1567253

    Please Post both the queries along with the table structure and execution plan .. it will be more helpfull

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • December 10, 2012 at 11:02 pm

    #1567257

    The batch is big. Can you let me know how how I can check the performance benefits.

  • December 10, 2012 at 11:24 pm

    #1567261

    without DDL and Execution plan its difficult to say anything.

    but still check the Estimated Cost in execution plan.

    Run both the queries in single batch and compare execution plan

    check which query having less cost

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • December 11, 2012 at 2:32 am

    #1567304

    nidhis (12/10/2012)

    The batch is big. Can you let me know how how I can check the performance benefits.

    Compare duration, CPU and reads as well as the consistency of the performance.

    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

  • December 11, 2012 at 3:53 am

    #1567347

    Thank you. Also - the plan changes when I use the same batch using sp_ExecuteSql

    I see there are no Key Lookups when I run the same query using sp_executesql as opposed to when I execute the batch on SSMS.

    The .net code uses sp_executeSQL.

    Is my approach of picking the batch and running it separately on SSMS OK?

  • December 11, 2012 at 4:34 am

    #1567367

    nidhis (12/11/2012)

    Thank you. Also - the plan changes when I use the same batch using sp_ExecuteSql

    I see there are no Key Lookups when I run the same query using sp_executesql as opposed to when I execute the batch on SSMS.

    The .net code uses sp_executeSQL.

    Is my approach of picking the batch and running it separately on SSMS OK?

    sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

  • December 11, 2012 at 5:41 am

    #1567398

    SQLSACT (12/11/2012)

    sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

    Not necessarily. Ad-hoc batches can use cached plans.

    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

  • December 11, 2012 at 5:44 am

    #1567399

    GilaMonster (12/11/2012)

    SQLSACT (12/11/2012)

    sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

    Not necessarily. Ad-hoc batches can use cached plans.

    Simple Parameterization?

  • December 11, 2012 at 5:55 am

    #1567401

    Parametrisation is not required for plan reuse. Parametrisation results in better reuse of cached plans, it's not a requirement for any having any reuse at all.

    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 10 posts - 1 through 9 (of 9 total)

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