slow navision on sql

mp

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

fb

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...!

kine

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...

joepkoek

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.

mp

Friends
Thanks for the posts..Will post back later reg my findings.

MP

davmac1

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.

kriki

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".

mp

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_Little

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.

kriki

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.

kine

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...

Joe_Little

I sent the to the admin for the download section. Do what you like with it. Hopefully it will be useful. Joe.

recall

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

fb

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.

mrQQ

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?)

orossi

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.

ovicash

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

johanna

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

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


Best Regards,

Johanna

kriki

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.

johanna

Thank Kriki for your reply..

Which versions are included in the older versions?

Which versions are included in the newer versions?

kriki

older : <= 5.00
newer : 50SP1+

rdebath

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.

ALopez27

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?

bbrown

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.

kriki

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.

stryk

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

RedFoxUA

kriki wrote:

older : <= 5.00
newer : 50SP1+

Good day.
How about your advice & R2 release? I know what we can Cursors for Key now.

stryk

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+

David_Singleton

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.

kriki

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 )

RedFoxUA

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