Directions EMEA 2016 – SmartQuery Tuning

NAV/SQL Performance – Optimizing [not-so?] Smart-Queries


Query- and Index-Tuning in general are the most important aspects in optimizing performance in Dynamics NAV. Since NAV 2013 here a new “challenge” was introduced: SmartQueries! These SQ often turn out not being so smart at all, then causing seriously degraded performance and user-experience.
In this session it will be explained how to troubleshoot such issues, discussing options about diagnosis and resolution; also highlighting the special challenges in Cloud based scenarios.


Please find attached the related download-package containing the PowerPoint slides, Examples and essential Tuning Scripts.

Directions EMEA

NAV TechDays 2016 – Announcement

Registration is now open for NAV TechDays 2016, from 17th-18th November 2016 in Antwerp/BE.

Attend the greatest NAV conference in the world here:

As before, there will be also several pre-conference workshops – but this year there’ll be TWO workshop days – 15th & 16th November 2016!
Due to the throughout positive feedback I’ll – again – present the workshop “NAV/SQL Troubleshooting Essentials“. This is a compressed version of the regular “Masterclass” training which normally takes two days. Though, we have decided to still run the ONE day workshop (= compressed content) and repeat that on day number TWO!
This should give you the chance to attend two different workshops at NTD if you like; also I’m able to present the content to a greater audience, as in the past my workshops have been fully booked very quick.
See the sessions (yet not complete) and workshops here:

Looking forward to meeting you in Antwerp!

Troubleshooting Essentials

This is the recording of my presentation at NAV TechDays 2015 in Antwerp; about “Tips, Tricks & Tools for Troubleshooters“.
This sort of wraps up all the areas we need to investigated in terms of “Performance Optimization” ad highlights how to approach the issues.
Further I’m introducing several tools which are IMHO quite feasible to get the job done.

A similar article was posted on my old BLOG space (actually it is the last article there), too, but I’d like to have this available here, as well, since I consider this most important and a good starting point for troubleshooters.

Please find attached to this article the download package according to the session. Here a brief description of the scripts etc. it includes:

File/Folder Description
NAVTD 2015 NAV SQL Troubleshooting – STRYK – Session 1_4.pptx The PowerPoint slides
PerfMon Templates Templates for PERFMON.EXE In case of a SQL “Named Instance” you’ll have to modify the template – see Readme.

To process the output-data with PAL: PAL basically only works with US localizations. I have attached a modified PAL.ps1 Powershell script which is supposed to run on any localization. Further, I have removed/modified some output according to my demands – that’s the PAL script I use personally. Those of you running non-English PERFMON can translate the counter names into English using PLT.

Profiler Templates Templates for SQL Profiler; SQL Server 2005, 2008, 2008R2, 2012 and 2014. CAUTION: these templates are not pre-filtered! Using Profiler without filters could create an immense amount of trace data which kills your disk! Apply filters as demanded, e.g. Reads >= 1000 AND Duration >= 20
Scripts All kinds of TSQL Scripts and Examples for NAV/SQL Troubleshooting:
Wait Statistics (Paul Randal).sql Displaying SQL WaitStatistics incl. comments
QEP_MissingIndexes.sql Investigates SQL Procedure Cache to find/analyze Expensive Queries; displaying “Missing Index” proposals (naming “ssi%”) CAUTION: as mentioned during the session: NEVER EVER simply APPLY these proposals without VERIFICATION and CLEAN UP! Else you will cause other problems instead of resolving them!
ReScript_SSI_Indexes.sql Displays and documents custom built indexes; creates code for CREATE and DROP (based on naming “ssi%”)
VerifyIndexUsage_SSI.sql Displays Index Usage of custom built indexes CAUTION; statistic is reset on restart of the SQL Server! You need a reliable uptime of the server for sufficient statistics! (based on naming “ssi%”)
template_TraceCheck(generic2).sql Script to analyze/group SQL Profiler Traces
template_NavSQLTraceAnalysis.sql Script to analyze/group SQL Profiler Traces; incl. NAV Call Stack (by “SQL Trace”)
Block_1_{}.sql to Block_6_{}.sql Scripts to establish event-triggered Block Detection (incl. analysis)
Deadlock_1_{}.sql to Deadlock_3_{}.sql Scripts to establish background tracing of Deadlock Graphs (incl. analysis) Well, in the session I only showed you to export the Deadlock Graphs to XML to be opened in Excel. OK, I decided to share another script (didn’t show this in the session), which is way more convenient: “Deadlock_2_TraceCheck_ssi_dlg_check.sql” creates a Table and a Stored Procedure; this SP could read the Deadlock-Trace file directly and used some SQL magic to parse the XML and saves it into the table. “Deadlock_3_TraceCheck_template.sql” shows how to use that.
NAV2013 Some special features only for NAV 2013 and higher. The problem is that without “User Delegation” we hardly can identify an individual user – involved in blocking – from SQL side.
NAV2013_SessionTrace.sql NAV2013_SessionTrace.tdf SQL Profiling (GUI template or TSQL script) to record the “NAV Call Stack”. This requires to have the “SQL Trace” feature up running on the designated NAV Service Tiers. This trace-data could be used to assign the NAV User ID to the Block Detection recordings. CAUTION: running this profiling could create an immense amount of trace data, which potentially kills the disk! NEVER run this unattended; watch the trace-file size/growth carefully. Use this feature only temporary!
GetSessions_NAV2013.sql GetSessionsBlocks_NAV2013.sql Templates showing how the “Session Trace” could be used and assigned to the Block-Recordings.

Please regard, that most of these features work generic, e.g. independently of any application. Means, you could use that not only with NAV, but with any other SQL database as well!

Everything you use, you


Hope this could help you to troubleshoot your NAV!


Welcome to my new BLOG space on WordPress!

Well, for many years my BLOG was hosted at, but now I’m starting to host it on my own Webspace using WordPress.
You’ll still find the old articles there as I probably will not move them to the new site (at least not in near future).

All new articels will be published here, but as I’ll keep the old BLOG space I’ll put a link-reference there.

Hope you enjoy the new site; your feedback is much appreciated!