Maintaining the Lync SQL Databases
An important aspect of the health of any Lync
deployment is maintaining the Lync SQL databases. To keep Lync Server
operating smoothly and with optimal performance, regular maintenance
should be performed on each SQL Server database. Such maintenance tasks
include rebuilding indexes, checking database integrity, updating index
statistics, and performing internal consistency checks and backups.
Database maintenance tasks can be performed either by executing
Transact-SQL commands or by running the Database Maintenance Wizard.
This section provides information and
recommendations for maintaining the databases that store Lync Server
data and configurations. Also, details are provided on how to automate
and schedule the major maintenance tasks by creating database
maintenance plans through SQL Server Database Maintenance Wizard.
Checking and Repairing Database Integrity
DBCC CHECKDB is a Transact-SQL command that is frequently used for checking the logical and physical integrity of databases. Essentially, DBCC CHECKDB is a superset command that actually runs three checks (CHECKALLOC, CHECKTABLE, and CHECKCATALOG) by issuing a single command.
The following are some recommendations for using DBCC CHECKDB to check and repair SQL database integrity:
• Always ensure that recent backups are on hand before running the command.
• Generally it is better to run the DBCC CHECKDB
superset command than to execute the individual operations, because
this will serve to identify the majority of the errors and is generally
safe to run in a production environment.
• After DBCC CHECKDB has been run, the command can be run again with the REPAIR
argument to repair reported errors. However, consideration should also
be given to restoring the database from backup instead, because the REPAIR options should be considered a last resort.
• DBCC CHECKDB can require
a considerable amount of time to run against large databases, and it
performs schema locks that prevent metadata changes. Therefore, it is
highly recommended to run the command during nonproduction hours.
• For large databases, the command can be run with the PHYSICAL_ONLY
option, which will limit checking to the integrity of the physical
structure of the page and record headers, along with the allocation
consistency of the database. For these larger databases, it is
therefore recommended to run the command with the PHYSICAL_ONLY option on a more frequent basis, and perform a full run of DBCC CHECKDB only on a periodic basis.
Monitoring and Reducing Fragmentation
Although indexes can speed up the execution
of queries, there is also some overhead associated with them. Indexes
consume extra disk space and require some time to be updated whenever
any data is updated, deleted, or inserted in a table. When indexes are
first built, little or no fragmentation is present. Over time, as data
is inserted, updated, and deleted, fragmentation levels on the
underlying indexes can begin to increase.
When a data page is completely full and
further data must be added to it, a page split occurs. To make room for
the new data, SQL Server creates another data page somewhere else in
the database (not necessarily in a contiguous location) and moves some
of the data from the full page to the newly created one.
The effect of this is that the blocks of data
are logically linear but physically fragmented. Therefore, when
searching for data, the database engine is forced to jump from one page
to somewhere else in the database looking for the next page it needs,
instead of just sequentially moving to the next physical page. The end
result is performance degradation and inefficient space utilization.
The fragmentation level of an index
is the percentage of blocks that are logically linear and physically
nonlinear. In SQL Server versions starting with SQL Server 2005, the sys.dm_db_index_physical_stats transact-SQL command can be used to monitor this, with the avg_fragmentation_in_percent column showing the fragmentation level. The value for avg_fragmentation_in_percent
should be as close to zero as possible for maximum performance.
However, up to 10% might be acceptable without noticeable degradation.
Shrinking Data Files
In SQL Server 2005 and above, free
space can be reclaimed from the end of data files to remove unused
pages and recover disk space. However, shrinking data files is not
recommended unless the database has lost at least half of its data.
This typically occurs after an activity has been performed that creates
whitespace in the database, such as moving a large amount of data from
one database to another, or deleting a large amount of data. Shrinking
Lync Server databases is generally not recommended, since there are
typically not enough deletions to cause a significant amount of free
space.
