Tools

Fully Tested
and Supported
Tools

PERFORM-TOOLS

RYA CASE STUDY

The Royal Yachting Association – (RYA) is the UK’s national association for all forms of recreational and competitive boating, representing sailing, motor cruising, sports boats, windsurfing, inland boating, powerboat racing and personal watercraft.  There are over 100,000 member records in the Microsoft Dynamics NAV database with the system supporting all aspects of the business with 60 plus users accessing the system at any one time.  The database is approximately 45GB in size and the IT team needed to ensure that the response given by the Microsoft SQL Server database for the members and users in the organisation was optimal.  Connected Solutions, a Dynamics NAV development partner for the RYA was asked to provide consultancy for this area and the recommendation was to install Perform Tools for Maintenance and Analysis and provide supporting consultancy.

Project overview & consultancy performed

1. After installing Perform‑Tools™, we ran the Perform-Analysis function pt_memory to see how much memory was installed in the machine and how much of that SQL Server was using.  Memory is the most important SQL Server component as memory bridges the performance gap between the (fast) caches and (slow) disks.
This function showed us that SQL Server is utilising only about half of the RAM in the machine. Further investigation revealed that the reason was that RYA is using Standard Edition of SQL Server that limits to maximum 2GB of memory. If the RYA migrated to SQL Server Enterprise Edition the SQL Server memory could be set to at least 3GB, providing a massive 50% improvement in performance.

2. The next step was to check disk response times.  By running the Perform-Analysis function pt_filestats showed us that an average latency of about 1.3ms would not be a cause for concern for database performance. Furthermore,the Perform-Analysis function pt_filestats2 proved that the Dynamics NAV database functions were the only causing major disk activities, hence we concluded that no other databases were  consuming disk I/Os.

Both CPU and network utilization monitoring using Windows System Monitor showed that none of these components were bottlenecks.

3. After checking the infrastructure, we moved on and investigated the Dynamics NAV database settings. Less than optimum settings of the database can potentially cause unexpected performance issues due to various factors, such as “Auto Statistics” running an update during normal hours which in turn would impact performance on a specific process. We select Perform-Maintenance and run the pt_fixautooptions function.

To ensure that the optimum settings are maintained we scheduled this function to run unattended each night so that optimum settings are permanently maintained. We also identified that AutoGrow was turned on in the database.  This may lead to unexpected performance problems during automatic database expansion.  However we were assured that this is being monitored regularly and adjustments to the database files are made as required.

4. Furthermore we checked the database for any other misbehaviour and used the Perform-Maintenance function pt_checkclustered. The function showed us many Dynamics NAV tables were not clustered, these are called “heaps”. Heaps are valid table types; however they serve only one main purpose – for writing data.  The data is stored “as it comes” without any real organisation. Whilst this improves writing data to the tables, reading becomes slow as each record has it’s own address and sequential reading cannot happen as the data is stored “chaotically”. This can lead to immensely slow read times. After identifying this we were able to fix the issue and there were several possible approaches to this.  The preferred option was to upgrade the client to the very latest NAV release, where the issue had been resolved.  However, this had major implications so we decided to write a small codeunit in NAV which corrected the issue.  After running the codeunit we moved onto our next task.

Perform-Maintenance

Database Maintenance is the key to the smooth running of a Dynamics NAV implementation. Without performing database maintenance, tables become fragmented which impact on performance, (as described in Heaps – however with slightly less impact). A fragmented database can lead to 10-50 times slower reading operations!

Standard SQL Server provides for the re-indexing of tables. However, Perform-Maintenance provides the functionality to fine-tune how indexes are re-indexed!  This is based on the nature of the index and other characteristics such as it’s volatility.  For example, within the same table, Perform-Maintenance selects a different Fill-Factor for each index, perfecting the way these are optimised as compared to the standard SQL Server approach which uses the same fill factor for all tables and indexes in the database. Furthermore, Perform-Maintenance provides further unique functionality such as – removing zero, typically “old” records from the summary (so called SIFT) tables. Records in SIFT tables are normally never deleted from the database leading to situations where a normal table in Dynamics NAV has a very few records but the associated SIFT table contains millions of records – real orphans – potentially slowing down the server and wasting valuable computing power. By removing these blank, unnecessary records, backup sizes are reduced – therefore reducing the time needed for this daily housekeeping routine. Perform-Maintenance also ensures the removal of other overheads such as unnecessary created statistics.

The maintenance plan at The RYA is now watertight!  Perform Tools are reducing fragmentation, ensuring that all statistics are current, and all unnecessary overheads are removed. The maintenance steps to ensure the optimum database are:

Start

1.     Perform Full backup

2.     Perform Log backup

3.     Perform Shrink log

4.     Perform Bulk-Log recovery model

·          The database recovery is changed from Full to Bulk-Logged to prevent massive log growth during the further maintenance steps

5.     Delete zero SIFT records

·          On average 20,000 to 50,000 records deleted every week

6.     Re-index indexes with optimum fill factor

·          After several weeks some indexes have almost 100% fill factor while some have close to 50% fill factor

7.     Create statistics on all index keys

·          During this step we make sure that the server has vital information about all indexes so that it can choose the best execution plans

8.     Full recovery model

·          The database recovery model is changed from Bulk-Logged back to Full

Finish – The database is now ready to use

Project Conclusion

Using the functions in Perform-Maintenance we were able to design a perfect solution for The RYA.  The IT Team can focus on other tasks knowing that Perform-Tools are supporting their goals of a correctly maintained system for the befit of the members and users of the system.

So what do the IT Team at The RYA think of Perform-Tools?  Andy Galvin, IS Network Manager – feels “The Perform Tools, as recommended and monitored by Connected Solutions, has turned out to be a fantastic aid to the overall management of our systems, enabling us to deliver optimum performance for our users and supporting IT services to our members.  We now take for granted the functions that Perform Tools run for us each month – I can only say that it is like we have an additional member of staff, i.e. a SQL DBA!”

Typical Project Costs

To provide a service as described above we would have provided the complete Perform suite of tools and provided two days of consultancy.

We would like to thank the IT team at The Royal Yachting Association and Connected Solutions for allowing us to review the project.