Re: SQL Server 2000 UDF Intermittent Slow Execution

Plamen Ratchev (Plamen@xxxxxxxxxxxxx) writes:
To solve this problem you can assign the parameter to a local variable
and then use the local variable in the query. The value of the local
variable is not known at compilation time because it is not set until
the UDF is executed. This in effect disables parameter sniffing.

This may or may not work. There are two ways why this could backfire:

1) The query is compiled for an unknown value, and the distribution is
such that for an unknown value a scan may be best. Say for instance
that of the rows in tblPaymentTemplate, most are for schedules
that are no longer used, but there are many rows for these schedules.
Whereas active schedules are more selective.

2) When recompilation occurs because of changed statistics, this could
happen on statement level, in which case the variable may be sniffed.

Copying a parameter to a local variable mainly makes sense, in my
opinion, if the most common parameter value is actually never used in
the query. The typical case is a date parameter where NULL means "today".
Since the NULL value is not used in the query, it's better to copy to
local variable.

SQL Server 2008 adds the FORCESEEK hint.

It also adds the hint OPTIMIZE FOR @var UNKNOWN, so that you don't
need that extra variable.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: many "or" operation make system choose incorrect index
    ... Is there a join in the query? ... too many predicates would disqualify index seeks, ... Please note that there is a certain point at which the compilation time ... lsllcm wrote: ...
    (comp.databases.ms-sqlserver)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)