Can I push an Index hint down into a base tables from a view

  • Yeah I did think of that but presumably if I'm happy with the parameters I optimize for will give me a good(ish) plan every time then it is better to do this than have the cost of actually compiling the plan each time. Although I don't know actually know how much a compilation will cost on a plan that small, I've always tried to avoid the recompile option unless I know that its the only way to get round some sort of optional parameter problem or something.

  • Ken Gaul (2/16/2009)


    Yeah I did think of that but presumably if I'm happy with the parameters I optimize for will give me a good(ish) plan every time then it is better to do this than have the cost of actually compiling the plan each time. Although I don't know actually know how much a compilation will cost on a plan that small, I've always tried to avoid the recompile option unless I know that its the only way to get round some sort of optional parameter problem or something.

    You can get the compile times by turning on STATISTICS TIME when you run the query. It'll show exactly how much you're paying for the compile and it might be worth it. I'd test both ways to see what the gains & costs are for each approach. Be sure to use a bad parameter value with the OPTIMIZE FOR to see what happens there.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • one way to pass hints to a view is with a plan guide - certainly you can pass a maxdop for i9nstance - if you can extract the query from cache then you should be able to create a plan guide - whether you can pass one index hint i have no idea - never tried that. Have used plan guides though with some success ( with a poor app that generated sql on the fly from within it - before you all ask )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • colin Leversuch-Roberts (2/17/2009)


    whether you can pass one index hint i have no idea - never tried that.

    You can't. Plan guides are limited to the hints that appear in the OPTION clause, which index hints do not.

    The only way to force an index is to use the WITH PLAN hint, which is a little extreme.

    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
  • GilaMonster (2/17/2009)


    colin Leversuch-Roberts (2/17/2009)


    whether you can pass one index hint i have no idea - never tried that.

    You can't. Plan guides are limited to the hints that appear in the OPTION clause, which index hints do not.

    The only way to force an index is to use the WITH PLAN hint, which is a little extreme.

    And more than a little difficult to implement and maintain.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 5 posts - 31 through 35 (of 35 total)

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