Steps to Create the SQL Server Trace

  1. Run SQL Server Management Studio (SSMS)
  2. Go to menu Tools -> SQL Server Profiler
  3. App launches, on "Trace Properties" window that opens up:
    1. Give the Trace Name something useful.
    2. Use the Template: Tuning
    3. Save the File. Save to Temp directory, name it something you will remember.
    4. Set maximum file size: 1000mb
    5. Enable File Rollover
    6. Enable trace stop time: Set it at least an hour in advance.
    7. Click 'Run'.
  4. After trace is finished, click SQL Server Profiler.

Using the SQL Server Profiler

  1. Back in SSMS, go to Tools -> Database Engine Tuning Advisor.
    1. On the application, in the main window, there will be a large tab with the server name and time/date.
    2. In 'General', name the session something useful.
    3. Workload: File. Select the FIRST file in the temp directory. *.trc file.
    4. The 'database for workload analysis' should be 'master'.
    5. Under 'Select databases and tables', select the database you want to analyze. Best to leave all tables selected.
    6. Moving to the 'Tuning Options' tab, turn off 'Limit tuning time' (more processes, better analysis).
    7. Leave all other settings as is. (Should have 'Indexes', 'No partitioning', and 'Keep all existing PDS' selected.)
    8. Click the "Start Analysis" button in the top toolbar.

Analyzing the Result Set

When it finishes, it will give a result set. Likely a large one. Typically, 99% of them will be 'Create Statistics', with one or two 'Create Indexes'.

You can save the entire output to a SQL file and then load the SQL file up into SSMS.

Generally, we manually remove the 'Create Index' statements, and just use the 'Create Statistics' ones. (Since they are by nature non-destructive and don't affect product upgrades)

If you decide to implement any of the Create Index statements, please record them down (e.g. in a SpiraTeam task) so that you can undo them before you try and upgrade your instance of SpiraTest, SpiraTeam, SpiraPlan or KronoDesk. This is because the indexes might block the installer changing some of the database tables (needed for a successful upgrade).