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 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 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 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-6184 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 2Document Version 3Legal Notice 4Table of Content 5About the Author 8From the Author 9Preface 10Performance Toolbox 11What is “Performance”? 12> Performance Troubleshooting 13Finding “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 27Avoiding 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” 63Fixing 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 117Getting 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 131Database Maintenance 135> Maintenance Plan 135> SQL Server Agent Jobs 137> Backup Strategy 140Parameter Sniffing 142High Availability & Failover Strategies 146> Failover Clustering 146> Database Mirroring 147> Transaction Log Shipping 148> Database Snapshots 149> Replication 150Miscellaneous 152> Ghost Cleanup 152> Named Pipes 152> Windows Registry & User Profiles 152> 32bit Applications on 64bit Servers 152> Dynamic Management Views 153Additional Resources 154Index 155Appendix A – System Checklists 160Part A - SQL Server Configuration 160Part B – Database Configuration 165Part C – Database Maintenance 168Part D – Performance Monitor 169Part E – SQL Profiler 171Appendix B – Version Lists 172MS Dynamics NAV (Navision) C/SIDE Versions 172MS Dynamics NAV (Navision) Database Versions 175MS SQL Server 2000 Versions 177MS SQL Server 2005 Versions 178MS SQL Server 2008 Versions 179
As always I appreciate your feedback! Thank you in advance!
Hmm is there some way I can get one delivered here?
I'm working on this ... hang on ...
How would this be different from Hynek's SQL Perform class?
wery good, respect!
Got the book, read the book, like the book very much!!
Hi. Good news.
Hi. Good site.
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
thanks for your praise ;c) Yep, Barcelona was great!
Good luck for your course - and the exam which will follow(will it?) !
I bought the book a little while back and picked up some very useful info from it.
I bought the book lately and I must admit that it is a really good book.
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.
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 Amazon.com 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?
Thanks for the info! So gladly my publisher is fixing this ...
Again: I recommend MSDynamicsBooks.com !
>> 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 ...