Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired

I am creating Clustered Column Store Index in SQL Server 2014.

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

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.

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.

StackExchange Database Administrators Q#72816, answer score: 21