Sql-server – Long running index rebuild

indexola-hallengrensql server

I'm running an index rebuild using Ola Hallengren's script below.

EXECUTE dbo.IndexOptimize
 @Databases = 'insight',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'

It has been running for almost 2 days. How bad would it be to kill the job? The index it is running against is 78% fragmented. The table at issue has 111,543,114 rows.

Best Answer

You should be able to see if it's doing a reorg or a rebuild by using sp_whoisactive or something similar and inspecting the sql_text column.

If you kill it, you will not lose the work it has done on previous indexes that were defragmented as part of this script. Those are done and committed.

As for the current index it's defragmenting, if it's doing a reorg, the progress it has made will not be lost if you kill it. If it's a rebuild, which seems to be the case given the data you've provided, it's still OK to kill it, but you will lose any progress that has been made.

Related Solutions

Sql-server – Should I lock the table during delete

Changing the locking to table locks will just make the deletes run even slower as the delete won't be able to run until the lock can be taken on the table which means that all other threads need to be finished or blocked. If you have foreign keys with delete cascade enabled that will probably take a lot of the time.

You might want to change it to a SQL Agent job so instead of running your app which connects and disconnects, you just run a loop deleting data until you are done.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table. This however does require Enterprise Edition.

Sql-server – Why would running a SQL query overnight break the database for the following day

My gut is that it's related to parameter sniffing; when you call the TVF's, do you pass in parameterized values or hard-coded values? For example:

SELECT * FROM FormatMonthEndReport(2013, 1);

will perform differently than

DECLARE @p1 int = 2013, @p2 int = 1; SELECT * FROM FormatMonthEndReport(@p1, @p2);

Best Answer

Related Solutions

Sql-server – Should I lock the table during delete

Sql-server – Why would running a SQL query overnight break the database for the following day

Related Question