Are you getting complaints from users, that opening the General Journal (especially with date range) will take time to display to their PC, even if your server is not at peak time? Yes I do. During non-peak hour it will take around 15-20 seconds to display the content of the journals (see below) and during peak time it will consume almost a minute (Terrible.!!!!)
Every time I heard such performance problem and time is short, first to come into my mind is to add more memory, more CPU, bigger and better disk subsystem and so on. But doubling your horsepower is not a guarantee to double the performance of your application. You might end up throwing a lot of money.
But Alas.!!!!! from 15 seconds, it went down to 2 seconds (Terrible fast..!!!). Thanks to DTA (Database Engine Tuning Advisor of MS SQL). to know more about DTA, see books online.
Here is the process :
- Create workload for analysis ( how to )
- Open SQL Server Profiler and connect to your server
- On the Event Selection tab of Trace Properties, click Column Filters and go to 'NTUsername' and under 'Like' type your user name. This is optional, but I did this because I want to record only my session. Click OK and Run buttons.
- Now you are ready to create a workload
- Go back to Exact Globe, under finance tab, click General Journal (be sure you have a date range defined earlier) and wait until it will display all the journals.
- Once displayed, go back to SQL Server Profile session and you will see something like below
- Stop the trace and Save the workload.
- Open DTA (Database Engine Tuning Advisor) and connect to the SQL server
- Locate the file (workload you created) under "Workload/File", select also the "Database for workload analysis" and under the "Select databases and tables to tune" I select the database level (it means it will tune all the affected tables)
- on the "Tuning Option" tab I only want to consider additional Indexes, under PDS to use in database.
- I do not want the DTA to provide me any recommendation on partitioning, since I'm not using any partitioning.
- Clicking the keep all the existing PDS radio button tells the PDA to keep the existing structures.
- I also unchecked the "Limit Tuning Time" to remove time limit while tuning.
- If it is ready, click "Start Analysis" button on the upper menu and you will see the "Progress" tab.
- After couple of minutes you will see the "Recommendation Tab", take a look on the "Estimated Improvement". Mine is showing 95% increase in performance. Wow.!!! Superb.
- I can't wait to implement the recommendation. Let's do it. You have two ways to implement the recommendation.
- if you click "Apply Recommendation", you will be asked to "Apply Now" means immediately DTA will run the SQL script or you can schedule the recommendation to run at your convenient time.
- if you click "Save Recommendation", you can save it and execute later in SQL Analyzer window.
This task is little burdensome but you will be an instant hero for improving the performance of your Globe application. Happy Tuning.!!!!
