NAV/SQL Performance - My Two Cents

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

The NAV/SQL Performance Field Guide - 4th Edition

News Update  9th June 2010:
Recommended by Microsoft, see http://blogs.msdn.com/b/nav/archive/2010/06/08/books-four-options-for-learning-more-about-how-to-create-roletailored-solutions-with-microsoft-dynamics-nav.aspx

>> 09.12.2009 - Important Notice

In the past days I received several complaints that Amazon.com (and probably Amazon.co.uk, 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!

Jörg