Pageiolatch

frgustofrgusto Member Posts: 32
edited 2008-06-21 in SQL Performance
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?

Comments

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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 :mrgreen: )

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • frgustofrgusto Member Posts: 32
    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
  • kinekine Member Posts: 12,562
    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.)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • frgustofrgusto Member Posts: 32
    Thankyou. I'll post back.
  • frgustofrgusto Member Posts: 32
    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_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • frgustofrgusto Member Posts: 32
    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
  • krikikriki Member, Moderator Posts: 9,086
    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)
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • davmac1davmac1 Member Posts: 1,283
    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)?
  • frgustofrgusto Member Posts: 32
    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.
  • frgustofrgusto Member Posts: 32
    Buy the way, is there any way to log pageiolatch?
    Perhaps a tool that monitor sysprocesses or something like that.

    Thanks Fredrik
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • WaldoWaldo Member Posts: 3,412
    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 :-)

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
Sign In or Register to comment.