slow navision on sql

mpmp Member Posts: 20
Hello Gurus

We moved to sql for our navision 5 months ago. I generally hear that Navision-SQL is little bit slower than the regular native database. Users are reporting that the system is getting day by day slower.

I have a good amount of knowledge in moving around in SQL enterprise manager. Could anyone guide me through some basic sql database maintenance steps or share some knowledge why navision is slow.

MP
«1

Comments

  • fbfb Member Posts: 246
    Hmm, here's a few tips to get you started:

    1) Index maintenance -- weekly for heavily used tables -- some run it daily... Arguments break out on the forums (? fora ?) about whether using the NaviSql client/db info/tables/optimize is better, or just set up a db maint plan to run automatically... just do something (actually, you really need to do both -- schedule maint. plan frequently; then manually optimize after every full restore, and periodically, say monthly)...

    2) Split the 'secondary' db file into multiple files, spread across spindles if possible... this takes a full backup/restore/optimize cycle to gain the benefits, but is is worth it... Hard to say how many files -- depends on your hd config...

    3) Don't rely on auto-grow -- it always happens at a lousy time (when the db is the most busy) -- and it is non-optimal: it only grows one file of many, causing that file to become 'hot' -- all new records go into one file... So, watch your file free space (at least weekly), and manually grow the files before they fill up.

    4) If you restart the SQL Server service periodically, be sure to define the initial size of the Transaction Log correctly (read: make it pretty big). A tx log auto-grow is sure to piss off a whole bunch of people because things are guaranteed to be locked up for just about everyone when/if this happens...

    And that's just to get you started...!
  • kinekine Member Posts: 12,562
    And look, if you have enough memory and you are using apropriate Server edition and OS switches if you want to use more than 2GB RAM...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • joepkoekjoepkoek Member Posts: 2
    We had the expierience of the SQL Server getting slower, changing the recovery model to simple and scheduling daily backups with the enterprise manager solved it for us.
  • mpmp Member Posts: 20
    Friends
    Thanks for the posts..Will post back later reg my findings.

    MP
  • davmac1davmac1 Member Posts: 1,283
    Do you have the transaction log file on a separate physical disk?

    Changing your recovery mode to simple means you cannot recover to a point in time, you have to go back to previous backup.
  • krikikriki Member, Moderator Posts: 9,094
    With SQL, you don't need to keep all the indexes Navision has created. You cannot disable them because Navision uses them, but you can say Navision not to maintain them in SQL (MaintainSQLIndex-property of an index in Navision). The same story for SIFT. For SIFT, there is even more: for every field of the key, Navision maintains an extra record in a new table, this because SQL doesn't have the SIFT-technology. So by not maintaining the SIFT, you gain a lot of writing-speed and in general you loose little reading speed.

    So in a lot of tables, you can remove (=MaintainSQLIndex:No) some indexes that SQL will not use.

    And even if you put SETCURRENTKEY in Navision, SQL translates it to ORDER BY. The index that is used wil be decided by SQL-server when the command is run.

    Some guidelines:
    -In general SQL uses the smallest indexes first.
    -If SQL has to scan 2000-3000 records to select some records or do a SUM(...), you don't need an extra index for that. This goes very fast.
    -Don't maintain all levels of the SIFT, just the ones you really need.

    An example on Table 21 Navision 3.70B W1: (I didn't take the disabled keys)

    -Entry No. : primary key, you need this one.
    -Customer No.,Posting Date,Currency Code Sales (LCY),Profit (LCY),Inv. Discount (LCY) : if you have a lot of customers with a few 1000 of entries, don't maintain the index, nor the SIFT. BUT create a new key with just 1 field in it:"Customer No.".
    -Document No.,Document Type,Customer No. : this key is to big. Or SQL uses an index on "Document No." or "Customer No.". Don't maintain this Index. BUT create an index on "Document No.".
    -Document Type,External Document No.,Customer No. : this index is stupid, it should NOT start with an option, but it should start with "External Document No.". So don't maintain it, BUT create a new index "External Document No.".
    -Customer No.,Open,Positive,Due Date,Currency Code : we already have an index "Customer No.", so no need to maintain it.
    -Open,Due Date:this index is better maintained. In general indexes starting with a boolean are not good, but this one is because this index is used to get the open entries, and only a small part of the entries will be open.
    -Document Type,Customer No.,Posting Date,Currency Code Sales (LCY),Profit (LCY),Inv. Discount (LCY): "Document Type" is an option, so it is a bad idea to start an index with it. Don't maintain the index nor the SIFT, we already have an index with "Customer No."
    -Salesperson Code,Posting Date: Best maintain this index.
    -Closed by Entry No.: Best maintain this index.
    -Transaction No.: Best maintain this index.
    -Customer No.,Applies-to ID,Open,Positive,Due Date: we have a smaller index with "Customer No.", so don't maintain this one.

    New Indexes:
    -"Customer No."
    -"Document No.". This index is always very good, because with a certain document no, SQL has only to scan 1 or just a few records!!!!
    -"External Document No."

    Conclusion:
    -We don't maintain 6 indexes.
    -We have 3 new indexes, but very small ones, so faster to maintain.
    => we gain a lot of writing speed but losing very little reading speed.

    Afterthought:
    If we have few customers, but lots of entries per customer, we might create 2 indexes, 1 with "Customer No."+"Posting Date" and 1 with "Customer No."+"Open".
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • mpmp Member Posts: 20
    My recovery model is simple.
    My transaction file is in a separate disk.

    kirki - you have posted lot of information. Thanks. I am not a Navision person. I am the IT guy here and support is handled thru the Navision Support Center folks. I will pass your post to them to see if they can provide some help.

    current status is I have asked my Support center guys to look at it. They looked at the Navision Tables and said everything is normal. They are planning to turn on the performance monitor and watch the activity and load. I am not sure whether this is going to help or not.
  • Joe_LittleJoe_Little Member Posts: 45
    I think kirki is on the right page here. Especially with the sift indexes. These indexes are maintained in separate tables in SQL and with high transaction volume can create some major processing when the table indexes are maintained.

    The problem is that these sift tables are maintained when records are inserted, deleted, modified, etc. Even though the owning table is under a record level lock, once the sift maintenance starts, you can get into table level locking for the key table. This can really hurt performance.

    Setting up the SIFT indexes appropriately is very important in a high volume environment. Since SQL is capable of running without them (flow field calculations will be slower but will still calculate).

    These can be a little tricky to figure out, but here's a simple way to quickly identify if this has anything to do with your issues.

    Make a copy of the database and disable all sift indexes on the tables involved. Most commonly this will be in the sales order processing and posting areas.

    Try turning off sift maintentance for all keys in the following tables:

    Item Ledger Entry
    Value Entry
    Reservation Etnry
    G/L Entry
    Vendor Ledger Entry
    Detailed Vendor Ledger Entry
    Customer Ledger Entry
    Detailed Customer Ledger Entry
    Analysis View Entry
    Sales Line
    Sales Invoice Line

    Things will run significantly faster or not, but it is quick to do and if it changes things, you have somewhere to go.

    It looks like I can't add attachments here or I'd attach a little utility that you can use as well.

    If you are interested, send me your email address and I'll forward you a 3.7 version of this object.

    It contains a form and table called Performance Buffer. Run whatever activity that you are interested in and turn on the client monitor from within Navision (You may need help from your solution center to get a more powerful license if yours does not support this). Then run the performance buffer form. Select Load Buffer from the functions menu.

    This will use the temporary performance buffer table to summarize the client monitor information into source object, function, table data and will give you an elapsed time for what is going on broken down by the object, function and accessed table. It will give you an idea of where the system is spending its time. You can do use this before and after changing the sift fields to get objective results.

    Good Luck! Joe.
  • krikikriki Member, Moderator Posts: 9,094
    It looks like I can't add attachments here or I'd attach a little utility that you can use as well.
    Can't you send it to the administrators, so they can put it in the download section?
    I am also interested in that utility and I think also a lot of others.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • kinekine Member Posts: 12,562
    If course, there are many objects for performance hints. For example "Key Information Tool" - where you can see cost of insert (how many modifications will be needed for doing one insert into table) or "Index Defrag Tool" which will show you info about selectivity, defragmentation etc... all is on MBS Tools cd in folder Implementation\SQL Server Resource Kit...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Joe_LittleJoe_Little Member Posts: 45
    I sent the to the admin for the download section. Do what you like with it. Hopefully it will be useful. Joe.
  • recallrecall Member Posts: 36
    I wonder why he should put the transaction-file on a seperate disk ?
    And why should he split the "second" db-file ?
    Especially the second one is not quite clear to me ?!
    If I had an array of disks (RAID 10), do you mean to split up the files on one disk or should one split up the array too ? And why ?

    Thanks
  • fbfb Member Posts: 246
    I've taken the ideas from the "Optimizing Database Performance" section in SQL Server Books Online:

    RE: Trx log:
    Optimizing Transaction Log Performance
    General recommendations for creating transaction log files include:

    Create the transaction log on a physically separate disk or RAID (redundant array of independent disks) device. The transaction log file is written serially; therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next write operation.

    Set the original size of the transaction log file to a reasonable size to prevent the file from automatically expanding as more transaction log space is needed. As the transaction log expands, a new virtual log file is created, and write operations to the transaction log wait while the transaction log is expanded. If the transaction log expands too frequently, performance can be affected.

    RE: Splitting db files:
    If the computer has multiple processors, Microsoft® SQL Server™ 2000 can perform parallel scans of the data. Multiple parallel scans can be executed for a single table if the filegroup of the table contains multiple files. Whenever a table is accessed sequentially, a separate thread is created to read each file in parallel. For example, a full scan of a table created on a filegroup comprising of four files will use four separate threads to read the data in parallel. Therefore, creating more files per filegroup can help increase performance because a separate thread is used to scan each file in parallel. Similarly, when a query joins tables on different filegroups, each table can be read in parallel, thereby improving query performance.

    It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access using multiple files. To spread data evenly across all disks, first set up hardware-based disk striping, and then use filegroups to spread data across multiple hardware stripe sets if needed.

    Eventually, there is a saturation point when there are too many files and therefore too many parallel threads causing bottlenecks in the disk I/O subsystem. These bottlenecks can be identified by using Windows NT® Performance Monitor to monitor the PhysicalDisk object and Disk Queue Length counter. If the Disk Queue Length counter is greater than three, consider reducing the number of files.
  • mrQQmrQQ Member Posts: 239
    ok, so from what i gather, disks should be used as:

    * disk/array for system
    * disk/array for data
    * disk/array for tx logs

    what aboud temp db and swap file - where to put those?

    and does anyone have a good recommendation table for cpu #/memory depending on users (say, 50, 100 users?)
  • orossiorossi Member Posts: 12
    We've moved to SQL from Navision database and the performance improvement was huge. We've a 25Gb Navision Database, split in 5 files of 5Gb each. The transaction log has 25Gb of space used. All in one RAID 1 disk. We have an HP Proliant ML350 G3 with 2GB of RAM and 1 Xeon 2.8Ghz processor. Commonly there are almost 40-45 concurrent Navision users.
    Osvaldo Rossi
  • ovicashovicash Member Posts: 141
    orossi wrote:
    We've moved to SQL from Navision database and the performance improvement was huge. We've a 25Gb Navision Database, split in 5 files of 5Gb each. The transaction log has 25Gb of space used. All in one RAID 1 disk. We have an HP Proliant ML350 G3 with 2GB of RAM and 1 Xeon 2.8Ghz processor. Commonly there are almost 40-45 concurrent Navision users.

    Have you made some C/AL code optimization? Or it was just migration from one platform to another?

    thx
    ovidiu

    Best Regards
  • johannajohanna Member Posts: 369
    kriki wrote:
    With SQL, you don't need to keep all the indexes Navision has created. You cannot disable them because Navision uses them, but you can say Navision not to maintain them in SQL (MaintainSQLIndex-property of an index in Navision). The same story for SIFT. For SIFT, there is even more: for every field of the key, Navision maintains an extra record in a new table, this because SQL doesn't have the SIFT-technology. So by not maintaining the SIFT, you gain a lot of writing-speed and in general you loose little reading speed.

    So in a lot of tables, you can remove (=MaintainSQLIndex:No) some indexes that SQL will not use.

    And even if you put SETCURRENTKEY in Navision, SQL translates it to ORDER BY. The index that is used wil be decided by SQL-server when the command is run.

    Some guidelines:
    -In general SQL uses the smallest indexes first.
    -If SQL has to scan 2000-3000 records to select some records or do a SUM(...), you don't need an extra index for that. This goes very fast.
    -Don't maintain all levels of the SIFT, just the ones you really need.

    An example on Table 21 Navision 3.70B W1: (I didn't take the disabled keys)

    -Entry No. : primary key, you need this one.
    -Customer No.,Posting Date,Currency Code Sales (LCY),Profit (LCY),Inv. Discount (LCY) : if you have a lot of customers with a few 1000 of entries, don't maintain the index, nor the SIFT. BUT create a new key with just 1 field in it:"Customer No.".
    -Document No.,Document Type,Customer No. : this key is to big. Or SQL uses an index on "Document No." or "Customer No.". Don't maintain this Index. BUT create an index on "Document No.".
    -Document Type,External Document No.,Customer No. : this index is stupid, it should NOT start with an option, but it should start with "External Document No.". So don't maintain it, BUT create a new index "External Document No.".
    -Customer No.,Open,Positive,Due Date,Currency Code : we already have an index "Customer No.", so no need to maintain it.
    -Open,Due Date:this index is better maintained. In general indexes starting with a boolean are not good, but this one is because this index is used to get the open entries, and only a small part of the entries will be open.
    -Document Type,Customer No.,Posting Date,Currency Code Sales (LCY),Profit (LCY),Inv. Discount (LCY): "Document Type" is an option, so it is a bad idea to start an index with it. Don't maintain the index nor the SIFT, we already have an index with "Customer No."
    -Salesperson Code,Posting Date: Best maintain this index.
    -Closed by Entry No.: Best maintain this index.
    -Transaction No.: Best maintain this index.
    -Customer No.,Applies-to ID,Open,Positive,Due Date: we have a smaller index with "Customer No.", so don't maintain this one.

    New Indexes:
    -"Customer No."
    -"Document No.". This index is always very good, because with a certain document no, SQL has only to scan 1 or just a few records!!!!
    -"External Document No."

    Conclusion:
    -We don't maintain 6 indexes.
    -We have 3 new indexes, but very small ones, so faster to maintain.
    => we gain a lot of writing speed but losing very little reading speed.

    Afterthought:
    If we have few customers, but lots of entries per customer, we might create 2 indexes, 1 with "Customer No."+"Posting Date" and 1 with "Customer No."+"Open".


    Hi, Kriki :D

    I'm still a beginner in this Navision world :P
    Your post is very useful information for me..

    I want to ask you. In your post, if you said not to maintain the index, is it mean turn off the MaintainSQLIndex? And if you said not to maintain the SIFT, is it mean turn off the MaintainSIFTIndex?

    Thank you :D


    Best Regards,

    Johanna
    Best regards,

    Johanna
  • krikikriki Member, Moderator Posts: 9,094
    Correct.

    But an extra note is needed now:
    In older versions it wasn't a problem not maintaining those 'bad' indexes or changing the SQLINdex-property and getting better performance.
    With the newer versions, NAV is using the SQL-cursors a little different and doing not maintaining can now trigger a clustered index scan on the table (worse than this is not possible).
    So be careful about doing this : each time you do this, you should check if it helps performance.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • johannajohanna Member Posts: 369
    Thank Kriki for your reply.. :wink:

    Which versions are included in the older versions?

    Which versions are included in the newer versions?
    Best regards,

    Johanna
  • krikikriki Member, Moderator Posts: 9,094
    older : <= 5.00
    newer : 50SP1+
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • rdebathrdebath Member Posts: 383
    kriki wrote:
    With the newer versions, NAV is using the SQL-cursors a little different and doing not maintaining can now trigger a clustered index scan on the table (worse than this is not possible).
    Sigh, so how's the campaign for a "don't be ****ing stupid" switch in the next SQL version going? :roll:

    You know like the old versions where the query optimisers were actually predictable. :(
  • ALopez27ALopez27 Member Posts: 42
    Hello Kriki, can you elaborate a little bit on this? What are SQL-cursors ? Were on NAV2009 Classic SQL2008, is there an easy way to 'check' if turning the MaintainSQLIndex off or changing the SQLIndex property helps or makes things worse?
  • bbrownbbrown Member Posts: 3,268
    Recovery model has nothing to do with performamce. A different recovery model does not change the way the T-log is used during write transactions. And that's the only time it is used.
    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,094
    ALopez27 wrote:
    Hello Kriki, can you elaborate a little bit on this? What are SQL-cursors ? Were on NAV2009 Classic SQL2008, is there an easy way to 'check' if turning the MaintainSQLIndex off or changing the SQLIndex property helps or makes things worse?
    Search the forum for it, you will find a lot of info on it. Check also the download-section.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • strykstryk Member Posts: 645
    ALopez27 wrote:
    Hello Kriki, can you elaborate a little bit on this? What are SQL-cursors ? Were on NAV2009 Classic SQL2008, is there an easy way to 'check' if turning the MaintainSQLIndex off or changing the SQLIndex property helps or makes things worse?
    Maybe you want to check out this: http://dynamicsuser.net/blogs/stryk/default.aspx :wink:
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • RedFoxUARedFoxUA Member Posts: 21
    kriki wrote:
    older : <= 5.00
    newer : 50SP1+
    Good day.
    How about your advice & R2 release? I know what we can Cursors for Key now.
    Regards,
    Yuriy Golyachuk, NAV Solutions Specialist & Developer
    Skype: RedFoxUA
  • strykstryk Member Posts: 645
    RedFoxUA wrote:
    kriki wrote:
    older : <= 5.00
    newer : 50SP1+
    Good day.
    How about your advice & R2 release? I know what we can Cursors for Key now.

    Should be:

    older : <= 5.00
    newer : >= 50SP1+
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • David_SingletonDavid_Singleton Member Posts: 5,479
    stryk wrote:
    RedFoxUA wrote:
    kriki wrote:
    older : <= 5.00
    newer : 50SP1+
    Good day.
    How about your advice & R2 release? I know what we can Cursors for Key now.

    Should be:

    older : <= 5.00
    newer : >= 50SP1+

    Except that it isn't version dependent, its Hotfix dependent, so you need to look at Waldo's blog. You can get versions of 4 and 5 with Dynamic Cursors. 5.00 also have VSIFT now.
    David Singleton
  • krikikriki Member, Moderator Posts: 9,094
    RedFoxUA wrote:
    I know what we can Cursors for Key now.
    Cursors and keys are 2 completely different things.
    Keys: keys defined in NAV. They are needed for the SETCURRENTKEY in NAV.
    Index: NAV Keys generally create an Index in SQL (you can avoid it with key-property MaintainSQLIndex=No). SQL doesn't care about the key NAV uses (not in a direct way that is). When a query arrives in SQL, SQL analyses it and decides which index is the best to get the data.

    Cursors: NAV works on a record-per-record basis. But SQL hates that and wants to work set-based. To make SQL behave as a native-DB it uses cursors to fetch the records a record at the time (not completely true, but +- that is the idea). There are a lot of different cursors that can be used. The cursor-type used with older versions looked more at the WHERE-clause to decide the index to be used (and to make it even more complicated: it also changes with the build). The cursor-type used with newer versions look more at the ORDER BY clause (= the SETCURRENTKEY) to decide the index to be used. This last means that we cannot change the index-fields of a NAV-index like we did in the old days. Doing that now has often the effect that the clustered index (=the whole table) is scanned for the records needed with very bad performance as a result.

    2009 R2 is a good version, especially with a good recent build and combined with SQL2008R2 64bit.
    Out-of-the-box, it has good performance. With the older versions, performance-tuning was more necessary.



    PS Joerg: "50SP1+" is the same as ">= 50SP1" (ok, it is not a NAV-filter :lol: )
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • RedFoxUARedFoxUA Member Posts: 21
    RedFoxUA wrote:
    kriki wrote:
    older : <= 5.00
    newer : 50SP1+
    Good day.
    How about your advice & R2 release? I know what we can Cursors for Key now.
    Sorry, my fault:
    - 5.0 SP1 R2 or 2009 SP1 R2
    - SQL Views instead of Cursors
    Regards,
    Yuriy Golyachuk, NAV Solutions Specialist & Developer
    Skype: RedFoxUA
Sign In or Register to comment.