Navision read performance on a big table is very slow

scott

We have a big table with 2M records. 15 fields. We have keys for 4 searchable fields. When users try to search on this table, it is very slow, and many times they get locked up.

these are the configuration:

1) sql 2005, 12 drives RAID 10, log file is on seprate drive. 4G RAM.
2) 150 users. half of them Citrix users.
3) NAV 5.0. database size is about 50G.
4) We search using any part of the field, Find as you type options.

Any suggestions what we should do to speed up the formance?

Thanks.

Savatage

your filtering on the form - are there any flowfield in these 15 fields that you can remove?.

I mean as long as you find the record quickly you can then drill into or lookup the record in question.

DenSter

What about the 75 users that are not Citrix users?

DenSter

Oh by the way, you need to turn off Find as you type. Especially with 150 people typing away, generating ad hoc queries continuously it will save a LOT of time turning this off.Searching on part of a field generates a LIKE query, which is bad enough for performance. Using Find as you Type generates a LIKE query for every keystroke.

scott

Thanks for the reply. No flowfield. I agree that Find As Type + Part of Field are killing, but the users don't want to lose them...

We are thinking about building a keyword search screen for them...We will index a few searchable fields. Users enter keywords on the screen, They have to click a button to start searching.

DenSter

Have them try it without find as you type for a couple of weeks. Once they see how much faster the system is, they won't want to go back. You can always turn it back on.
<edit> something else that has made a big difference is setting the SourceTablePlacement property to First or Last. </edit>

Dave_Cintron

Have you tried caching the indexes? This would be the best way, if you have the RAM.

gaspode

Have you applied the latest updates (1 and 3) for NAV 5.0, cumulative update 4 for SQL Server 2005 SP2 and then created some query plan guides to add the COMPILE index hint to your slow running queries?

Take a look at the posting on the sustained engineering blog.

http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/01/04/simple-query-to-check-the-recent-performance-history.aspx

There is a link to KB article on that blog post that describes the problem of incorrectly selected query plans under SQL 2005.

I followed this and got some dramatic performance improvements.

hawwa

Maybe you can try to Optimize the table with the Navision Optimize function.

kine

For me 4GB of RAM is TOO low for such a big DB and many users. You need much more RAM to have enough space to not compile everything each time you need something (SQL triggers, plans, small cache etc.).

Another thing is to use SQL profiler to catch the long-running queries and analyze them.

garak

First.
turn off "Find as you type". Every keystroke will send to the sql server as "like" (Field LIKE %E%) command. Thats the hell for the maschine. The users should use F7 or CTRL+F7 (but also Filters like @*otto* is not perfomant :-( )

Second.
4 GB RAM is not enougth, Add more. Test with 16GB or higher
If you have the 32 bit OS, than also activate in boot.ini the /PAE, /3GB Parameters (search the forum or search engines, for this). The /PAE and /3GB Parameter should also activated when a system has only 4GB RAM.
Now the server allocate max. 3GB RAM. But with 16GB you have more. Now you must enable the Option "Lock pages in memory" (with gpedit.msc) and the SQL Server must run under an user account. After activating this flag --> reboot. than activate AWE under SQL Server Settings. But set the max. Server Memory to avail. RAM - 1GB RAM (is for OS). Have you more than 16 GB RAM, than tourn off the /3GB Parameter in boot.ini (because the OS need more than 1GB to handle the RAM with AWE) and set max server memory to avail. Memory - 3 GB for OS.

Third.
Use SQL Profiler and Perfmon to find out, which queries slow down your system.

Foured:
Check your C/AL, Indexstrukture (here are often mistakes) and SIFT

Fivth:
The SQL Server2005 with SP5 has some problems with queries usining Like and comparision operator on the same field. So you need to update your server with update 4 (now availabel is 6, its also good, kumulatives update). Also here search the forum.

sixth.
Test hindexhint for specific tables, keys indexes, search methodes, and test recomile or own SQL "Plan Guides"

seventh:
I'm not sure, but is the same blocking error like in HF6 for 4.03 also be included in HF1 for 5.0 :?: If yes, update the Navision version

eight:
often there are locks on Rows, ranges or tables. For example when people post some invoices or the avg. unit cost will be calculated, or code mistakes. To find out, which user blockes an which table is blocked you can use this tool http://www.mibuso.com/forum/viewtopic.php?t=23204 (change the timer event)

After checking this parts let us know, which queries are to slow (high duration, many reads or writes, high CPU time). Than we tell you the next steps to optimize your system.

Regards

kriki

[Topic moved from Navision forum to SQL Performance forum]

pdj

scott wrote:

1) sql 2005, 12 drives RAID 10, log file is on seprate drive. 4G RAM.
2) 150 users. half of them Citrix users.
3) NAV 5.0. database size is about 50G.

garak wrote:

4 GB RAM is not enougth, Add more. Test with 16GB or higher

kine wrote:

For me 4GB of RAM is TOO low for such a big DB and many users.

The recommended hardware specification according to Microsoft is pretty clear:
1) No. of CPUs/Cores = 4
2) RAM= 8GB
3) Database= 8-10 disks in raid 10
4) Transactionlog= 2 disks in raid 1
Source: https://mbs.microsoft.com/partnersource ... page=false

Are these requirements way too optimistic?

kriki

This should be enough.
2 remarks:
-the tempDB is best put on its own RAID1-disk. Specially if you have SQL2005.
-the transactionlog, I would put on a RAID10 with 4 disks, but generally a RAID1 should do the job.

pdj

What is most important for NAV on SQL2005x64: 4 disks for transactionlog or 2 dedicated disks for tempdb? (I guess you won't recommend settling with 6 disks for the database to get both.)

I have just got permission to order a sql server after the Microsoft requirements. Now I apparently need to configure them a bit differently than I expected.

Does anyone have any combined best-practice for usage of raid and number of transactionlog and database parts etc. for NAV SQL servers? I keep reading fragments in different threads, but have a hard time getting an overview.
Some say one large DB part put on one big raid10 partition. Other say one db part per cpu core each put on a raid1 or raid10 partition. Some even say it depends on the amount of memory.

Any info or links will be much appreciated.

TIA.

kriki

If your server has internal 2 drives in RAID1 for the system, I would put the tempDB there to have 4 disks for the transactionlog.
I noticed that 4 disks for the transactionlog is useful in case you use full recovery model with transactionlog backups. If you don't use that and make 1 full backup a day and some differential backups during the off-hours (lunchpause for example), you can go for 2 disks for the transactionlog.

I haven't found a document where the SQL-best practices for a Navision-DB is combined. I hope that Waldo will provide one next week during Directions 2008.
My ideas are that 1 big DB on a RAID10 is good enough. It is possible to have some better performance if you have multiple databasefiles on the same RAID10, but I never found some figures about them.
I also read that it is best to have 1 tempdb per cpu-core, but I think that is more important on a real SQL-db, because Navision doesn't really use the tempdatabase.