Hints & Tips - Dimensions Slow Down/Locking - By Feruzan Warner - Support Team Leader, dhc
Mon, 23 Jan 2012
When systems start to slow down, reports can take longer to produce, users can be denied access, or everything might appear to freeze and stop working. This is not only frustrating but can lead to loss of business. These issues may force the user to incorrectly exit the software (switching the machine off/ rebooting/ending task) which might result in rollbacks or in the worst case data corruption.
Why would this suddenly happen when everything was fine yesterday? Generally it is because something has changed but attempting to identify what that is can be difficult. Has new software/new equipment been installed, or have there been network changes? With technology continually developing this is inevitable at some stage. Several or all of these components might be faulty, or not compatible with one other. It must be remembered therefore that changes impact on a wide area and the whole big picture must always be considered. With companies today outsourcing various elements of their business to others, it has become harder to manage.
Because the accounts system is a key area to a company's operations it is clearly visible here if things start to go wrong, therefore the first port of call is to ensure that the environment is correct, the database is well maintained, and customisation is fully compatible and optimised.
Here are a few tips on checking your environment is correct
1. Check that the hardware on all the workstations and servers is adequate to meet the users' needs.
2. Ensure that the network is configured for optimum performance. Performance Monitoring can be a good indicator and if regular comparison of results to baselines is carried out then you can be proactive rather than reactive.
3. Check basic SQL configuration complies with that supported by Access...
• master 80Mb (80Mb mdf 40Mb ldf)
• tempdb should be 25% of the largest datafile on that instance or a minimum of 80Mb (e.g. if the largest datafile is 1gb then set 250Mb mdf and 125Mb ldf)
• SQL properties for processor the ‘Parallelism’ set the specify number of processors to use for parallel execution of queries to parallelism set equals 1 (see Appendix A How to Configure SQL for Access Dimensions )
• SQL properties for memory to min 50% of the total memory and maximum setting being total memory less at least 128mb or that required by the operating system and adjust trigger settings (see Appendix A How to Configure SQL for Access Dimensions )
• Where multiple physical drives are available, separating the location of the LDF and MDF and the backup files optimises SQL performance.
4. Stop and start services regularly as this will clear all cached issues
5. Other software/applications can impact on performance eg MS Exchange running on the same server will take the processing resource available over SQL.
Return to News Article List
