1

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:

  1. The NC index should have Value as an included column. We are seeing queries that filter by PeriodId use 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.

  2. The NC Index does not need MetricId in the key after PeriodId because SQL Server implicitly includes any columns in the clustered PK index.

  3. As an aside: the main filter we use in queries is PeriodId therefore it would be best to have the PK around the other way (ie. PeriodId,MetricId). I also believe PeriodId is more selective (?) in that it would make sense to filter first by PeriodId to 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?

  • 3
    Run the script through a new query window in SSMS. Not through the visual designer. – Martin Smith Oct 22 '15 at 9:12
  • @martinsmith Thanks - that did the trick. Feel free to add as an answer if you'd like. It took 15 minutes. – dan Oct 22 '15 at 9:51
1

Community Wiki answer for the solution originally provided in a question comment:

Martin Smith: Run the script through a new query window in SSMS. Not through the visual designer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.