Fire and Forget Index Creation

Recently we added a new index that we realized was going to take a really long time to create on some of our existing tables with lots of data (it would of course be fine on new installs with no data). So we started to look for options to tell the database to create the index in the background and return control to the setup code (aka fire and forget). We didn't really care when the index creation finished as long as it did at some point.

using (SqlCommand cmd = sqlConnection.CreateCommand()) {
    cmd.CommandText = "CREATE INDEX index_name ON table_name (column_name)";
    connection.Open();
    cmd.BeginExecuteNonQuery();
}

This failed almost universally (a couple of indexes were created before the connection closed but basically it didn't work). As this similiar question in StackOverflow highlights, if the connection closes the query ends. We looked at implementing the ThreadPool option outlined in that question or just adding an EndExecuteNonQuery method to close the connection but both ran into a roadblock. Our Network Operations team runs the setup code from a Windows application. During testing we found that closing the Windows application also closed the connection meaning that the index creation fails. We could add a progress indicator to the application and have them wait to close the app but then we were essentially back to the blocking issue.

So somewhat relutcantly we decided to use SQL ServerAgent Service to create the indexes. The downside being if we wanted to do the same for Oracle we would have to write separate code (we work with both SQL Server and Oracle). It only works on SQL Server when the service is installed and running (which excludes SQL Server Express).

To kick off a job we added code similiar to the answer to this question. We also added the following test to see if SQL Agent is running otherwise we fallback to the old blocking code.

SELECT spid FROM MASTER.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher'

Ideally it would be nice if we could have just kicked this off from C# code but at least this allowed us to remove a roadblock and move on to the next issue.