NAV/SQL Performance - My Two Cents

Sort by: Most Recent | Most Viewed | Most Commented

  • #38 of Top 100 Most Influential Dynamics People

    Wow! I came across Erik's BLOG ( http://dynamicsuser.net/blogs/admin/archive/2009/06/12/the-100-most-influential-microsoft-dynamics-people.aspx ) and I was more than surprised when I saw my name on this list. The British Community " Dynamics World " ( http://www.dynamicsworld.co.uk ) voted me on rank 38 among the Top 100 Most Influential Dynamics People together with lots of other people well known from http://dynamicsusers.net or http://www.mibuso.com . I feel really proud and honored...

  • Object Auditing

    Recently in DynamicsUsers.net some "Wiki" was posted showing how to audit Object changes in NAV with SQL site Triggers; see http://dynamicsuser.net/wikis/navdev/auditing-who-changes-an-object-sql-only.aspx This actually describes how to add some fields to the NAV Object table and to save the User Name etc. who has changed an Objects. Also, on German NAV Developer's BLOG recently was shown how to generate a SQL Report about " Schema Changes "; see http://blogs.msdn.com/german_nav_developer...
    Filed under: , , ,

  • BLOB Fields with NAV & SQL

    BLOB = [ B inary] L arge OB ject Well, several tables in MS Dynamics NAV use such "BLOB"-fields to store binary data like pictures. This could be quite convenient for the application, for example for creating Item catalogues displaying such pictures, etc. but from a NAV/SQL performance perspective BLOB could also be a real pain – I'd like to explain why, and what I recommend to do to avoid the problems … The basics A NAV " BLOB " data-type is actually the "...
    Filed under: , , ,

  • DBCC DBREINDEX vs. DBCC INDEXDEFRAG

    The following matters only for those who still run on SQL Server 2000 , as since SQL Server 2005 the DBCC DBREINDEX and DBCC INDEXDEFRAG actually have been replaced by the ALTER INDEX REBUILD/REORGANIZE command (I don't know if the following problem also affects ALTER INDEX). Why executing anyway? Well, Indexes will fragment over time, as data is inserted, modified or deleted anytime when processing NAV (or other) transactions. Basically it's the same like a hard-disk-drive could fragment...

  • Prevent multiple Logins in NAV with SQL Server

    Well, it is a frequently asked question „ How to prevent multiple logins of the same user in NAV? ". Usually the reason for this is to save client-licenses, or other things. When browsing the known web-forums you'll find several approaches to perform this; from executing 3 rd -party applications to kill processes, to sending Alt-F4 keys via " Windows Scripting Host " to manually kill a session in " Session Monitor " (NAV) or " Activity Monitor " (SQL Server...
    Filed under: , ,

  • Blocks & Deadlocks in NAV with SQL Server

    Well, "Performance Optimization" in NAV & SQL is some sort of "Cornucopia of issues" – starting from Hardware Sizing, Platform Configuration, Index Tuning, SIFT Optimization, etc. … and – last but not least – the question: " How to prevent and solve blocking conflicts? " At the " Directions EMEA 2008 " Event in Paris I tried to give some feasible, practical advices to answer this question. The following should give some further...

  • The NAV/SQL Performance Field Guide - 3rd Edition

    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...
    Filed under: , ,

  • Index Statistics

    Well, we all know that " Index Statistics " are crucial for optimized query processing in SQL Server, that we shouldn't use "Auto. Create Stats" or "Auto. Update Stats", that we should use "Jobs" or "Maintenance Plans" to keep them up to date, etc. – but sometimes it is not that easy … Preface " Index Statistics " – short "stats" – contain statistical information about field values, precisely their "density", which...

  • Downgrading NAV

    Well, a current discussion is whether to go for NAV 5.0 Service Pack 1 or not. On the one hand, NAV 5.0 SP1 has lots of improvements regarding SQL performance (Indexed Views, Buffered Inserts, etc.), on the other hand there are other issues and problems (slow FlowFields, lacking SQL 2000 capability, etc.). Once the decision was taken to upgrade – and in the following I ONLY refer to a technical upgrade , means the change of the C/SIDE programs, DLL, etc. – normally there is no way back to a previous...

  • „Indexed Views“ performing slower than „SIFT Tables“ ?

    Today I just ran a brief test to compare the performance of queries on " SIFT Tables " (older NAV versions) with queries on " Indexed Views " (introduced with NAV 5.00 SP1) called VSIFT . Well, in previous BLOGs or forum threads I already stated my concerns about " reading performance " on large tables with VSIFT (no question about " writing performance ", this is definitely improved). As a "View" is just a pre-defined SELECT statement on a table...

  • NAV/SQL Troubleshooter’s Wish-List

    Ho, ho, ho … well, X-Mas is coming soon, time to make a wish-list! In my special case I would like to ask for several things making life with NAV and SQL easier. I'm not asking for "science fiction" features like " non-blocking posting routines " or " different code versions for C/SIDE and SQL "; but small (?) and smart enhancements. Hope someone northern from here (not necessarily Santa C. from North Pole, someone from Vedbaek would be OK ;c) ) takes care about...
    Filed under: , ,

  • MS Dynamics NAV 5.0 Service Pack 1

    Well, at Convergence 2007 in Copenhagen it was announced (Session: NAV Keynotes) that in Q1 2008 the SP1 for NAV 5.00 would be released (dates are subject to change, of course). During the "Meet the Experts" session little more information about the SP's content was published. As we had no chance to really looking into the technical details, this post is more guessing than knowing. Here is what I considered most important: SIFT Tables will be replaced by Indexed Views "Buffered...

  • Included Columns vs. SIFT Tables

    Base on David's "hint, hint ) I made my promise come true and copied this from a current thread: SIFT tables include aggregated information of e.g. "Ledger Entry" tables to display these values in FlowFields. The aggregation is done via SQL Server site triggers which are programmed really poor and performing bad (I skip some details here). Hence, it is necessary to minimize the number of SIFT indexes and bucktes to get the optimal balance between reading and writing performance...

  • Welcome to Jörg's Blog: Dynamics NAV Performance on SQL - My Two Cents

    Here we go! So, this is my very first BLOG! I don't know why .... I have been writing postings in hundreds of forum threads, but never dared to put it a BLOG ... hmmm, feels somewhat "different" than answering or asking questions ... Most of you might know me for years, knowing that I'm focusing on NAV/SQL Performance issues and everything related. This BLOG should be the attempt to "concentrate" my statements - My Two Cents - so I guess I will primarily Copy & Paste...