Post reply

Strange query plan behaviour

  • April 10, 2012 at 7:28 am

    #271610

    I have a stored procedure with a number of parameters - the important one is @in_forename Nvarchar(35)

    In the stored procedure the code is

    IF (@in_forename IS NULL)

    SET @in_forename = '%%';

    ELSE

    if (@in_forename = '')

    SET @in_forename = '%%';

    ELSE

    SET @in_forename = @in_forename + '%';

    it then uses the @in_forename in the query

    if @in_forename is '%' the query returns 2+ million rows in 1 minutes 6 seconds, If @in_forename is null it returns in 6 mins + and if @in_forename is '' it takes 20 minutes.

    For each run it uses a different query plan and only for '%' is there are degree of parallelism.

    Any suggestions?

  • April 10, 2012 at 7:29 am

    #1471198

    Can you post the stored procedure and the execution plan please?

    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

  • April 10, 2012 at 7:44 am

    #1471204

    Attached

  • April 10, 2012 at 7:50 am

    #1471213

    This: http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/

    Also http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    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

  • April 10, 2012 at 9:14 am

    #1471278

    Tried to fix as parameter sniffing but still getting the problem

  • April 10, 2012 at 9:27 am

    #1471289

    Please post the revised procedure and exec plan.

    Main point, make sure you are not changing parameter values in the procedure then using those parameter values in queries

    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

  • April 11, 2012 at 2:17 am

    #1471708

    Updated proc and plan attached. Using parameter sniffing is now causing the test with forename ='%' to not use a parallel query.

  • April 11, 2012 at 5:27 am

    #1471768

    i have altered the procedure so that it builds the sql query at run time and executes it dynamically. The query now executes as a parallel query.

Viewing 8 posts - 1 through 7 (of 7 total)

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