0

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?

Neil Trodden
  • 262
  • 5
  • 14

1 Answers1

1

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.

Alberto Martinez
  • 453
  • 2
  • 7
  • Thanks, the sharing a namespace thing helped a lot and I renamed them to something a little more appropriate – Neil Trodden Nov 19 '10 at 10:55
  • 1
    That little details sometimes make the difference between "I know the problem" and "this don't make sense at all". – Alberto Martinez Nov 19 '10 at 16:00
  • Very insightful! On the positive side, I think the only reason I do this is for the moment of clarity when you grok something properly. It's very satisfying, in a way more than fixing the issue. Fixing without understanding feels utterly hollow! Many thanks for your help! – Neil Trodden Nov 19 '10 at 21:44