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?