The problem
SQL Server tables and indexes tend to become fragmented over time because of data modifications. In large I/O operations, application performance using these fragmented indexes and tables in SQL Server can be adversely affected.
When you are among a small number of DBAs who are in charge of tens of SQL Server instances and hundreds of databases, the situation can lead to a loss of control, or understanding, of the activity types in the diverse databases. In that situation, it's important to check for problematic fragmented tables and indexes regularly (unless you defragment all tables recurrently). That task often falls to the DBA because, often, development teams are not aware of the physical representation of the data.
This article provides an example code for collecting information on all fragmented indexes into one central database.
Possible commands
To find fragmented tables, here are two commands you can run:
DBCC SHOWCONTIG, for SQL Server 2000 or SQL Server 2005.
Select …. From sys.dm_db_index_physical_stats, in SQL Server 2005.
These commands need to be executed on all servers in all the databases for all the indexes. There are a few options for doing so. For example:
- Run a DTS/SSIS package.
- Write a program that loops on the server list and collects the information.
- Run a SQL Server T-SQL batch job.
In order to run the utility in any version of SQL Server without asking for the version first, I decided to execute the DBCC SHOWCONTIG command. For simplicity, I created a SQL Server job using only T-SQL and linked servers.
Note: I intend to replace the DBCC SHOWCONTIG with the sys.dm_db_index_physical_stats when all my servers are upgraded to SQL Server 2005, to follow a best practice.
The environment
I have one central development server hosting a SQL Server instance where I created a database for DBA use (DBA_DB) and linked servers to all my production servers. In the DBA_DB database, I have a table with the list of production servers with a column named "IsActive" indicating if the server should be checked (this way I can exclude some servers from being checked):
Click here to download the above script.
I have a few programs looping on the list of "live" servers (IsActive = 1) and executing common tasks. These tasks include checking databases that were not backed up for the last X days and performing periodical performance traces, among others. Now, I would like to add a program that will check for fragmented indexes. I enabled the xp_cmdshell stored procedure in the central database, as I am using it in my program.
The strategy
My strategy is to use a simple stored procedure. I am collecting the index fragmentation information from all servers and databases into a constant table I created in my DBA_DB database:
Click here to download the above script.
The stored procedure looks like this:
Click here to view and download the complete stored procedure.
The stored procedure results
The fragmentation data is collected into the t_FragmentedTables table. What can be done with the collected data? Here are a few examples:
- Send an email with the fragmented indexes information.
- Open tickets regarding the fragmentation -- assuming you have a ticketing system and you can interact with it.
Example (send email with fragmented tables -- up to 8,000 characters in the message body):
Click here to download the above script.
Performance:
Just to give you an idea about its performance, I ran my stored procedure in a test environment when activity was very low on the databases:
Windows 2003 Standard Edition SP1
2* Dual Core CPU 3.06 GHz
4 GB RAM, maximum of 2 GB configured for SQL Server
SQL Server 2000 SP3
Total size of user databases = 46 GB
Total of 2,170 user tables
Total of 8,154 indexes
Total of 5,864 fragmented indexes
Execution ended successfully after exactly three hours.
Conclusion:
If you set up an environment to include a SQL Server instance with
- A special "DBA" database (for example: DBA_DB) that includes:
- a table with the server list (t_ServerList) as described in the article.
- a table to capture fragmentation data as described in the article.
- Linked servers configured for each "active" server in the list table (t_ServerList).
Then you can just copy and paste the stored procedure code and execute it. Later, you can analyze the results in the local table with the fragmentation details and implement alerts/reports/email according to your needs. You can also automatically defragment the fragmented indexes using one of the methods described in Microsoft Books Online.
Note: The stored procedure used in my example should run when activity is low (for example, once a month during the weekend).
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
Copyright 2007 TechTarget
|
|
