Errors or inconsistencies in the data tier of a SharePoint farm environment can have dramatic effects on the performance of the farm as a whole. Proper execution of database maintenance results in a more stable and better-performing SharePoint experience for your users, often resulting in performance gains without the need for additional equipment or reconfiguration.
health analyzer rules
In previous versions of SharePoint, it was often necessary for either the SharePoint administrator or SQL DBA to perform index defragmentation and/or statistics maintenance. Health Analyzer rules were added (starting with SharePoint 2010) that addressed both defragmentation and statistics maintenance, removing these administrative tasks as regular maintenance items.
A Health Analyzer rules definition can be found in Central Administration in the Monitoring → Health → Review Rule Definitions menu. Within the Performance section, you see three rules that are all enabled and set to repair their related issues on a daily basis (see Table 1).
TABLE 1 Health rules for indexing and statistics
| Health Rule | Schedule | Enabled | Repair Automatically |
| Databases used by SharePoint have fragmented indices | Daily | Yes | Yes |
| Databases used by SharePoint have outdated index statistics | Daily | Yes | Yes |
| Search: One or more crawl databases may have fragmented indices | Daily | Yes | Yes |
Checking database consistency using DBCC CheCKDB
Running DBCC CHECKDB from SSMS executes a series of actions that check the logical and physical integrity of a selected database:
- Verifies the allocation structures in the database (equivalent to DBCC CHECKALLOC)
- Checks every table and view in the database to verify their logical and physical integrity (equivalent to DBCC CHECKTABLE)
- Verifies the consistency of the metadata in the database (equivalent to DBCC CHECKCATALOG)
“ AVOID RUNNING DBCC CHECKDB DURING PRODUCTION HOURS”
DBCC CHECKDB can consume a lot of memory, I/O, and CPU resources; it is best to not run it during production hours. If you need to run this check against a production database that you suspect may be corrupt, you can back up the database, restore it to a different server, and then run the check there.
executing a database consistency check
Performing a consistency check on a SharePoint database is done from SSMS, as follows:
1. Open SSMS.
2. Select the database that you intend to check for consistency; then right-click its name and select New Query.
3. Within the query window, type DBCC CHECKDB (see Figure 1) and select the Execute button.
FIGURE 1 DBCC CHECKDB (Execute).
4. Optionally, you can include the WITH PHYSICAL_ONLY option to reduce CPU and memory usage on your system.
5. The database check runs and its output begins to appear in the Messages window (see Figure 2).
FIGURE 2 DBCC results
.6. If everything is good in the database, you will see 0 allocation errors and 0 consistency errors (see Figure 3).
FIGURE 3 DBCC results (no errors).
