sql azure time consuming query

Question

I have a table in Sql Azure contains about 6M rows. I want to create a new index for it. the cmd is like:

CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].Table1
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)
INCLUDE ( [Column5],[Column6]) 

And after about 15 minutes, an error occurs

"Msg 10054, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

I tried several times, got the same error. But I have executed other time consuming queries,like:

Insert into table1(Col1,Col2,Col3) select Col1,Col2,Col3 from table2

Which took 20 minutes and returned successfully.

The queries were executed in the same Sql Azure DB. I don't know what's going on here. Could anyone help? Thanks!

Answer 1

I had a the same problem with at table containing 100M rows and contacted Microsoft Support. This is the reply i got:

The reason why you can’t create the index on your table is that you are facing a limitation on the platform that prevents to have transactions larger than 2GB.

The creation of an index is a transactional operation that relies on the transaction log to execute the move of the table pages. More rows in a table means more pages to put in the T-Log. Since your table contains 100 million of records (which is quite a big number), it is easy for you to hit this limit.

In order to create the index we need to change the approach. Basically we are going to use a temporary(staging) table to store the data while you create the index on the source table, that you would have previously cleared from data.

Action Plan:

1. Create a staging table identical to the original table but without any index (this makes the staging table a heap)
2. move the data from the original table to a staging table (the insert is faster because the staging table is a heap)
3. empty the original table
4. create the index on the original table (this time the transaction should be almost empty)
5. move back data from staging table to original table (this would take some time, as the table contains indexes)
6. delete the staging table

They suggest using BCP to move data between the staging table and the original table.

When looking in the event_log table...

select * from sys.event_log 
where database_name ='<DBName>'
and event_type <> 'connection_successful'
order by start_time desc

.. I found this error message:

The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Answer 2

Thanks for answering! Actually, I found the root cause either.
There's a solution to it, set the ONLINE=ON, in the online mode, the index creating task will be broke into multiple small tasks so the T-Log won't exceed 2GB.
But there's a limitation, the 'include column' of the index creating command can not be object with unlimited size, like nvarchar(max), if so the command will fail immediately.

So in Sql Azure, for a index creating operation like the following:

CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].Table1
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)
INCLUDE ( [Column5],[Column6]) 

take the following actions, if the previous failed.
1.create index using 'online=on'
2.if #1 failed, means either column5 or column6 is nvarchar(max), query the table size, if < 2GB, directly create index using online=off.
3.if #2 fail, means table size > 2GB, then there's no simple way to create index without temporary table involved, have to take action as ahkvk replied.