Once you have obtained a good ‘performance’ trace for your database, you can enlist the help of SQL Server’s tuning advisor. The advisor will treat the queries in the trace file as a good sampling of the traffic that the database experiences – and generate test loads that mimic that traffic. This means the advisor WILL stress the database that you are trying to tune. If you have a STAGING database server – which is a faithful copy of your production database, you can run the advisor against this staging database.

How many times will I need to run the database tuning Advisor (DTA) ?

Multiple times. The advisor provides you with several possible new indices. The truth is – you do not need to accept all those recommendations. In fact, if you did, you might actually end up slowing down your system (too many indices require maintenance on the part of sql server). Instead, what you need to do is only accept the recommendations that point to IMPORTANT indices. IMPORTANT includes UNIQUE indices, CLUSTERED indices and INDICES involving a SINGLE COLUMN. These MISSING indices are causing the most table scans and slowing your queries down. Adding these indices should help avoid full table scans and speed up your queries.

First Run of Tuning advisor (from Tools—>SQL Server Tuning Advisor)

  1. For the first run, click the Tuning Options tab, and then click the Indexes, No partitioning, and Keep all existing PDS radio buttons.
  2. Click Advanced Options, verify that the setting for Max. columns per index is 1023, verify that All recommendations are offline is selected, and then click OK.
  3. Add a ‘stopping time’ – I would recommend running it for at least 4 hours for the first go around.

Applying the Recommendations

Once the Advisor finishes running, it creates a set of recommendations (new Indices and statistics to create). The rest of this post discussed how and when you want to apply these recommendations. Note that you can ‘apply’ these any time after the advisor has finished running (simply open up the ‘session’ from the saved sessions in the Advisor’s Session Manager – window in the left pane).

Adding Indices – NOT all at once !

The Definition column of the advisor’s results shows you the type of index it is asking you to add.

  1. First, add all the UNIQUE indices and the clustered indices.
  2. Now re-run the advisor. The recommendations will change (since you have addressed some of the bigger missing indices).
  3. After this run, there shouldn’t be  any more suggestions for unique, clustered indices. Now, you can tackle the single column indices – and indices with two columns.  Be careful NOT to index too many columns on any given table. This may speed up retrievals, but will slow down inserts and updates significantly.
  4. After adding these, re-run the advisor. Now, there should be no more single column or dual column recommendations.
  5. REJECT all other recommendations for now. Dropping INDICES may be recommended – but first, re-test your database with all the newly added indices and see if performance improved any. You SHOULD see a significant improvement at this point (try re-running individual queries that you know to be slow. You can analyze individual queries using the DTA as described here).
  6. You can quit now or go one step further. Look at some of your slower queries – and the WHERE clause.  If you wanted to, you could add an index on JUST the columns in the WHERE clause. This asks SQL Server to maintain those columns in a performance can be greatly improved by building an index on just one of the where clause columns. From MSDN documentation:
    • Use a covering index to reduce the query execution time of frequently used statements. A covering index is a nonclustered index that has all the columns that come in a WHERE clause and in the query column selection.

Here is a simple flowchart.

Add UNIQUE, CLUSTERED –> Re-Run advisor –> Add Single Column, Dual Column –> Re-run advisor –> Examine WHERE clause of longer run queries –> Add INDEX (nonclustered) on COLUMNS in WHERE CLAUSE (of frequently used queries).

Note: If you have a primary key on a column, SQL Server will automatically create a CLUSTERED index on the column for you.

Adding Statistics

Along with your index recommendations, the advisor (advisor) would have provided a set of ‘statistics’ to be applied to each table. Statistics help SQLServer figure out whether to use an index or not. It may opt to actually do a full table scan – if for example – there are very few rows in a table, SQL Server will choose NOT to use an index (reading an index and then retrieving the corresponding record is MORE time consuming than reading the entire set of rows of the small table – Cost Based Optimizer). Another example is the classic Select * from People where GENDER = ‘FEMALE’ .If GENDER has an index on it, this query will be SLOWER with an index on it (since 50% of the records satisfy this criteria). It would be quicker to retrieve ALL records and DISCARD GENDER = ‘MALE’. In this case, an INDEX would slow things down – and a CBO (Cost Based Optimizer) will choose to ignore any index on the GENDER column.

Typically, SQL Server maintains statistics on its own. One way to check if auto maintenance of statistics is turned on is to look at your database Options (see below).

idera-statistics-defaultdbsettings2-570x5132.png6_2

So, should you accept the recommendations of the Advisor? In my experience, doing so does no harm – and may provide some improvement. Remember that once you create all those missing INDICES, the statistics will be maintained by themselves (if auto update and auto create is set to true as shown above).

Undoing (Rolling back) the tuning advisor recommendations

Sometimes, you will need to roll back the recommendations that you may have already ‘Applied’. This may happen if you ‘accept all recommendations’ at the very first go (too many indices may be created, which can slow things down instead of speeding them up). There is no GUI option to roll back the recommendations from the tuning wizard. However, the DTA prefixes all its indices (and statistics) with _dta_ which makes it easy (for a script) to find all the applied recommendations. See this post for details on how to drop all the (applied) recommendations using a script.

Large database tables

If a table takes up more than 10% of the total space, it is a candidate for its own filegroup. If you have some binary files being written to a table in the database, this may be a potential suspect table (in one case, we found the binaryfiles table taking up 90% of the total disk space).

Hardware Considerations – Memory and IO System for your Database Server

Total Server Memory

For Windows Server 2008 R2, the OS itself uses close to 3 GB. More information can be found here.

SQL Server Memory

To see what the max memory settings for SQL Server are, you can use the following commands:

EXEC  sp_configure'Show Advanced Options',1;

GO

RECONFIGURE;

GO

-- See what the current values are

EXEC sp_configure;

PAGEIOLATCH_XX

If you see excessive PAGEIOLATCH_XX waits for your queries, you are running low on memory.

Read this post for how much memory your SQL Server hardware should possess. If your server has a total of 4GB memory, set (specifically set) 3200 MB for SQL Server. If your server has 8GB, set 6400 MB for SQL Server.

IO

For database servers, drives are to be purchased as IOPS source.  A 512 Gb SSD would be ideal for storing the database files.

Summary

The SQL Server database tuning advisor is a useful tool provided you:

  1. Have a good performance trace available (see this post for how to generate a good trace).
  2. Know which recommendations to accept and which ones to reject.

p

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin