How to Tune the ProjectDox Database Using SQL Profiler and SQL Tuning Adviser?

Solution

Tuning the ProjectDox database using SQL Profiler, and Tuning Adviser

NOTE: The interface of the tools may vary with the version of SQL Server you use. Please consult Microsoft’s web site or other documentation for additional information.

IMPORTANT! Running the SQL Profiler and Tuning Adviser should be undertaken only by persons sufficiently familiar with SQL Server. The tools create temp files, and care must be taken not to fill up the system drive. Use this guide at your own risk. Avolve Software is not responsible for any damages.

1.    Confirm you have ProjectDox standard users, Project Admin, and System admin logins and passwords, and the URL for the site
2.    Open SQL Server Profiler (in SQL Management Studio)
3.    Select File, New Trace
4.    Login as an SA on the SQL Server
5.    Select the Events Selection tab
6.    Check Show all Columns checkbox
7.    Click the Column Filters button
8.    Choose Database Name
9.    Expand Like on the right side of the window, type the name of your ProjectDox database (in the example ProjectDoxLu,) and then click OK.

   NOTE: The actions describe in the next step are for general tuning. Your purpose for tuning may vary, and you may select specific activities to trace and tune (for example: logging in as a user with membership in many projects, waiting for the project list to display)

10.    Click Run when you are all ready to start.
    1.    Login as a low permissioned user. Use PD – view projects, files, folders, Eforms, etc. (Better if multiple active users are doing it live)
    2.    Login as PA, SA. Use ProjectDox – view projects, files, folders, Eforms, etc.
11.    Stop trace
12.    Open database tuning advisor
13.    Choose Workload, File radio button
14.    Navigate to the Trace file
15.    Choose the Database for workload analysis
16.    Place a checkmark next to the name of the database
17.    Click the Start Analysis button at the top of the Advisor
18.    When complete, Choose the Actions menu, Save Recommendations 
19.    Save the Recommendations as a SQL file, this is for audit trail
20.    Apply recommended indexes and statistics.
21.    Test your site again with the same actions to verify improvement.

Resources

For screenshots to clarify steps further, see attached file. 

Revised 02-26-2015