Share via

  • Article

SQL Q & ARemoving Index Fragmentation, Synchronizing vs. Synchronized, and More

Paul S. Randal

QI'm confused about how removing index fragmentation affects statistics. I've heard that sometimes I should rebuild my statistics after rebuilding an index and sometimes I shouldn't, and that rebuilding a clustered index will affect all the other indexes too. Can you shed some light on this please, as I want to make sure I'm not inadvertently making performance worse?

AThis certainly is an issue that causes a lot of confusion, but you are right that a comprehensive database maintenance strategy includes removing index fragmentation and updating statistics. I'm not going to go into the details of exactly when and why to remove index fragmentation—for that, see the August 2008 article "Top Tips for Effective Database Maintenance." Instead, I'll assume you know which indexes to work with and I'll just focus on how things work.

The first part of the confusion is around which fragmentation removal operations affect statistics. Rebuilding an index (using ALTER INDEX … REBUILD, DBCC DBREINDEX, or CREATE INDEX … WITH DROP_EXISTING) does update statistics with the equivalent of a full scan, but reorganizing an index (using ALTER INDEX … REORGANIZE or DBCC INDEXDEFRAG) does not update statistics at all, even though both remove index fragmentation.

The reason that rebuilding does update statistics and reorganizing does not has to do with the algorithms used for these operations. An index rebuild operation has a complete view of the index and therefore can update the statistics correctly. A reorganize operation, however, only operates on a few pages of the index at a time and cannot correctly update the statistics for the whole index.

The second part of the confusion is about which statistics are updated when an index is rebuilt. There are two general kinds of statistics for a table—those for the table's indexed columns and those for the non-indexed columns. An index rebuild operation only updates the statistics for the index being rebuilt. The statistics for non-indexed columns must be updated manually in the maintenance plan. Furthermore, as I mentioned in the "Top Tips for Effective Database Maintenance" article, you must be careful not to manually update index statistics after rebuilding the index, as the manual update may use a sample rate of less than 100%, whereas the index rebuild will use the equivalent of a full-scan (100%) sample. In other words, this could lead to overwriting a full-scan statistic with a sampled statistic.

The last part of the confusion is around what effect rebuilding an index has on other indexes. The answer is always that rebuilding an index only affects that particular index and its statistics. The exception is for a non-unique clustered index in SQL Server 2000, where rebuilding such an index will also cause all non-clustered indexes on the table to be rebuilt. This has been fixed from SQL Server 2005 onwards. You can find more information on this point in my blog post at "Indexes from Every Angle."

To summarize, your index and statistics maintenance should do the following:

  • Rebuild or reorganize your indexes to remove fragmentation
  • Update index statistics for those indexes that were not rebuilt
  • Update non-index statistics

QI've been experimenting with SQL Server 2008 and I've found some weird behavior. It seems that if I enable data compression in the production database and then backup the database and try to restore it on a Standard Edition instance, the restore fails! Is this expected behavior? If so, is this limited to data compression or are any other features affected? And why doesn't the restore fail immediately instead of seeming to do the entire restore before failing?

AThe behavior that you're seeing is by design. Most "Enterprise-only" features just limit the SQL Server Editions with which you can make use of the feature to Enterprise Edition, Enterprise Evaluation Edition, and Developer Edition. There are some features, however, that also limit which SQL Server Editions can restore a backup of a database containing the feature. You've found one of the SQL Server 2008 features that has this restriction—data compression.

The behavior is actually not new for SQL Server 2008. In SQL Server 2005, if a database contains any partitioned tables or indexes (explicitly using the partitioning feature), then a backup of that database can only be restored using an Edition in the list I just mentioned. There are two issues with this, though, in SQL Server 2005. Firstly, it can be hard to tell whether there is any partitioning in a database, and secondly, the restore doesn't fail until just before it would have completed.

This means you don't find out that your restore is going to fail until you've waited through the entire (potentially long-running) restore operation. This is because the database isn't transactionally consistent until the recovery part of restore has completed—and so an operation during recovery might add or remove partitioning. Encountering this situation can be really annoying in a disaster recovery situation where the only server available to quickly get the database restored happens to be Standard Edition.

In SQL Server 2008, the list of "Enterprise-only" features with this behavior has grown to four: data compression, change data capture, transparent data encryption, and partitioning. This means that more people are going to encounter this problem. For this reason, a new DMV was added, sys.dm_db_persisted_sku_features, which allows a DBA to easily check whether any of these features are enabled in a database.

For instance, in a database where a table has had data compression enabled, running the DMV will give the following results:

SELECT * FROM sys.dm_db_persisted_sku_features; GO feature_name feature_id -------------- ----------- Compression 100

However, SQL Server 2008 does not address the issue with the restore operation having to almost complete before the DBA is informed that the database cannot actually be restored. In fact, this issue is unlikely to be addressed given the nature of how restore works—see the SQL Q&A column from the October 2008 issue for more details.

If it's possible that a database needs to be restored on Standard Edition (or a lower edition), it's imperative that the DBA either disallows these features or uses the DMV regularly to avoid getting a nasty surprise when trying to restore in a critical situation.

QI've implemented synchronous database mirroring and was under the impression that this meant the mirror database is always synchronized with the principal database. However, I've occasionally seen the mirror state become SYNCHRONIZING instead of SYNCHRONIZED, as well as when we first set up a mirror. Why does this happen?

ABefore I answer the question, I'll define the SYNCHRONIZED and SYNCHRONIZING database mirroring states. In a nutshell, database mirroring works by continually sending physical transaction log records between the SQL Server instance hosting the principal database and the instance hosting the mirror database. If there are no transaction log records waiting on the principal to be sent to the mirror, the mirroring state is SYNCHRONIZED (in other words, the two databases are in sync). If there are transaction log records that have not yet been sent to the mirror (the mirror database is "behind" the principal), the mirroring state is SYNCHRONIZING.

The method for initializing a database mirror is to take a full database backup, plus at least one transaction log backup, and restore them (using WITH NO_RECOVERY) on the mirror. Then when mirroring is enabled, the mirroring state is initially SYNCHRONIZING. This is because there may have been some transaction activity on the principal since the last log backup that was restored on the mirror. This is very likely for a database with a constant workload.

The trick to minimizing the time in which the mirroring state stays SYNCHRONIZING is to try to make the mirror database as up-to-date as possible using log backups. However, for a database with a constant workload, this may be very difficult and so the mirroring state will initially be SYNCHRONIZING for some time. Once the mirror catches up, the mirroring state will change to SYNCHRONIZED.

After that point, it is possible for the mirror database to again fall behind the principal, in which case the mirroring state will drop back to SYNCHRONIZING. One cause of this could be if the mirror and principal are unable to communicate for a period of time. Or it could happen if the principal is generating the transaction log faster than the log can be sent to the mirror. In either case, and depending on how database mirroring is configured, the principal database may continue to process transactions and a queue of transaction log records will build up (called the SEND queue). This must be sent to the mirror in order for it to catch up. Until the mirror database has caught up, the mirroring state will remain SYNCHRONIZING.

If the network link is unreliable between the principal and mirror, you may see the mirroring state switching back-and-forth between SYNCHRONIZING and SYNCHRONIZED. You can find a full discussion of these states in the whitepaper Database Mirroring in SQL Server 2005.

QWe have synchronous database mirroring set up for our main application database and our monitoring shows that the redo queue on the mirror is usually near-zero. We'd like to use the mirror database for reporting and consistency checking so we can make better use of the redundant hardware. We know this is possible, but are there any drawbacks to doing this that we should know of?

AThis is a pretty common practice, but there are a few drawbacks that you may need to watch out for.

The first issue is licensing. The license for the SQL Server instance hosting the mirror database is free if the instance is only used for that purpose. As soon as you create a database snapshot on the mirror database or do anything else with that SQL Server instance, you must purchase a license.

As far as consistency checking is concerned, running them on the mirror database (through a database snapshot) doesn't give you any guarantees about the consistency of the principal database. Only transaction log records are mirrored between the databases, so if the I/O subsystem corrupts a page in the principal database, that corruption will not be mirrored on the mirror database. This means that a consistency check of the mirror database would not see that corrupt page on the principal database.

Reporting is a good use of a database mirror. The first issue that people run into is how to refresh the database snapshot that the reports are running against. A database snapshot cannot be refreshed so a new snapshot must be created and the reporting application must connect to the new snapshot. This requires extra logic to be built into the reporting application. The second issue is determining how the reporting application should behave in the event of a mirroring failover—should it remain connected to the new principal or should it move to the new mirror? Going into details on this is beyond the scope of this article.

With any extra use of the mirror database, the main concern is the potential for causing performance problems on the mirror. Creating a database snapshot adds extra I/O load in that the first time a page changes in the mirror database, it must be pushed into the database snapshot. Also, any workload you then place on the database snapshot increases the I/O load on the mirror database—as many of the pages read from the database snapshot will really be read from the mirror database, as they will not have changed since the snapshot was created.

This extra I/O load on the mirror database can slow down the replaying of transaction log records and in turn lead to a backlog. This backlog is called the REDO queue and is the amount of transaction log that must be replayed before the mirror database can come online after a failover. The larger the I/O load on the mirror database from the database snapshot, the larger the REDO queue will potentially grow and the longer the database will be unavailable after a failover.

You did mention that your REDO queue length is usually near-zero so this may not be an issue for you, but it is definitely something to watch out for so you don't end up compromising the database's availability in return for the increased ability to run reports.

Paul S. Randal is the Managing Director of SQLskills.com and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Paul wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Paul is an expert on disaster recovery, high-availability, and database maintenance and is a regular presenter at conferences around the world. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at Twitter.com/PaulRandal.