RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Keep Your SQL Server Indexes Fragmentation Free : Page 2

Routine maintenance can keep index fragmentation to a minimum—with some help from an index-defragmenting script.


The Defragmenting Code

The index-defragmenting code is composed of one script in two parts. The first part scans the indexes to find tables that you need to defragment. The second part, which contains the first part, generates all the statistics for the specified database's tables and indexes, and e-mails them to an administrator.

Figure 1. The Initial Run: This first part of the script reports the tables that have fragmented indexes.

You run the first part to locate the tables that require defragmenting. Choose a time when the system is mostly idle. To run the script in Management Studio (as shown in Figure 1), do the following:

  1. Choose Open from the File menu.
  2. Select File and locate the script.
  3. From the Database dropdown control, choose the database you want to check. Alternatively, you can add a USE mydatabase statement at the top of the script.
  4. Press F5 or click the Execute button.

Figure 2 shows the retrieved statistics from the resulting defrag report (against a local database). In addition, the script sends an e-mail containing the status and time of each occurrence to a specified recipient (see Figure 3). Be sure to update the script so that it sends the e-mail to an appropriate person within your organization.

When you're ready to defrag the indexes, you run the second part.

Figure 2. Defrag Statistics: The script generates a table named db_defrag for reporting the statistics on the specified database's tables and indexes.
Figure 3. E-mail to Admin: The script e-mails the specified administrator.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date