Pageiolatch

frgusto

One customer have problems with occasional several pageiolatch_ex and pageiolatch_sh causing blocks in the database.
I can see in activity monitor several wait type pageiolatch.

SQL-server 2005 (9.0.3200)
Navision klient 4.03 no hotfix
Users is around 140 logged in.
DB contains 27Gb data.

Where do I begin?

Slawek_Guzek

Hi,

Maybe here: http://support.microsoft.com/kb/822101 ?

1. Buy more memory and the problem shall disappear (this is the simplest and fastest approach when the database is relatively small - 27GB can easily fit in SQL server memory)

2. If you want to be more 'right' and do some engineering start form Client Monitor, identify query which is causing problems (with long run time), check which table is used, check exection plan if query uses right index, fine tune the index, or create new.

3. Hire someone experienced with NAV/SQL tuning and learn from him on real problems (the most efficient approach ? - you spent money on consultancy but save on trainings )

Regards,
Slawek

frgusto

Thankyou Slavek.
1. RAM is already 8Gb. I dont think buying more memory would solve this...?
2. Client monitor, of course. Problem is I dont know what is causing the problems, so client monitor cant be used yet. All I know is that many users are complaining and in activity monitor I can see pageiolatch.
3. Sure.

What is the nature behind pageiolatch in a Navision database?
What can be causing this?

Thankyou
Fredrik

kine

PageIOLatch means that SQL is waiting for read/write data from/to disc. It means that there are - too much writes/reads and the disc is overloaded... (e.g. by some Index scan etc.)

Marije_Brummel

Try to analyse why there are so many I/O's on the system.

You can use the SQL Performance monitor from the tools CD for this.

Once you have found the user/proces you can use client monitor for more details.

frgusto

Thankyou. I'll post back.

frgusto

I've run standard report Index Physical Statistics.
There are a lot of indexes where operation recommended is either rebuild or reorganize.
This is strange because there is a maintenance plan for rebuild index, skeduled to run every night, and with no errors in job history.

Rebuild index task
- Object: Tables and Views.
- Reorganize pages with the default amount of free space.
- Sort results in tempdb.

But this maintenance plan maybe is not good enough?

Slawek_Guzek

Rebuilding indexes will give you some performance gain but I doubt you get rid of pageiolatch only by rebuilding them.

The problem is (as mentioned in Microsoft KB article and by Kine) too much data transfer to and from disk.

In order to decrease physical IO load you need to increase your cache hit ratio (by increasing memory to 16 GB for example) or by decreasing number of required IO. Rebuilding indexes is one of safe method of decreasing IO, but not necessarily most efficient. I meant it is always desired and good to have index in a good condition, and it should be daily or weekly plan of all DB admins to do some indexes housekeeping, but you may want to redesign existing index or introduce new one to get real IO performance boost.

All the above (plus a few more) are solutions - not a starting point. Starting point is to find the longest query - or by using Client Monitor, or SQL Profiler.

Regards,
Slawek

Slawek_Guzek

Slawek Guzek wrote:

The problem is (as mentioned in Microsoft KB article and by Kine) too much data transfer to and from disk

Or - better said - the problem itself is not in amount of the data but in fact that SQL engine has to wait for it.

You may get rid of that by speeding up your IO path (buy more faster disks for example), or by decreasing physical IO load.

Regards,
Slawek

frgusto

I have found that the maintenance plan that rebuild index did not complete for all indexes. But did report success anyway. It seems like when the plan consists of several steps an error during rebuild index will not make the plan to report error. The plan first run check consistency and then rebuild index followed by update statistics.

Now I have made separate plans for check consistency and rebuild index. Update statistics was removed as rebuild index will update statistics.
Any error during rebuild index is now reported as an error for the maintenace plan.

Free space per page was changed to 10% instead of default amount of free space.

I have done som trace for long running queries to be compared with same trace after all indexes has been rebuilt.
As soon as all indexes are rebuilt I will continue and try to find and optimize separate queries.

I understand there are updates for nav 4.03 that improves performance of flowfields. Is this right? Are they recommended to apply?

Regards Fredrik

kriki

frgusto wrote:

I understand there are updates for nav 4.03 that improves performance of flowfields. Is this right? Are they recommended to apply?

They don't improve performance but make sure they are correct.
They are MORE than recommended to apply! Best take the latest update (see http://dynamicsuser.net/blogs/waldo/archive/2008/05/09/platform-updates-overview-3-70-b-5-0-sp1-updated-again.aspx)

davmac1

One problem I have seen affect performance is when a terminal server is supporting too many users so that its CPU is running 100% and it is swapping users - not enough CPUs and not enough RAM.
This can cause processes to take too long.
How are your 140 users connecting? Are they all on the local network or are a number of them coming in via Terminal Services (Microsoft or Citrix)?

frgusto

Ok thankyou Kriki. Then we'll apply the update but not as solution for bad performance and pageiolatch then.

All users connect from terminal servers. We have checked that they are appropriately stuffed with RAM and CPU.
But waittype pageiolatch is SQL waiting for read/write data from/to disc. This cannot be caused by any client side problems.

frgusto

Buy the way, is there any way to log pageiolatch?
Perhaps a tool that monitor sysprocesses or something like that.

Thanks Fredrik

Marije_Brummel

You can use the SQL Perform Tools (www.sqlperform.com) for that. We have them running at 80 customers.

Another tool is SSI. (www.stryk.info) but I do not have experience with that.

Waldo

I have some experience with both. I mainly use the SQLPerform Tools, because they are so easy to use.

I also use Stryk's field guide, which is very well written ... and can be a guide for you as well.. . Not really a tool, but a book :-)