OPTIMIZE FOR UNKNOWN

mrQQ
mrQQ Member Posts: 239
edited 2014-03-31 in SQL Performance
Hi,

on 2008+ newer builds of NAV support OPTIMIZE FOR UKNOWN, but I've noticed that only the client sets this, NAS still does the regular prepare/execute. Is this by design?

Comments

  • jensk
    jensk Member, Microsoft Employee Posts: 1
    Hi,

    As you state, SQL Server 2008 introduced the statement hint OPTIMIZE FOR UNKNOWN, to turn off Parameter Sniffing on a statement level.

    As of Microsoft Dynamics NAV 2009 SP1 and with database compatibility level set to 100 or greater, Nav will add the OPTION(OPTIMIZE FOR UNKNOWN) to queries to prevent sporadic bad query performance from SQL Server Parameter sniffing. For older version of Navision, some recommends setting SQL Server Trace Flag 4136 to request the same effect. http://blogs.msdn.com/cfs-filesystemfil ... Config.pdf

    If you collect the SQL Server statements issued by Dynamics Nav (both 2009R2 and 2013++), many statements will not specify the OPTIMIZE FOR UNKNOWN hint. These are mostly Navision Service Tier (NST) internal queries, either querying for Metadata or issued by NST Services, such as Server Heartbeat, Object Change Listener (Metadata) and Session Event Logger .

    A NAS Thread runs AL Code and should generate same hints as do a Client.

    As of Nav 5.0SP1 Classic NAS and CSide also specify OPTIMIZE FOR UNKNOWN.,

    regards
    jjens

Categories