NAV/SQL Performance - My Two Cents

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

News Update  9th June 2010:
Recommended by Microsoft, see

>> 09.12.2009 - Important Notice

In the past days I received several complaints that (and probably, too) are still shipping the old - 3rd - edition. This is due to a big mistake Embarrassed by my publisher "Books on Demand". I reported the issue to them and they are in progress to fix it instantly.
So if you received an old edition please send it back to the book-store, ideally with a little notice.
To all international customers I recommend to visit MS Dynamics Books - their website is in German and English and they also ship international:

Those of you who want to order 25+ books could contact me directly to place a bulk order at BOD.

I'm sorry about this inconvenience.




While struggling with NAV/SQL performance issues for many years I scribbled down countless pages about various issues, learned by own experience or from colleagues, taken from numerous knowledgebase articles, whitepapers, etc. or heard in several webcasts.

In 2006 I started to put all this into a structured document, one I could use more straight in my optimization workshops - so "The NAV/SQL Performance Field Guide" was born. It's not a replacement of any official NAV/SQL documentation and requires some pre-knowledge about certain issues, but this "braindump" should give feasible advice and practical hints for the daily work with NAV and SQL.

At Convergence 2007 in Copenhagen I got the chance to present this little booklet to some other "Navisioners" and I got throughout a positive feedback! This is why I dare to mention here, that I recently published this PFG on "Books on Demand".

In October 2008 the 3rd edition was published; and on 23rd October 2009 the latest version was published!

The NAV/SQL Performance Field Guide 

NOW AVAILABLE - 4th Edition (2009) !

The new version was - of course - reviewed, corrected, enhanced and expanded. Actually all areas have beed improved. Now there is a stronger emphasis on SQL Server 2005 and 2008, focusing more on the new technologies, including NAV 2009 and SQL Server 2008.

Thanks to all who sent me their precious feedback to improve this little booklet - hope we could continue this progress!

4. Edition (2009)
ISBN: 978-3-8370-1442-6
184 Pages (Paperback, English, b/w), 17.90 EUR (19.90 USD, 14.90 GBP)
Printed & Published by "Books on Demand" 

In total it's 28 pages more, but I could keep the old cheap price (this book is sold at cost (almost))! And another good news: now it is also available in US, Canada and UK!

Here some distributors:


Table of Content:

Imprint 2
Document Version 3
Legal Notice 4
Table of Content 5
About the Author 8
From the Author 9
Preface 10
Performance Toolbox 11
What is “Performance”? 12
> Performance Troubleshooting 13
Finding “Bottlenecks” 15
> Windows Performance Monitor 16
> NAV Client Monitor & Code Coverage 23
> SQL Profiler 24
>> Basic Interpretation of Execution Plans 26
> Correlation SQL Profiler and Performance Monitor 27
Avoiding the Trouble – Fundamental Setup 28
> Server Hardware Environment 28
>> Moving Databases 37
> Client Hardware Environment 40
> Citrix/Terminal Server Hardware Environment 41
> Middle Tier Environment (NAV 2009) 42
> Used Version and Edition of the Operating System (OS) 43
>> Memory – 32bit Architecture 43
>> Memory – 64bit Architecture 46
>> Processors 47
> Used Version and Edition of the SQL Server 49
> Configuration of SQL Server and Database 51
>> SQL Server Instance Settings 51
>> Database Settings 54
>> NAV Client Settings 57
>> Example TSQL for Configuration 61
> Optimizing “tempdb” 63
Fixing the Trouble – Erasing problems 64
> Structure of Indexes in NAV 65
>> Reduction of the number of Indexes 69
>> Optimizing the order of fields 70
>> Remove UNIQUE Flag and added PK Fields 75
>> Define optimized Fill-Factors for the indexes 77
>> Index Statistics 83
>> Move indexes to a dedicated File-Group 85
>> Specific Hints for Index Creation in SQL Server 2005/2008 87
> Structure of SIFT Indexes in NAV 88
>> Moving SIFT to a dedicated File-Group 92
> Structure of VSIFT Views in NAV 94
>> Comparing SIFT and VSIFT 96
>> Replacing SIFT/VSIFT using Included Columns 99
> Design of C/AL Code 100
>> Querying SQL Server 101
>> Cursor Handling 104
>> Using SQL Server code 105
>> Linked Objects 108
>> Using Temporary Tables 112
> Miscellaneous Issues 114
>> The used C/SIDE Version 114
>> The DBCC PINTABLE feature 115
>> NAV “Table Optimizer” 116
>> Index Hinting 116
>> Different Query Performance in SQL 2000 and 2005 117
Getting rid of Locks, Blocks and Deadlocks 118
> Locking Mechanisms 118
>> Lock Escalation 118
>> Implicit Locking 119
>> Explicit Locking 119
> Using GUID 122
> Using AutoIncrement 124
> Forcing Row-Locking 125
> Setting Lock Granularity 126
> Block Detection 127
> Deadlocks 129
>> Index Optimization 130
>> Serialization / Semaphore 130
>> Common Locking Order 131
>> Transaction Speed 131
>> Investigating Deadlocks 131
Database Maintenance 135
> Maintenance Plan 135
> SQL Server Agent Jobs 137
> Backup Strategy 140
Parameter Sniffing 142
High Availability & Failover Strategies 146
> Failover Clustering 146
> Database Mirroring 147
> Transaction Log Shipping 148
> Database Snapshots 149
> Replication 150
Miscellaneous 152
> Ghost Cleanup 152
> Named Pipes 152
> Windows Registry & User Profiles 152
> 32bit Applications on 64bit Servers 152
> Dynamic Management Views 153
Additional Resources 154
Index 155
Appendix A – System Checklists 160
Part A - SQL Server Configuration 160
Part B – Database Configuration 165
Part C – Database Maintenance 168
Part D – Performance Monitor 169
Part E – SQL Profiler 171
Appendix B – Version Lists 172
MS Dynamics NAV (Navision) C/SIDE Versions 172
MS Dynamics NAV (Navision) Database Versions 175
MS SQL Server 2000 Versions 177
MS SQL Server 2005 Versions 178
MS SQL Server 2008 Versions 179

As always I appreciate your feedback! Thank you in advance!



  • Thanks for the info. Again, it's not in my hands to fix this ... it all depends on my publisher (and they know about this problem).

    I recommend to send it back to Amazon with a harsh complaint; actually they are advertising the new edition (184 pages etc.).

  • Just wanted to tell you guys that i ordered the book last thursday, Feb. 18th 2010, from and received the 3rd edition! =/

  • Well, yes and no. Of course, most things which are related to NAV 4.0/5.0 and SQL 2005 apply to higher versions as well.

    But with the 4th edition I focused more on SQL 2005/2008 (adding some stuff specifically for 2008) and few things of NAV 2009. As - technically - NAV behaves like 5.0 SP1 there is not much difference; here I just spend some thoughts on the RTC ...

  • >> Do you think the book will be useful with nav 2009 and mssql 2008?

    Well, I should say YES ;c) Hope others agree with me ..


    I mean Do you think the third edition of the book will be useful with nav 2009 and mssql 2008?



  • Thanks for the info! So gladly my publisher is fixing this ...

    Again: I recommend !

    >> Do you think the book will be useful with nav 2009 and mssql 2008?

    Well, I should say YES ;c) Hope others agree with me ...

  • Hi,

    I purchased the book on 28/12/2009


    It arrived today and is the third edition.

    I go to amazon and now is this messages:

    Item Under Review

    While this item is available from other marketplace sellers on this page, it is not currently offered by because customers have told us there may be something wrong with our inventory of the item, the way we are shipping it, or the way it's described here. (Thanks for the tip!)

    We're working to fix the problem as quickly as possible.

    Do you think the book will be useful with

    nav 2009 and mssql 2008?


    Santi Campos.

  • Since today - 23rd October 2009 - the 4th edition of the PFG is available. Please be careful, some resellers might still have the old version on stock. So when ordering make sure you have the 180+ pages (= NEW) instead of the 152+ pages (= OLD) version.

    Best regards,


  • Hi,

    I bought the book lately and I must admit that it is a really good book.

  • Hi Jörg,

    I bought the book a little while back and picked up some very useful info from it.


  • Hi Tino,

    thanks for your praise ;c) Yep, Barcelona was great!

    Good luck for your course - and the exam which will follow(will it?) !

    Best regards,


  • Hi Jörg,

    Great book I must say!

    I'm now taking the course "Implementing a Microsoft SQL Server 2005 database" and your book is the perfect connection between NAV and SQL 2005!

    Btw; It's been nice meeting you in Barcelona.

    greetings Tino Ruijs

  • Hi. Good site.

  • Hi. Good site.

  • Hi. Good news.

  • Got the book, read the book, like the book very much!!

    Thanks, Jorg!