Chapter 33. Efficient backups without indexes

Greg Linwood

As databases grow in size, the challenges associated with managing correspondingly larger backups also increase, as they require proportionally more storage space and processing time to complete.

The practice of compressing database backups has emerged over recent years to address these problems, but another opportunity still remains that can further reduce backup time, space, and energy consumption—simply by eliminating index data from backups! Because the proportion of data consumed by nonclustered indexes is significant in many databases (often 50 percent or greater), the savings to be made from eliminating these indexes from backups is also significant in many cases.

In this chapter, we’ll investigate a practical method to reduce backup sizes and timing—by separating tables and indexes into different filegroups, and then using filegroup backups instead of full database backups to eliminate index data from routine backups.

The example scripts shown in this chapter have been tested on SQL Server 2005 and SQL Server 2008, but simple customizations could be applied to allow this technique to also be used with older versions.

It’s OK to not back up nonclustered indexes!

Before describing how to eliminate index data from backups, it’s first important to point out that a nonclustered index contains merely a copy of the rows from its associated table, sorted differently (by the index’s keys) to allow efficient searching during processing of queries. Eliminating this redundant index data from routine database backups does not limit the recoverability of the database data from its backup; indexes can always be re-created by scanning and re-sorting their underlying table rows if necessary.

Re-creating indexes certainly adds additional time to the restore process, and some may argue that this overhead is unacceptable. But in real practice, high availability is usually implemented with techniques other than relying on restoration of database backups (for example, database mirroring, or log shipping), so the timing of restore operations is rarely significantly important.

In many cases, restoration of a database is only performed to recover specific data rather than for high availability purposes. In this common scenario, the elimination of indexes from the restore process increases restore speed—and therefore availability of data (as restoring a tables-only filegroup is obviously faster than restoring a full database backup that contains both tables and indexes).

A simple example

Let’s walk through an example that demonstrates the steps involved in separating tables and indexes into different filegroups, then backup the tables-only filegroup, and finish with restoration of the backup.

We’ll begin by reviewing SQL Server’s default table and index filegroup placements to describe default behaviors (by which most databases operate), then move through the steps required to separate indexes from tables before looking at backup and restore.

We’ll use an extremely simple example—a database with only a single table and a single nonclustered index to demonstrate the essential steps before moving into a deeper discussion on practical issues associated with more complex real-world databases.

Default table and index storage behavior

By default, SQL Server stores all table and index objects within a filegroup named PRIMARY (assuming a default installation of SQL Server, without any modifications to the model database). In the vast majority of cases, most SQL Server databases contain only a PRIMARY filegroup, with all tables and indexes contained within it.

Before we can create any tables or indexes, we first need a database so let’s begin there—by creating an empty database. This will contain one filegroup named PRIMARY: 

CREATE DATABASE BackupTest;
USE BackupTest;
Note

The CREATE DATABASE command does allow fine-grained control over many attributes and configurations of the new database, including which filegroups and associated files are created (including addition of multiple filegroups)—but we are only concerned with inspecting default behavior at this point.

To verify existence of the PRIMARY filegroup, you can right-click the database in SQL Server Management Studio (SSMS) and view the Files tab in the Properties dialog box, or run the following query against the sys.filegroups system catalog view within the newly created BackupTest database. 

SELECT name, data_space_id, is_default FROM sys.filegroups;

The results should be similar to those displayed in figure 1, confirming the database has one default filegroup named PRIMARY.

Figure 1. List of filegroups from sys.filegroups

name

data_space_id

is_default

PRIMARY

1

1

By default, unless further filegroups are explicitly created, all tables created within the database are stored within this PRIMARY filegroup. Any indexes created on those tables are also stored within the same filegroup as their tables, and therefore will be stored within the PRIMARY filegroup.

Let’s continue by creating a simple two-column table:

CREATE TABLE dbo.Table1 (
   Col1 INT NOT NULL primary key
 , Col2 INT NOT NULL
);

To verify that the preceding table is created within the PRIMARY filegroup, execute the following command:

SELECT d.*
FROM sys.data_spaces d, sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Table1')
  AND d.data_space_id = i.data_space_id
  AND i.index_id < 2;

Results should be returned similar to those displayed in figure 2, confirming that Table1 was created on the default PRIMARY filegroup.

Figure 2. Confirm that Table1 was created on the default PRIMARY filegroup.

name

data_space_id

type

type_desc

is_default

PRIMARY

1

FG

ROWS_FILEGROUP

1

If we now create a nonclustered index on Table1 named ncix_Table1, it will also be created in the PRIMARY filegroup:

CREATE NONCLUSTERED INDEX ncix_Table1 ON dbo.Table1 (Col1);

To verify that our new index has also been created within the PRIMARY filegroup, run the following command:

SELECT i.name, i.index_id, i.type_desc, i.data_space_id
FROM sys.data_spaces d, sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Table1')
  AND d.data_space_id = i.data_space_id
  AND i.index_id > 1;

Results should be returned similar to those displayed in figure 3, confirming that the index ncix_Table1 was created within data_space_id 1 (PRIMARY).

Figure 3. Confirm that index ncix_Table1 was created within data_space_id 1 (PRIMARY).

name

index_id

type_desc

data_space_id

ncix_table1

2

NONCLUSTERED

1

Our example so far represents the most common behavior for storing table and index objects in SQL Server databases—they’re stored within the PRIMARY filegroup by default.

Note

Few database administrators (DBAs) bother to reconfigure the physical layout of their databases until growth in data or transaction processing volumes force them to fine-tune physical database layout across disk systems using filegroups.

When using the default filegroup configuration, there’s no option other than to back up both tables and indexes together. Full database backups always back up all tables and indexes, regardless of filegroup configuration and a filegroup backup of only the PRIMARY filegroup would produce a similar result in this case, as the database’s only table and index are both contained within the PRIMARY filegroup.

Adding a dedicated filegroup for nonclustered indexes

To enable us to eliminate nonclustered indexes from our backups via filegroup backups, we must first move our nonclustered index ncix_Table1 from the PRIMARY filegroup to another filegroup, allowing the PRIMARY filegroup to be backed up in isolation from any index filegroups. We must first create another filegroup, which we’ll dedicate to the storage of nonclustered indexes.

To create a nonclustered index only filegroup, run the following command: 

ALTER DATABASE BackupTest
ADD FILEGROUP NCIX_FG;

This creates another filegroup within the database named NCIX_FG (short for nonclustered index filegroup). But we must also add at least one physical operating system file to the filegroup before database objects can be stored within it (alter the FILENAME path as appropriate for your filesystem): 

ALTER DATABASE BackupTest
ADD FILE (
   NAME = N'NCIX_FG_F1'
 , FILENAME = N'C: NCIX_FG_F1.ndf')
TO FILEGROUP NCIX_FG;

Moving nonclustered indexes into the new filegroup

Now that we have created a nonclustered index only filegroup to store our nonclustered index, we can relocate the nonclustered index into it. To accomplish this, we re-issue the CREATE INDEX statement, with two additional options, DROP_EXISTING and ON NCIX_FG: 

CREATE NONCLUSTERED INDEX ncix_Table1 ON dbo.Table1 (Col1)
WITH DROP_EXISTING --, ONLINE = ON
ON NCIX_FG;

The DROP_EXISTING option causes the newly created index to be created as the replacement of the existing index, without needing to explicitly drop the existing index (which could damage query performance for queries that might be in flight, while the index is dropped). The ONLINE option can be used to further reduce concurrency impact with SQL Server 2005 or 2008 Enterprise Edition.

The ON NCIX_FG clause is the main focus of our attention in this statement, which defines the new location for the nonclustered index.

Backing up only the PRIMARY filegroup

Now that we’ve successfully separated our nonclustered indexes (one only) from the PRIMARY filegroup into a separate filegroup, it’s now possible to perform a tables-only filegroup backup. Instead of issuing a standard BACKUP DATABASE command, add a filegroup specification to back up only the PRIMARY filegroup. For example: 

BACKUP DATABASE BackupTest
FILEGROUP = 'PRIMARY'
TO DISK = 'E:BackupTest_Primary.bak'

SQL Server will then perform a filegroup backup of the PRIMARY filegroup only, without copying any contents from the newly created NCIX_FG to the backup set.

Note

Although no data is backed up from NCIX_FG, the backup still contains definitions of nonclustered indexes because index metadata information is stored in system tables, which are located in the PRIMARY filegroup (sys.indexes, sys.index_columns and so on). This means that we can recreate the nonclustered indexes in the restore process from their definitions, even though we don’t have their allocated data structures in our backup file.

Restoring the PRIMARY filegroup backup

The process required to restore the database from the PRIMARY filegroup backup differs depending on whether you’re restoring only to perform data extraction purposes (re-creation of indexes not required), or whether you intend to bring the database fully online (re-creation of indexes is required).

Restoring from the PRIMARY filegroup backup to perform data extraction is simple and faster than restoring a full backup. On the other hand, restoring the database back to its full state to bring it back into a production role—including a rebuild of all nonclustered indexes—requires more steps than from a simple full database backup.

Let’s quickly review both of the steps required in each scenario.

Restoring for extraction only

When restoring a database from the PRIMARY (tables-only) filegroup for data extraction/examination only, you include FILEGROUP and RECOVERY (or NORECOVERY if you also wish to restore a chain of log backups) clauses in your RESTORE command, as follows: 

RESTORE DATABASE BackupTest
FILEGROUP = 'PRIMARY'
FROM DISK = 'E:BackupTest_Primary.bak'
WITH RECOVERY

SQL Server will then restore the PRIMARY filegroup and recover the database, bringing it online and available for querying. This allows extraction of data by executing SELECT commands, but INSERT, UPDATE, or DELETE commands will fail with error 8607: 

Msg 8607, Level 16, State 1, Line 1: The table '[Tablename]' cannot be modified
     because one or more nonclustered indexes reside in a filegroup which is not
     online.

It’s also possible that SELECT statements might fail if a nonclustered index is used to process the query, either via selection by SQL Server’s query optimizer or explicit index hint. In this case, error 315 will be returned: 

Msg 315, Level 16, State 1, Line 2: Index "[IndexName]" on table "[TableName]"
     (specified in the FROM clause) is disabled or resides in a filegroup which
     is not online.

In this scenario, you might need to either remove index hints, or explicitly hint SQL Server to access the table via its heap or clustered index storage, instead of via the nonclustered index (which is offline).

Restoring for production use

Restoring a database from a PRIMARY filegroup backup for production use does require a few more steps than restoring from a regular full database backup. The most significant of these involves rebuilding nonclustered indexes from the underlying tables.

In summary, these are the steps required to bring a database fully online from a PRIMARY filegroup backup:

1.  

Restore the primary filegroup backup and recover the database.

2.  

Script out index definitions from the PRIMARY filegroup, using a new filegroup name.

3.  

Restart SQL Server in single-user mode (with -m startup parameter).

4.  

Connect to SQL Server using the Dedicated Administrator Connection.

5.  

Delete nonclustered index definitions from the sys.sysindexes system view.

6.  

Remove -m startup parameter and restart SQL Server in multi-user mode.

7.  

Create a new, renamed filegroup to re-create the nonclustered indexes in.

8.  

Rebuild nonclustered indexes in the new filegroup.

Note

Scripting nonclustered index definitions from the PRIMARY filegroup is still possible even though the nonclustered index filegroup hasn’t been restored, because index definitions exist within the system meta tables in the PRIMARY filegroup, regardless of where the physical index storage allocations are located. This is a crucial fact that enables this technique to work.

Before walking through each of these steps, two issues associated with this technique should be discussed:

  • An update to system views is required to re-create nonclustered indexes (step 5).
  • The nonclustered index filegroup’s name must be changed (step 7).
Issue 1: Index Definitions Must Be Removed by Updating a System View

The process of re-creating indexes following restoration of the PRIMARY filegroup requires deleting existing index metadata from the sys.sysindexes system view prior to re-creating indexes (step 3 in the previous list). This fact might discourage some from using this technique, but it should be pointed out that updating this system view does not, in and of itself, compromise the integrity of the database, as the database tables that contain the real data in our database are stored within the PRIMARY filegroup and are not affected by the update of this system view.

Warning

Updating a system catalog is an operation that’s not supported by Microsoft. An alternative supported solution is to reconstruct the database by scripting its definition, re-creating the database, exporting all table data from the backup, and re-importing to the new re-created database.

This system update is required because neither DROP INDEX nor CREATE INDEX WITH DROP_EXISTING work when only the PRIMARY filegroup has been restored. Each fails with an error stating that the index’s filegroup is offline as SQL Server attempts to remove the index’s allocations, which aren’t available during execution of the command.

To avoid this system catalog update, an alternative is to re-create an entirely new database by scripting all database objects from the PRIMARY filegroup (tables, indexes, and all other objects) and exporting all data from the restored PRIMARY filegroup into that new database. This is obviously more time-consuming than only recreating nonclustered indexes (as tables are also re-created in the new database), but it’s a workable alternative that allows you to bring a database back online from PRIMARY filegroup backup without using any system catalog updates.

Issue 2: New Nonclustered Index Filegroup Must be Renamed

Another issue is that the filegroup that contained the nonclustered indexes in the source database (NCIX_FG in our example) cannot be fully removed from the restored database without further system catalog updates.

This is less of a problem though, as another filegroup can be added to the restored database and nonclustered indexes be built into it. The name of a filegroup is rarely important to an application, so this doesn’t pose a serious problem—simply create another filegroup (for example NCIX_FG2) and rebuild nonclustered indexes into it instead. This is what we’ll do in our example.

Restoring for production use—step by step

Let’s walk through the individual steps required to restore our database for production use from a PRIMARY filegroup backup.

Step 1—Restore the Primary Filegroup Backup and Recover the Database

The first step is identical to the restore process for extraction only. Assuming our PRIMARY filegroup backup remains located in the root of E: drive, execute the following command: 

RESTORE DATABASE BackupTest
FILEGROUP = 'PRIMARY'
FROM DISK = 'E:BackupTest_Primary.bak'
WITH RECOVERY

This restores the database’s PRIMARY filegroup, meaning that all tables, views, stored procedures, user-defined functions, and other database objects stored in the PRIMARY filegroup have been restored to the database. Index definitions have also been restored to the system catalogs (which reside in the PRIMARY filegroup), but their data structure allocations have not, as they are located within the dedicated nonclustered index filegroup (NCIX_FG in our example)—which was neither backed up nor restored.

Step 2—Script Out Index Definitions from Primary Filegroup

In the current state of our database, it’s not possible to rebuild our nonclustered indexes using more convenient options such as DBCC DBREINDEX, ALTER INDEX REBUILD, or CREATE INDEX WITH DROP_EXISTING. Each of these generates an error whether executed from a normal connection or dedicated administrator connection (DAC), because each requires existing index allocations to be available during execution of the command.

Instead, we’ll script index definitions out to a file and use the script later (step 8), to rebuild the indexes, after we have dropped index definitions from the sys.sysindexes system catalog and created a new nonclustered index filegroup to contain the new indexes.

To generate the index scripts, use the utility stored procedure (SP) described later in this chapter, named usp_Manage_NCIX_FileGroup. This SP accepts two parameters—@ScriptOnlyOrExecute (nvarchar(6)) and @NCIXFGName (nvarchar(255)).

The @ScriptOnlyOrExecute parameter should be set to N'Script', which instructs the SP to generate scripts only, without executing them.

The @NCIXFGName parameter should be provided with the name of a new filegroup to rebuild nonclustered indexes into. At this stage, this filegroup does not exist, but we’ll create it in step 7. The name provided for this new filegroup must be different from the existing nonclustered index filegroup for this database (which is NCIX_FG in our example) because that filegroup’s descriptive metadata still exists in the system catalogs. For our example, we’ll create another filegroup named N’NCIX_FG2’. 

EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2'

Output from the execution of this procedure should contain a list of CREATE INDEX statements, one per nonclustered index in the database. Save this output to a script file (for example NCIXScripts.sql) for later use in step 7. Note that each CREATE INDEX statement will include the filegroup name NCIX_FG2. When we run this script later, nonclustered indexes will be re-created within this filegroup, so we’ll need to add it to the database (which we do in step 7).

Step 3—Restart SQL Server in Single-User Mode

Before we can re-create our nonclustered indexes, we need to remove existing index definitions from the database by deleting them from the sys.sysindexes system catalog. This requires us to connect to SQL Server in single-user mode, using the DAC before we can perform the DELETE.

Starting SQL Server in single-user mode requires adding the -m startup parameter via the SQL Server 2005 or 2008 Configuration Manager utility, as displayed in figure 4.

Figure 4. Adding the -m startup parameter in SQL Server Configuration Manager

1.  

Navigate to the SQL Server Program Files menu.

2.  

Open the SQL Server Configuration Manager utility.

3.  

Right-click on the relevant instance of SQL Server from the right-hand window.

4.  

Select the Properties menu.

5.  

Click on the Advanced tab.

6.  

Select the Startup Parameters configuration.

7.  

Add ; -m to the end of the existing list of Startup Parameters (or the beginning of Startup Parameters with SQL Server 2008).

8.  

Click Apply, and click OK in the warning dialog box that appears.

9.  

When the Properties dialog box closes, right-click on the SQL Server instance and select Restart.

Step 4—Connect to SQL Server using Dedicated Administrator Connection

Once the SQL Server instance has been restarted, connect through the DAC to allow updates to the system catalogs. Connect or reconnect an SSMS connection by adding ADMIN: to the beginning of the server name in your connection dialog box, as displayed in figure 5.

Figure 5. Specifying the Dedicated Administrator Connection using the SSMS connection dialog box

Step 5—Delete Nonclustered Index Definitions

Once connected through the DAC, delete existing nonclustered index definitions from the system schema by running the following command: 

DELETE
FROM  sys.sysindexes
WHERE OBJECTPROPERTY (id, 'IsUserTable') = 1
  AND indid > 1 AND indid < 255

Executing this command allows us to run the script generated in step 2 to re-create all nonclustered indexes—but first we need to remove the -m startup parameter, restart the SQL Server, and add a new nonclustered index using the filegroup name specified for the @NCIXFGName parameter in step 2.

Step 6—Remove -m Startup Parameter and Restart SQL Server

To remove the -m startup parameter, follow the steps outlined in step 3 but remove ; -m from the Startup Parameters configuration before restarting the SQL Server instance.

Step 7—Create a New, Renamed Filegroup

Once the SQL Server instance has been restarted in multi-user (normal) mode, a new filegroup must be added prior to re-creating the database’s nonclustered indexes. This is a two-step process; first we create the filegroup by executing the following command: 

ALTER DATABASE BackupTest
ADD FILEGROUP NCIX_FG2;

Then, we add at least one file to the filegroup by executing the following command:

ALTER DATABASE BackupTest
ADD FILE (
   NAME = N'NCIX_FG2_F1'
 , FILENAME = N'C: NCIX_FG2_F1.ndf')
TO FILEGROUP NCIX_FG2;
Note

The name used for this new filegroup must match the name provided to the parameter @NCIXFGName in step 2 because the script generated in step 2 will attempt to build nonclustered indexes into this filegroup when it is run in step 8.

Step 8—Rebuild Nonclustered Indexes in the New Filegroup

To re-create the nonclustered indexes in the new filegroup, connect to the database using SSMS and execute the script created in step 2.

usp_Manage_NCIX_Filegroup

This utility stored procedure has been designed to simplify two tasks described in this technique:

  • Move a large number of nonclustered indexes into a dedicated filegroup, as part of reorganizing your database in preparation to perform PRIMARY (tables-only) filegroup-based backups.
  • Generate a script to re-create nonclustered indexes during the restore process if necessary.

The workflow of this stored procedure iterates over a database’s indexes, generating a CREATE INDEX statement for each index, and either executes the statement (when reorganizing the database in preparation for performing PRIMARY filegroup backups) or prints the statement (to generate scripts for re-creating nonclustered indexes during restore). The value supplied for the @ScriptOnlyOrExecute parameter defines which behavior will occur. Supply N'Execute' to execute the scripts (moving the indexes into the filegroup named in the other parameter @NCIXFGName) or N'Script' to script out the CREATE INDEX statements when restoring the database.

Note that slightly different CREATE INDEX statements are generated when N'Execute' is supplied to the @ScriptOnlyOrExecute parameter. When N'Execute' is supplied, WITH DROP_EXISTING is appended to the CREATE INDEX statement so that each existing index remains in place while a new copy is being created in the new, dedicated nonclustered index filegroup. In addition, the script identifies whether the edition of the SQL Server instance is Enterprise or Standard Edition and includes WITH ONLINE=ON if Enterprise Edition is detected. The idea here is to cause minimal interruption to queries during reorganization of the database, with close to zero interruption if the Enterprise Edition of SQL Server is in use.

Here’s a sample usage. Move all nonclustered indexes into a newly created filegroup named ‘NCIX_FG’:

EXEC usp_Manage_NCIX_FileGroup N'Exec', N'NCIX_FG'

As in listing 1, script out CREATE INDEX statements into a newly created filegroup named ‘NCIX_FG2’.

Listing 1. Scripting CREATE INDEX commands to a new filegroup 
EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2'

CREATE PROCEDURE [dbo].[usp_Manage_NCIX_FileGroup]
   @ScriptOnlyOrExecute NVARCHAR(6) /* N'Script' or N'Exec' */
 , @NCIXFGName NVARCHAR(255) /* the name new filegroup to move NCIXs into*/
AS
SET NOCOUNT ON

/*cursor variables*/
DECLARE @tnm NVARCHAR(128), @ixnm NVARCHAR(128), @cnm NVARCHAR(128)
      , @schnm NVARCHAR(128), @isPK BIT, @isUn BIT, @isIncCol BIT
      , @cixnm NVARCHAR(128), @ctbschnm NVARCHAR(256)
      , @ixcr NVARCHAR(4000), @ixcnt INT, @indid INT, @order NVARCHAR(5)
      , @inccols NVARCHAR(4000)
SELECT @ixcnt = 0, @cixnm = N'', @ctbschnm = N''

/*open cursor over schema / table / index / columns*/
DECLARE cr CURSOR FOR
SELECT ss.name, so.name, si.name, N'[' + sc.name + N']', is_primary_key
     , CASE WHEN is_descending_key = 0 THEN N'' ELSE N' DESC' END
     , is_included_column, si.index_id, is_unique
FROM sys.schemas ss
JOIN sys.objects so on ss.schema_id = so.schema_id
JOIN sys.indexes si on so.object_id = si.object_id
JOIN sys.index_columns ic ON si.object_id = ic.object_id
                         AND si.index_id = ic.index_id
JOIN sys.columns sc ON ic.object_id = sc.object_id
                   AND ic.column_id = sc.column_id
WHERE OBJECTPROPERTY (so.object_id, 'IsUserTable') = 1
  AND si.index_id > 1 AND si.index_id < 255 /*only interested in NCIXs*/
ORDER BY ss.name, so.name, si.name, ic.index_column_id
/*order crucial for cursor logic*/
OPEN cr

FETCH NEXT FROM cr INTO @schnm, @tnm, @ixnm, @cnm, @isPK, @order, @isIncCol,
     @indid, @isUn

/*move over cursor, constructing & executing a
  drop / create index statement per index*/
WHILE @@FETCH_STATUS = 0
 BEGIN

  IF @ixnm != @cixnm or (@schnm+@tnm != @ctbschnm) /*new index or table*/
   BEGIN
    /*if index, table or schema name changes, reinitialise*/
    IF @schnm+@tnm != @ctbschnm SET @ctbschnm = @schnm+@tnm

    SELECT @ixcnt = @ixcnt + 1
    IF @ixcnt > 1
     BEGIN
      SELECT @ixcr = LEFT(@ixcr, LEN(@ixcr) - 2) + N')'
        + CASE WHEN LEN(@inccols) > 1 THEN N' INCLUDE ('
        + LEFT(@inccols, LEN(@inccols) - 2) + N')' ELSE N'' END
        + N' WITH (DROP_EXISTING = ON'
        + CASE WHEN SERVERPROPERTY('EngineEdition') = 3
          THEN N', ONLINE = ON)' ELSE N')' END
        + N' ON [' + @NCIXFGName + ']'
      /*execution of create NCIX in other FG occurs
        on first row of NEXT index*/
      PRINT @ixcr; IF @ScriptOnlyOrExecute = N'Exec' EXEC(@ixcr)
     END
    SELECT @cixnm = @ixnm, @inccols = ''
    SET @ixcr = N'create ' + CASE WHEN @isUn = 1
                                  THEN N'unique ' ELSE N'' END
              + N'nonclustered index [' + @ixnm + N'] on ['
              + @schnm + N'].[' + @tnm + N'] (' + @cnm + @order
              + N', '
   END
  ELSE
   BEGIN
    /*if same index, build key of included cols csv list*/
    IF @isIncCol != 0 SET @inccols = @inccols + @cnm + N', '
    ELSE SET @ixcr = @ixcr + @cnm + @order + N', '
   END

  FETCH NEXT FROM cr INTO @schnm, @tnm, @ixnm, @cnm, @isPK, @order,
     @isIncCol, @indid, @isUn
 END

/*should usually be one last index (assuming there were any)*/
IF @ixcnt > 1
 BEGIN
  SELECT @ixcr = LEFT(@ixcr, LEN(@ixcr) - 2) + N')'
    + CASE WHEN LEN(@inccols) > 1 THEN N' INCLUDE ('
    + LEFT(@inccols, LEN(@inccols) - 2) + N')' ELSE N'' END
    + N' WITH (DROP_EXISTING = ON'
    + CASE WHEN SERVERPROPERTY('EngineEdition') = 3
           THEN N', ONLINE = ON)' ELSE N')' END
    + N' ON [' + @NCIXFGName + ']'
  PRINT @ixcr; IF @ScriptOnlyOrExecute = N'Exec' EXEC(@ixcr)
 END

CLOSE cr ; DEALLOCATE cr

 

Note the following points about the preceding script:

  • The script iterates over all schemas and their user tables within the current database, identifying all nonclustered indexes and generating a CREATE INDEX .. WITH DROP_EXISTING ON [NCIX_FG] script for each index, which is then executed dynamically to move each nonclustered index.
  • The script will not attempt to move tables. Heaps or clustered indexes are eliminated by the cursor query’s WHERE filter:
    and si.index_id > 1 and si.index_id < 255"
  • Unique definitions, key order, and included columns are all also preserved, so nonclustered indexes should be moved identically with their current definitions.
  • Each individual CREATE INDEX statement is also printed, allowing easy review of the commands that have been dynamically executed by the script.

Planning before moving NCIXs into a dedicated filegroup

Using the usp_Manage_NCIX_FileGroup utility stored procedure makes moving all of your database’s nonclustered indexes into a new, dedicated nonclustered index filegroup an easy process, but there are some important considerations that need to be addressed before doing this.

Moving NCIXs temporarily requires additional disk space

As nonclustered indexes are moved into the new, dedicated nonclustered index filegroup, extra space needs to be consumed in the filesystem by the filegroup to allocate new page extents to contain the moved nonclustered indexes.

At the same time, space is being freed within the PRIMARY filegroup (as each nonclustered index is moved out to the new filegroup) but this space is not freed back to the filesystem. Filegroup space is only returned to the filesystem if the filegroup’s files are explicitly shrunk, using DBCC SHRINKFILE. This doesn’t happen automatically, so moving nonclustered indexes out of the PRIMARY filegroup to a new dedicated nonclustered index filegroup will require consumption of more filesystem space.

How much more space is required depends on how much nonclustered index data your database contains. The more nonclustered index data you have, the more filesystem space you need for the new filegroup but the more space you will save in your tables-only filegroup backups.

The amount of space consumed by nonclustered indexes can be roughly estimated in SQL Server 2005 and SQL Server 2008 by running the following query: 

SELECT SUM(page_count) * 8192.0 / 1048576 as NCIXs_Mb
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'LIMITED')
WHERE OBJECTPROPERTY (object_id, 'IsUserTable') = 1
  AND index_id > 1 AND index_id < 255

Here is a similar query for SQL Server 2000: 

SELECT SUM(dpages) * 8192.0 / 1048576 as NCIXs_Mb
FROM sysindexes
WHERE OBJECTPROPERTY (id, 'IsUserTable') = 1
  AND indid > 1 AND indid < 255

Moving NCIXs creates empty space in PRIMARY filegroup

Once nonclustered indexes have been moved out of the PRIMARY filegroup to a new dedicated nonclustered index filegroup, a significant amount of space will have been freed within the PRIMARY filegroup.

This space can be returned to the filesystem by shrinking the filegroup’s files via DBCC SHRINKFILE, but using this command will significantly fragment any clustered indexes within the filegroup.

Another option is to add another dedicated filegroup for table storage (heaps and clustered indexes), leaving the PRIMARY filegroup for system tables only. The process of building this additional filegroup requires even more filesystem space, but allows you to re-create the table storage structures within it without fragmenting those structures. After completion of this step, you can shrink the PRIMARY filegroup without significant impact.

Alternatively, you can leave the free space in the PRIMARY filegroup. A problem with this approach is that restoring backups of the database in this state requires equivalent filesystem space on the server the backup is being restored to. Given that the PRIMARY filegroup contains a significant amount of empty space, this might create difficulties in restoring backups on standby or development servers.

Log shipping

So what about combining this technique with log shipping and piecemeal restore? Log shipping standby databases are typically initialized by restoring a full backup of the primary database, and then continuously synchronized by performing regular transaction log backups on the primary database, and restoring those transaction log backups on the standby with NORECOVERY until the standby database needs to be brought online.

It’s also possible to initialize a standby database by restoring a tables-only filegroup, then continuously synchronizing via a similar process—except that restoration of log files on the standby utilizes SQL Server 2005’s new piecemeal restore feature to include a FILEGROUP clause to the RESTORE LOG commands. Consider this example: 

RESTORE LOG StandbyDatabase
FILEGROUP = 'PRIMARY'
FROM DISK = 'E:PrimaryDatabase_LogBackup_20090301090005.lbak'
WITH NORECOVERY

Using the FILEGROUP clause directs the RESTORE command to ignore entries in the transaction log that belong to the dedicated nonclustered index filegroup, only restoring log entries that relate to tables (ignoring log entries that relate to nonclustered indexes).

An advantage from combining this technique with log shipping and piecemeal restore is that initialization of the log shipping standby database is faster because a smaller tables-only filegroup backup only needs to be copied from the log shipping primary server to the standby server and restored to the standby database. Unfortunately, there’s no way to eliminate index-related transaction log entries from the log backup files, so this technique does not solve the widespread problem of routine index maintenance breaking log shipping systems.

On the other hand, it might be considered a disadvantage that this technique complicates the process of bringing the standby database online in the event of a failover requirement, as indexes also need to be rebuilt.

It’s important to point out that it’s also possible to continue using regular log shipping (without piecemeal filegroup level restore) while still taking advantage of the disk space, time, and energy savings from switching regular daily full database backups to tables-only filegroup backups. Initializing log shipping in this scenario still requires a full backup of the primary database, but this is still possible because full database backups can still be taken—irrespective of whether tables and indexes have been separated into separate filegroups or not.

In this mode of regular backups and log shipping, you can continue to take regular tables-only filegroup backups of the primary database while still fully restoring transaction log backups against a standby database that has been initialized from a full backup. If log shipping ever breaks, take an ad hoc full backup to re-initialize the log shipping standby database.

Summary

You have much to gain from eliminating index data from backups by the method described in this chapter. Reductions in disk space required to store backups, time taken to process routine backups, and also energy consumed in the process, are all real, tangible benefits.

As with many other technology choices, these advantages need to be weighed against the inconvenience of a slightly more complex and time-consuming restore process in the event that a database needs to be fully reconstructed from backup. In many cases, though, restoration of backups is only performed for ad hoc extraction of historical data, in which case using this technique makes the restoration process faster, allowing quicker recovery of data.

Another benefit is that the reduction in size of backup files from this technique also allows for more copies of historical backups to be maintained. Although this might not be of significant value with smaller databases, with larger databases it could make the difference between being able to store a second backup file rather than only a single full database backup.

Perhaps Microsoft might consider including a feature that allows backups to be taken of table data only, without requiring updates to the system schema, in a future release of SQL Server. Such a feature might even extend to filtering log backups to contain only table-related entries so that log shipping standby servers can be kept in a tables-only state without needing to copy all transaction log entries (including unnecessary index entries) during log shipping.

Although some might consider the updates to system catalogs or increased time required to restore a database as unacceptable, the benefits gained every day in disk space, backup processing time, and energy saved should outweigh these issues in many cases.

About the author

Greg Linwood is the Managing Director of MyDBA—a dedicated SQL Server support business—and also Australian Director of SQLskills, a specialist provider of SQL Server training. Greg has worked in a range of developer and DBA capacities with SQL Server since 1993, and was the first Australian to receive Microsoft’s SQL Server MVP award in 2003. He has gained significant expertise in SQL Server tuning, and most of his professional time is currently spent tuning customer SQL Server systems. Greg is passionate about building high-quality SQL Server community events in Australia, and is one of the founders of the Australian SQL Server User Group, leading the Melbourne SQL Server User Group.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset