ItemAvailability by Location is slow

Vineeth.R

Dear All,

One of our customer is facing slowness when they run this item availability by location form. There database is huge 400gb, When they restart their server and try this form its fast, but slowly after few days this becomes slow again. Please advise what may be the reason?they are using Version 4.0 SP3 SQL 2005 version.

thanks and Regards
Vineeth.R

kriki

There can be a lot of reasons, but I would first check if there is a rebuild index running at regular intervals (Your description makes me think they do a rebuild index and then do a server restart).

If this is the case, you can also check with the client monitor what happens when the form is run and is slow.


BTW: is this the only performance problem they have or do they have others?

Vineeth.R

Dear Kriki,

Analysis View and Currency Revaluation is also slow, They dont do and rebuild indexing and thr is no customied coding in itemavailability by location part.

Please advise why after restarting it becomes faster(normal) again and after sometimes slow.

thanks and Regards
Vineeth.R

kriki

Very weird that it is fast just after a restart. I would expect it to be slow.

Some other questions:
What is the diskconfiguration? DAS or SAN?
Have they 64-bit or 32-bit windows server and sql server?
How much memory has the server?
How much is dedicated to sql server?
Is there something else the server does? (it should be dedicated to sql server)
Build of NAV?

And to start: they should do a regulare indexrebuild. Best is each night. But if it is not possible, once a week is better then nothing.

Vineeth.R

Dear Kirki,

1) The disk configuration is SAN solution with 14 ultra 320 gb harddisks connected.
2) Microsoft Windows Server 2003 64 bit.
3) 64 gb memory.
4) nothing is dedicated to any application, windows decides.
5) server does not do anything else
6) housekeping tool is running every night which does the indexing and all.
7) Built is 23305.
Please advise how can we fix this slowness.

kapamarou

I recently faced an issue with this build. I think you need to apply a hotfix.

Belias

kapamarou wrote:

I recently faced an issue with this build. I think you need to apply a hotfix.

yes, i remember that there is a version where the reports gets slower and slower...
i just found a mail of a MS guy who adressed me to KB977604 but i don't know if it's the latest. Anyway, check if the problem is similar to yours, and eventually test the hotfix.
Let us know if you solve the problem :thumbsup:

Vineeth.R

Dear All,

There is a small change, some of the clients PC is using the 23305 build, most of them are using 27857 and the server is using 29961 build no.

Please advise.

kriki

Vineeth.R wrote:

Dear Kirki,

1) The disk configuration is SAN solution with 14 ultra 320 gb harddisks connected.
2) Microsoft Windows Server 2003 64 bit.
3) 64 gb memory.
4) nothing is dedicated to any application, windows decides.
5) server does not do anything else
6) housekeping tool is running every night which does the indexing and all.
7) Built is 23305.
Please advise how can we fix this slowness.

1) is the SAN used by other servers/applications? And how much cache does it have? And for sql server server more write cache than read cache (SQL server has its own read cache that is a lot better then the SAN cache AND something that is NOT in the SQL cache, will 99.999999% of the time neither be in the SAN cache.
2) and SQL server is 64 bit?
6) what housekeeping tool and what exactly does it do?

Get everything on the same build (Check here to get some help : http://dynamicsuser.net/blogs/waldo/archive/2009/09/17/platform-updates-overview-3-70-b-nav2009-sp1.aspx)

Vineeth.R

Dear Kirki,

1) SAN is not used for any other purpose.
2) Please advise how to check how much cache is assigned to SQL server READ/WRITE operations.
3) SQL is 64 bit.
4) internal housekeeping tools is run for rebuild of indexing etc.

Please advise how can we fix this slowness.

Thanks and Regards
Vineeth

kriki

2) Go in properties of the sql server => tab memory => it will show minimum and maximum memory.
Check also with taskmonitor if SQL is using the memory and if Windows is not using MORE memory then fysically available.

You could also try with client monitor (or SQL profiler) to check which commands NAV sends to SQL. Maybe it needs some extra index or SIFT-level.