6

I am creating Clustered Column Store Index in SQL Server 2014. enter image description here

I am getting error as

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)".

I set

EXEC sp_configure 'remote query timeout', 60000; reconfigure EXEC sp_configure

Row Count = 304969603 Data space = 88,812.266 MB

6

If you use Query Analyzer(QA) go to Tools -> Option -> Connection.Reset all the values using 'Reset to Default' Button.By default there is no time out on QA.

Or, write your CREATE INDEX or ALTER TABLE statement in the query window and run it.

Setting the time out in SQL Server to -1 will prevent time outs. It's possible that you are changing the time out setting, but it won't take effect until a RECONFIGURE is performed.

Run:

sp_configure 'show advanced options', 1;

...and look at the time out settings there. Take note as to if the time out setting is active in the run_value column. If not, then you need to do a...

RECONFIGURE;

...to activate the changed setting.

14

In Management Studio for SQL 2014, the timeout for the designers is held in Tools > Options > Designers > Table and Database Designers with a default of 30 seconds.

enter image description here

However, as the syntax is so simple for creating a clustered columnstore, as already suggested, you should script it rather than using the designer, eg

CREATE CLUSTERED COLUMNSTORE INDEX IX_MyTable ON dbo.MyTable ON [PRIMARY]
GO

This option won't time out, but may struggle if you don't have enough memory.

In order to get the best out of your compression, you might also consider creating a clustered index on the table to pre-sort it, then drop it prior to creating the clustered columnstore. YMMV.

7

This involves two steps. One is described above by @wBob, however you will not be able to create it via the UI, it will still time out.

What you need to do is to click on the 'Script' icon and then run the generated script.

enter image description here

Hope this saves you some time.

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.