NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

NAV TechDays 2015 – Tips, Tricks & Tools for Troubleshooters

First of all I’d like to thank you all for attending my session at TechDays. And a BIG THANKS to Luc Van Dyck and his team for organizing another great event!

Here you can watch the session-recording:

https://www.youtube.com/watch?v=gMQqUa77nP0&index=1&list=PLLKA5LIGcLFD1Lj_rKY_asb0ptNxwKHTK

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: https://pal.codeplex.com/releases/view/21261

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:

1_Platform

 

Wait Statistics (Paul Randal).sql

Displaying SQL WaitStatistics incl. comments

2_QueryStrategy

 

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”)

3_Blocking

 

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 USE AT OWN RISK; NO WARRANTY, NO GUARANTEE, NO SUPPORT.

Have in mind the session has been recorded (incl. the talk) and this will be provided for download on MIBUSO.COM soon.

Here some links to the 3rd-party tools I was (mostly) showing (I'm not responsible for the content of these websites or products; regard their corresponding terms etc.; don't blame me):

If you recommend other cool tools for NAV/SQL Troubleshooters I'd be happy if you could drop a comment here - thanks in advance for sharing!
(I will not list any commercial (or dubious) stuff here; only freeware)

Hope this could help you to troubleshoot your Dynamics NAV!

 

Session.zip