Home / server / Questions / 203370
Accepted
Neil Trodden
Asked: 2010-11-19 04:01:00 +0800 CST

Why did my newly added index show up as a 'statistic'?

  • 772

We are attempting to tune our SQL Server 2005 database as we frankly have no indexing on our tables and our CPU usage is starting to max out at 100%.

We have ran SQL Server profiler for a 3 hour period which covers the time we recieve the most usage on our web front-end.

After we fed this into the Database Engine Tuning Advisor, it made a number of suggestions for indexes and statistics. Here is an example of one of them:

CREATE NONCLUSTERED INDEX [_dta_index_ChangeLog_9_245575913__K5_K2_K3_K4] ON [dbo].[ChangeLog] 
(
    [ChangePrimaryKey] ASC,
    [TableID] ASC,
    [ChangeDate] ASC,
    [ChangeType] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

..but that fails because there is already "an index or statistics with name '_dta_index_ChangeLog_9_245575913__K5_K2_K3_K4'" which I can see under the Statistics section on that table in SQL Server Management Studio. The only thing I have under Indexes is a clustered index on the primary key.

Why has it been added as a statistic and not an index?

sql-server-2005 indexes performance-tuning

1 Answers

  1. Best Answer

    Statistics are a kind of non clustered indexes that are created automatically by SQL Server based on database usage, and they share the same namespace (you can see some index metadata in sys.indexes).

    Just try to create the index with a more meaningful name like IDX_ChangeLog, it should work fine.

    • 1