Do Hints Work Anymore?

  • I have seen something like the "parameter sniffing" in my own experimentation. When you run in QA its probably something like

    exec procedurename 'paramval1', 'paramval2'

    In other words the literally-supplied values are taken into account for optimisation.

    To reproduce the effect of parameters that cannot be determined at compile-time, run in QA like this:

    declare @var1 varchar, @var2 varchar

    set @var1 = 'paramval1'

    set @var2 = 'paramval2'

    exec procedurename @var1, @var2

    SQLServer (last time I tried it) is dumb enough that it does Not take the assigned values into account in optimisation.

    BTW always use DBCC DROPCLEANBUFFERS between retries for testing performance.

Viewing post 16 (of 15 total)

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