Determining Whether Events Can Be Tuned
If SQL Server Database Engine Tuning Advisor does not tune an event, the event is written to a tuning log. In the SQL Server Database Engine Tuning Advisor graphical user interface (GUI), on the General tab, you can select Save tuning log and view the log in the lower pane of the Progress tab. If you are using the dta utility, you can use the -e argument to specify a tuning log file name and location. For more information, see How to: View Tuning Output.
Common Reasons Why Database Engine Tuning Advisor Does Not Tune an Event
Some of the most common reasons why SQL Server Database Engine Tuning Advisor does not tune events are:
A workload references tables that the user has not selected to tune.
A workload references very small tables; for example, tables that contain fewer than 10 data pages.
SQL Server Database Engine Tuning Advisor cannot tune the workload within the specified time limit.
For more information, see Why Events Are Not Tuned.
Determining Whether an Event Can Be Tuned
The easiest way to determine whether SQL Server Database Engine Tuning Advisor can tune a particular event is to determine if a Showplan output can be obtained for that event. Use SQL Server Management Studio to determine if a Showplan output can be obtained. SQL Server Database Engine Tuning Advisor can tune the event only if a Showplan output is produced by the server for that event, in the database where the event was executed. For more information, see Displaying Graphical Execution Plans (SQL Server Management Studio).
Why Does Database Engine Tuning Advisor Recommend Dropping a Large Number of Objects?
If SQL Server Database Engine Tuning Advisor generates a large number of recommendations to drop physical design structures, this could be caused by the following reasons:
If your workload is small or it references a small subset of physical design structures, SQL Server Database Engine Tuning Advisor can recommend dropping the rest of the physical design structures because the workload does not reference them. SQL Server Database Engine Tuning Advisor assumes that the workload is representative of the normal workload on your server, and makes its recommendations based on that assumption.
If most of the queries in the workload are ignored, resulting in a large number of recommendations to drop physical design structures, this may occur for the following reasons:
The incorrect databases were specified for the -d argument if the dta utility was used.
Most of the queries in the workload reference small tables that contain fewer than 10 data pages.
Most of the Transact-SQL statements in the workload do not reference tunable objects. For example, if the workload contains many SET statements or DECLARE statements.
To determine what is actually causing SQL Server Database Engine Tuning Advisor to generate many drop recommendations, review the tuning log messages. For more information, see About the Tuning Log.