• Repost - originally posted 26-06-2012

Hypothetical indexes


We had a query recently about a database that had a lot of objects starting with the name _dta_. We explained that these where hypothetical indexes and statistics that had been created by the Database Tuning Advisor. Most of the time the Database Tuning Advisor cleans up these objects on completion but they can be left over if the Database Tuning Advisor had been cancelled or something else happened whilst it was running. You can safely delete these objects after you check that they are truly hypothetical. For Microsoft SQL Server 2005 and later, check the is_hypothetical column of the sys.indexes catalog view, for Microsoft SQL Server 2000, use the predicate sysindexes.status & 0x20=0 to identify real indexes.

https://technet.microsoft.com/en-us/library/ms190172(v=sql.105).aspx


Recent Posts

See All

Standardise database sizes and automation

The default setting for the initial sizing of data and log files (and their autogrowth) is not adequate. I like to make sure that we set standard sizes for our data and log files - plus standard sizes

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p