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.