Performance issue with 5.0 sp1

fabio78

Hi,

I've upgraded my Navision installation from 4.0 sp3 to 5.0 without performance issue, then from 5.0 to 5.0 sp1 with very very performance issue...

I've read that 5.0 sp1 uses Index Hint by default, but by trace from my Database I don't see any hint.

Can this issue caused by new sql Pre-Processing way to execute queries?

From sys.processes I've also seen that there are a lot of processes waiting for pageiolatch_sh, but with 5.0 I don't have waiting for resource...

davmac1

Which version of SQL Server are you using?
32 bit or 64 bit?
How much RAM?

kine

Because the "pageiolatch_sh", I suppose that the discs are busy with reading data for some scans. Try to use SQL Profiler and look for queries with many reads...

pdj

http://dynamicsuser.net/blogs/navsustat ... -2000.aspx

Marije_Brummel

There are known issues with SP1 for slow flowfields. This can cause many reads.

If your server is already on the edge of performance this can cause it to be over the edge.

Pageio means that SQL server has to few HW resources. What is your readqueue on the datadisk?

My advise would be to go back to 5.0 via NAV backup/restore.

If you are on SQL2005, it is even better to go to the last 4.0 SP3 build.

stryk

Hi,

well, there could be plenty of reasons for encountering problems with "pageiolatch_sh". How's your disk-subsystem configured? YOu should monitor the disk counters like "avg. read queue length", "avg. write queue length", "% disk time", "avg. sec/transfer" etc. to determine if there are any latencies; in parallel you should monitor several memory counters like "buffer cache hit ratio", "page life expectancy", "free pages", "memory granst pending" etc. to determine if RAM is the problem. (Lacking RAM wil increase pressure on the disks).

As suggested by kine you should use the Profiler to find queries "consuming" lot of Reads (e.g. > 1000). Such queries could "kill" the cache and further screw up the disk perfromance ...

If tables are heavily fragmented, this could also lead to pageiolatch_sh problems. You should run a periodic Index Rebuild (ALTER INDEX REBUILD) at least on tables with a "logical fragmentation" > 30% ... when re-indexing take care about NOT applying wrong fill-factors; if FF can't be set precisely based on table growth, a value of 90 or 95 percent is OK for NAV (caution: in "Maintenance Plan" you define the amount of "Free Space". Hence, to apply a FF of 90% you have to define 10% Free Space!)

Best regards,
Jörg