Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
OPTIMIZE FOR UNKNOWN
mrQQ
Member Posts: 239
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?
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?
0
Comments
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