Quick Scan Report – Leftover DTA Tables

What this check looks for

Tables and indexes in msdb whose names indicate they were created by the Database Engine Tuning Advisor, typically prefixed DTA_.

Why it matters

These are the working tables the Database Engine Tuning Advisor creates while it analyzes a workload. It is supposed to remove them when it finishes, and when a tuning session is cancelled, crashes or is disconnected, they are left behind.

In themselves they are close to harmless. They occupy some space in msdb, they appear in every object listing, and they confuse anybody auditing what is in the system databases. That is the whole direct cost, which is why this is a low severity finding.

The reason to pay attention is what they imply, and that is a more useful line of inquiry than the cleanup:

Somebody ran the Tuning Advisor against this instance. That is worth knowing, because:

• It may have been run against production. The Tuning Advisor’s analysis is expensive, and running it against a production instance during working hours is a common and costly mistake.
• Its recommendations may have been applied wholesale. The Tuning Advisor evaluates a captured workload in isolation and recommends indexes, indexed views and partitioning to serve it. It does not know about your write workload, your maintenance window, or the indexes another application depends on. Applying its output uncritically is a well known route to an over indexed database.
• Hypothetical indexes may be left behind, which is the more important leftover. The Tuning Advisor creates hypothetical indexes and statistics in the user databases while it evaluates candidates, and an interrupted session leaves those too.

Hypothetical objects are worth finding. They are not real indexes, so they provide no benefit, but they:

• Appear in index listings and confuse index reviews.
• Are included in some maintenance scripts, wasting time.
• Cannot be dropped with DROP INDEX in the usual way on older versions, which is why they tend to survive.
• Leave _dta_stat_ statistics objects behind, which are real statistics with real maintenance cost.

So the useful shape of this finding is: clean up the msdb tables because they are clutter, and then go and look in the user databases for what else the same interrupted session left.

How to confirm it yourself

The msdb objects:

USE [msdb];
GO
SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name],
       o.[name]                   AS [object_name],
       o.[type_desc],
       o.[create_date],
       o.[modify_date]
  FROM sys.objects AS o WITH (NOLOCK)
 WHERE o.[name] LIKE 'DTA[_]%'
    OR o.[name] LIKE '%DTA[_]reports%'
 ORDER BY o.[create_date];

create_date tells you when the tuning session ran, which is usually the most interesting column here.

What they occupy:

USE [msdb];
GO
SELECT OBJECT_NAME(p.[object_id]) AS [table_name],
       SUM(p.[rows])              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.partitions            AS p WITH (NOLOCK)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE OBJECT_NAME(p.[object_id]) LIKE 'DTA[_]%'
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id]
 ORDER BY [size_mb] DESC;

Then the more important search, in the user databases:

-- hypothetical indexes
EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME()                        AS [database_name],
       OBJECT_SCHEMA_NAME(i.[object_id]) AS [schema_name],
       OBJECT_NAME(i.[object_id])        AS [table_name],
       i.[name]                          AS [index_name],
       i.[type_desc],
       i.[is_hypothetical]
  FROM sys.indexes AS i WITH (NOLOCK)
 WHERE i.[is_hypothetical] = 1
    OR i.[name] LIKE ''_dta_index%'';';

-- statistics the tuning advisor left behind
EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME()                        AS [database_name],
       OBJECT_SCHEMA_NAME(s.[object_id]) AS [schema_name],
       OBJECT_NAME(s.[object_id])        AS [table_name],
       s.[name]                          AS [stat_name],
       s.[auto_created],
       s.[user_created]
  FROM sys.stats AS s WITH (NOLOCK)
 WHERE s.[name] LIKE ''_dta_stat%'';';

And whether any of its recommendations were actually applied and are now unused, which is the finding worth having:

SELECT OBJECT_NAME(s.[object_id]) AS [table_name],
       i.[name]                   AS [index_name],
       s.[user_seeks], s.[user_scans], s.[user_lookups], s.[user_updates],
       s.[last_user_seek], s.[last_user_scan]
  FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.[object_id] = s.[object_id]
                                          AND i.[index_id]  = s.[index_id]
 WHERE s.[database_id] = DB_ID()
   AND i.[name] LIKE '_dta_index%'
 ORDER BY s.[user_updates] DESC;

An index named _dta_index_... with high user_updates and no seeks or scans is pure overhead, and dropping it is a real improvement rather than housekeeping.

How to fix it

Drop the msdb tables, then clean up the hypothetical objects in the user databases.

1. The msdb leftovers. Confirm no tuning session is running, then generate the drops:

USE [msdb];
GO
SELECT 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME([name]) + ';'
  FROM sys.tables WITH (NOLOCK)
 WHERE [name] LIKE 'DTA[_]%';

Review the generated list before running it. That habit matters more in msdb than anywhere else, because a mistake there affects Agent, backup history and maintenance plans.

2. The hypothetical indexes, which are the more valuable cleanup:

USE [YourDatabase];
GO
SELECT 'DROP INDEX ' + QUOTENAME(i.[name]) + ' ON '
       + QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id])) + '.'
       + QUOTENAME(OBJECT_NAME(i.[object_id])) + ';'
  FROM sys.indexes AS i WITH (NOLOCK)
 WHERE i.[is_hypothetical] = 1;

On older versions a hypothetical index may resist DROP INDEX, in which case:

DBCC AUTOPILOT (5, DB_ID(), OBJECT_ID('dbo.YourTable'));

3. The leftover statistics:

SELECT 'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) + '.'
       + QUOTENAME(OBJECT_NAME(s.[object_id])) + '.' + QUOTENAME(s.[name]) + ';'
  FROM sys.stats AS s WITH (NOLOCK)
 WHERE s.[name] LIKE '_dta_stat%';

4. Review any _dta_index_ indexes that were actually created. Those are real indexes from applied recommendations. Use the usage query above: if an index has no reads and many writes, it is costing you on every insert and update and giving nothing back.

And if you use the Tuning Advisor again, a few rules make it safe:

• Run it against a test instance restored from production, not against production itself.
• Use a representative workload, captured over a period that includes your heavy processes, not a ten minute sample.
• Treat the output as candidates, not decisions. Check each recommendation against existing indexes, against the write workload, and against sys.dm_db_index_usage_stats.
• Let it finish, or use the session cleanup option, so it removes its own working tables.
• Rename anything you do apply, so a future review is not looking at an index whose name says it was generated by a tool.

How long it takes

About an hour, most of it checking the user databases for hypothetical objects and reviewing any applied recommendations.

Related reports

Related checks

Frequently asked questions

Are these tables dangerous? No. They are working tables from a tuning session and they hold no live data. The reason to look is what else the same session may have left in the user databases.

What is a hypothetical index? An index definition the Tuning Advisor creates to evaluate a candidate without building it. It provides no benefit, appears in index listings, and should be dropped.

Should I use the Database Engine Tuning Advisor? It can be useful on a test instance with a representative workload, treated as a source of candidates. Applying its recommendations directly to production is the usual way a database becomes over indexed.

How do I know which _dta_index_ indexes to keep? Check sys.dm_db_index_usage_stats. An index with reads is earning its keep whatever its name; one with only updates is costing you on every write.