Achieving Near 24/7 Uptime Using SQL Server Standard Edition
What obstacle stands between you and 24/7 uptime for your SQL Server? Many database administrators would say index maintenance, especially if they are running SQL Server Standard Edition.
Most database administrators do index maintenance using the built-in index maintenance tasks REBUILD and REORGANIZE. These maintenance tasks provide a quick, simple way to schedule index maintenance. However, there are two issues that are an obstacle to 24/7 uptime when you are using SQL Server’s built in maintenance tasks.
The first issue is that in Standard Edition the index rebuild task locks entire tables while they are being rebuilt. Users cannot access those tables during that time. Since it can sometimes take hours to rebuild a large database using the built-in maintenance task, 24/7 up-time might seem impossible in Standard Edition unless you simply do not rebuild your indexes. The consequence of that is poor performance.
To avoid the locking problems, some database administrators substitute a REORGANIZE task for a REBUILD task. Reorganization does not lock tables, even in Standard Edition. However, while reorganization may be better than nothing, it is not a replacement for rebuilding indexes. Failing to rebuild your indexes or substituting REORGANIZE for REBUILD will give you more uptime, but you will pay for it in lost performance.
All or Nothing
The second issue is that the built-in index rebuild task rebuilds ALL the indexes in the database, even the ones that are not fragmented. This compounds the locking problem by extending the time that the tables will be locked.
Considering that we can easily find the fragmentation level of each index before deciding whether to reindex it, it is hard to understand why Microsoft has not added the ability to selectively rebuild indexes that need it and to skip the ones that don’t. A cynic would say that a good, smart reindex maintenance task in Standard Edition might hamper sales of Enterprise Edition.
Enterprise Edition
The marketing department at Microsoft knows very well that Standard Edition users are between a rock and a hard place trying to balance index maintenance and uptime. That is why it reserves a feature called on-line indexing for the much more expensive Enterprise Edition. This feature allows you to rebuild indexes without locking the tables. In theory at least, this allows users to continue to work while the indexes are being rebuilt.
Enterprise Edition is a big step toward true 24/7 uptime but it is expensive and, even with the big price tag, it doesn’t take us all the way there. For one reason, tables with certain kinds of indexes and data types still cannot be rebuilt on-line. Even Enterprise Edition has to lock tables containing binary large objects like images or xml or spatial indexes.
Get Smart
To solve this problem, many clever people have written scripts and stored procedures that perform intelligent index maintenance. Often a good smart reindex script can reduce index maintenance time from hours to minutes. Table locking is also reduced significantly. because the number of tables needing to be locked is dramatically lower.
I often install smart scripts for my clients. After my latest install, run-time of the index maintenance job was reduced from four hours to seven minutes,.. honest. Tables were locked for only a few of those seven minutes.
How Does It Work?
All the smart scripts work basically the same way, but some have useful bells and whistles like support for rebuilding specific partitions, using online indexing on Enterprise Edition, etc. Most of these scripts will only work on SQL 2005 and later versions.
In these procedures a query is run against the sys.dm_db_index_physical_stats() function to retrieve data about indexes whose current logical fragmentation exceeds a specified minimum (usually between 5% and 15%). The indexes that exceed this value are queued for maintenance. This list of indexes needing attention is usually selected into a cursor.
The cursor is opened and takes each of these indexes and, depending on their current fragmentation level, either reorganizes them or rebuilds them. Usually the scripts are set to REORGANIZE indexes with less than 30% logical fragmentation and REBUILD those over 30%. Those that were found to have less than the minimum amount of fragmentation did not get into the list in the first place, so nothing is done to them. Most of the smart scripts or stored procedures take parameters that allow you to set these percentage levels to suit yourself.
A quick internet search will find lots of smart reindexing scripts, including those that work well and those that don’t. I like Lara Rubelke’s stored procedure. Lara is a technical architect at Microsoft and you can download her stored procedures HERE.
She has written a version for Standard Edition and another for Enterprise Edition. You get both in the download.
Expectations
While smart reindex scripts do not solve the problem of locked tables in Standard edition, they can make a huge difference in how long it takes to do the reindex and the level of resource usage required. In many databases, at any given time a great majority of the indexes do not need to be rebuilt or reorganized. This can make the smart scripts an order of magnitude faster than the built-in reindex tasks. Technically your uptime is not absolutely 24/7 using Standard Edition but in many cases it is very close.
Rebuiding Multiple Databases
It is more difficult to rebuild multiple databases using a smart script than it is with a SQL maintenance plan task. The built-in maintenance task allows you to check each of the databases you would like to rebuild or with a single click you can rebuild them all.
Using a smart script, it is not that easy. You can deal with this problem in a number of ways, depending on how many databases you have to maintain.
If you have only a few databases, you might want to create a job for each database. Most of the smart scripts, including the one I recommended above, take a database name as a parameter. Each job would consist of a call to the stored procedure using a different database name. Or you could create a single job with multiple calls to the stored procedure with a different database name parameter.
If you have a lot of databases to maintain, it might be best to create a cursor that executes the smart procedure for all databases, passing the database name into the procedure call. For example:
- DECLARE @dbname sysname;
- DECLARE @sqlstring varchar (128);
- DECLARE @execstring varchar(256);
- SET @sqlstring = ‘exec dba..RebuildOnline @databasename= ”’
- DECLARE db_cursor cursor fast_forward for
- SELECT name
- FROM sys.databases
- WHERE database_id > 4 — skip system tables
- OPEN db_cursor
- FETCH NEXT FROM db_cursor INTO @dbname
- WHILE @@FETCH_STATUS= 0
- BEGIN
- SET @execstring = @sqlstring + @dbname + ””
- — PRINT @execstring — uncomment to debug
- exec (@execstring);
- FETCH NEXT FROM db_cursor INTO @dbname;
- END
- CLOSE db_cursor;
- DEALLOCATE db_cursor;
If you need to exclude any databases you can add to the WHERE clause in the cursor declaration. Since we are querying the system view sys.databases, the procedure will automatically include any new databases and exclude any that have been dropped.
A Caution
If you have never rebuilt the indexes in your databases you may run into some issues the first time you do. It is likely that a lot of indexes will need to be rebuilt and it might take more time than I have led you to believe. Also, the first reindex will cause your data and log files to grow significantly. Make sure you have plenty of free space on those drives. If your databases are in FULL recovery mode, the first transaction log backup after each rebuild will be much larger than your normal log backup files.