I have the following table with ~100 million rows:
CREATE TABLE Result ( MetricId int NOT NULL, PeriodId int NOT NULL, Value decimal (18, 2) NOT NULL, CONSTRAINT PK_Result PRIMARY KEY CLUSTERED (MetricId ASC,PeriodId ASC) ) CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC)
My analysis of this situation (and I'm by no means a DBA) is:
The NC index should have
Valueas an included column. We are seeing queries that filter by
PeriodIduse the NC index but then need a Key Lookup on the PK to get the value. I think this will give us much better query performance.
The NC Index does not need
MetricIdin the key after
PeriodIdbecause SQL Server implicitly includes any columns in the clustered PK index.
As an aside: the main filter we use in queries is
PeriodIdtherefore it would be best to have the PK around the other way (ie. PeriodId,MetricId). I also believe
PeriodIdis more selective (?) in that it would make sense to filter first by
PeriodIdto get rid of the most rows during a query.
My question is then - how can I create or rebuild the following index?
CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC) INCLUDE (Value)
I have tried editing the existing index through Management Studio which requires a rebuild, and that times out after 5-10 minutes. It's a 'Wait Timeout' I believe.
I have tried creating a brand new index through Management Studio with the same result.
I have tried putting the database in Single User mode and creating the new index. Same result.
Upgrading to Enterprise is not an option for me.
How can I create this index on such a large table?