DBCC IndexDefrag

Being day 16 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC INDEXDEFRAG.

Description:

DBCC INDEXDEFRAG was used for to defragment indexes on SQL Server 2000, the same thing can be done with Alter Index on newer versions of SQL Server..

DBCC INDEXDEFRAG Syntax:

dbcc indexdefrag
(
    { 'database_name' | database_id | 0 }
    , { 'table_name' | table_id | 'view_name' | view_id }
    [ , { 'index_name' | index_id }
    [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

For more info on index defragmentation, check out the Database Health Reports application. Below is a sample screenshot of the Index Fragmentation report.

Index_Fragmentation_Report

Notes:

Note that DBCC INDEXDEFRAG was deprecated and replaced by ALTER INDEX.

For more information see TSQL Wiki DBCC indexdefrag.

DBCC Command month at SteveStedman.com is almost as much fun as conveyor belt sushi.

Making Progress on the Server Health Re-write

Originally I implemented the  Database Health Reports as a series of SQL Server Reporting Services reports, which was very handy and easy to create, but hard to distribute in a way that they could be shared and customized to the needs of a project like the Database Health Reports should be.

Here is what I have so far, and I think that I am about a month and a half away from a Beta release, lets call it September 1st for the beta.  If you are interested in the beta, please register on this website, all registered users will be notified when the beta becomes available.

Here are a couple of sneak peak preview screenshots of the database health application.  This application includes reports on Backups, CPU by Query, Connections, Duplicate Indexes, Index Fragmentation, Long Running Queries, Plan Cache, Stored Procedure Performance, Statistics, Unused Indexes and much much more.

Here is a preview of the statistics report, and the Statistics Advisor.

So if you are interested in getting in on the Beta of the new Database Health Reports application, just register on this site and you will be notified when the beta becomes available, about the first of September.

SQL Server Health Reports — Working on Version 2

Late last year and early this year, I created a collection of report in SSRS that I called SQL Server Health reports.

Some of the things that the Server Health report evaluates are the following

But…. I have found it very challenging to distribute the SSRS reports in a way that they are easy to use for anyone anywhere with any SQL Server database environment.

So… The solution is to take the work done in the reports and port it into an application that can replace this collection of SSRS reports.  I started on this project today, and think that over the next couple of months, I should be able to replace the existing SQL Server Health reports in SSRS with an application that hosts all the queries and makes it easier than it is now.

I am just getting started, but over the next couple of months I will be releasing the SQL Server Health reports as an application.

Abstracts submitted to SQL Saturday

I just submitted abstracts to SQL Saturday  #114 in Vancouver and #108 in Redmond.

The Redmond SQL Saturday appears to be the more popular of the two.  Who knows if my session will get accepted, but I am giving it a shot.

 

Using SSRS reports to analyze SQL Server health

Assessing the health of your SQL Server with Several SQL Server Report Server reports designed for DBA’s and database developers to diagnose, and locate common pitfalls of SQL Server.  This includes reports for Duplicate Indexes, Index Fragmentation, Big Indexes, Backup Status – backup log, Big One Time Use Queries, Queries Needing Parameters and more.

 

Index Fragmentation and SHRINKDATABASE

Is it a good idea to run DBCC SHRINKDATABASE regularly?

Download the sample file ShrinkSample.

This article and samples apply to SQL Server 2005, 2008, and 2008R2.

This really depends on a number of factors, but generally the answer is NO, it is not a good idea to run DBCC SHRINKDATABASE regularly.

For the purpose of this article, I am going to assume a couple of things:

  1. You are concerned about database performance.
  2. Over time your database is growing (which is probably why are are concerned about performance).
  3. You want to do your best to improve the overall health of the database, not just fixing one thing.

Most DBAs who are not familiar with the issues around index fragmenation just set up maintenance plans, and see SHRINKDATABASE as a nice maintenance plan to add.  It must be good since it is going to make the database take up less space than it does now.  This is the problem, although SHRINKDATABASE may give you a small file, the amount of index fragmentation is massive.

I have seen maintenance plans that first reorganize or rebuild all of the indexes, then call DBCC SHRINKDATABASE.  This should be translated as the first reorganize all of the indexes, then they scramble them again.

Here is an example showing some new tables, with a clustered index on the largest, that are then fragmented, then REORGANIZED, then SHRINKDATABASE.  You might find the results interesting.

 

To start with, I am going to create a new database, with two simple tables. One table uses a CHAR column and the other users VARCHAR. The reason for the CHAR column is to just take up extra space for the purpose of the demonstration. Each table will be filled with 10,000 rows holding text that is randomly generated with the NEWID() function and then cast to be a VARCHAR. For the purpose of demonstrating, that appeared to be a good way to fill up the table with some characters.

USE MASTER;
GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'IndexTest')
DROP DATABASE [IndexTest];
CREATE DATABASE [IndexTest];
USE [IndexTest]

CREATE TABLE [Table1] (id INT IDENTITY,  name CHAR (6000));
SET nocount ON;
GO
INSERT INTO [Table1] (name) SELECT CAST(Newid() AS VARCHAR(100));
GO 10000

CREATE TABLE [Table2] (id INT IDENTITY,  name VARCHAR(6000));
CREATE CLUSTERED INDEX [Table2Cluster] ON [Table2] ([id] ASC);
GO
INSERT INTO [Table2] (name) SELECT CAST(Newid() AS VARCHAR(100));
GO 10000

 

Now that we have some tables, lets take a look at the size of the database and the fragmentation on Table2. We will run thee following two queries before after each of the commands following this.

DBCC showcontig('[Table2]');
SELECT CAST(CONVERT(DECIMAL(12,2),Round(t1.size/128.000,2)) AS VARCHAR(10)) AS [FILESIZEINMB] ,
CAST(CONVERT(DECIMAL(12,2),Round(Fileproperty(t1.name,'SpaceUsed')/128.000,2)) AS VARCHAR(10)) AS [SPACEUSEDINMB],
CAST(CONVERT(DECIMAL(12,2),Round((t1.size-Fileproperty(t1.name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10)) AS [FREESPACEINMB],
CAST(t1.name AS VARCHAR(16)) AS [name]
FROM dbo.sysfiles t1;

 

The results of the two checks are shown below. You can see that the “Logical scan fragmentation” is 2.9% which is very good. You can also see that the data file is taking 80.0mb of disk space. Remember these numbers as they will be changing later.

Next we drop Table1 which will free up space at the beginning of the datafile. This is done to force Table2 to be moved when we run DBCC SHRINKDATABASE later.

DROP TABLE [Table1];

The checks after dropping the table show that there is no change to the Table2 fragmentation, but free space in the datafile is now 78.38mb.

 

Next we shrink the database, then run the same 2 queries to check the size and the fragmentation.

DBCC shrinkdatabase (‘IndexTest’, 5);

The results show good news and bad news. The good news is that the filesize has been reduced from 80mb to just 1.88mb. The bad news shows that fragmentation is now 98.55%, which indicates that the index is not going to perform as optimal as it should. You can see the shrinkdatabase has succeeded just as expected, and if you didn’t know where to look, you wouldn’t know that the clustered index on Table2 is now very fragmented.

Imagine running DBCC SHRINKDATABASE every night on a large database with hundreds or thousands of tables. The effect would be that very quickly every table with a clustered index would end up at close to 100% fragmented. These heavily fragmented indexes will slow down queries and seriously impact performance.

To fix this fragmentation, you must REORGANIZE or REBUILD the index.
The standard recommendation is to REORGANIZE if the fragmentation is between 5% and 30%, and to REBUILD if it is more than 30% fragmented. This is a good recommendation if you are running on SQL Server Enterprise Edition with the ability to REBUILD indexes online, but with standard edition this is not available so the REORGANIZE will do the job.

ALTER INDEX table2cluster ON [IndexTest].[dbo].[Table2] reorganize;

Once we run this our check script shows that after the REORGANIZE the fragmentation has been reduced to 10.14%, which is a big improvement over the 98.55% it was at earlier.

Next we try the REBUILD.

ALTER INDEX table2cluster ON [IndexTest].[dbo].[Table2] rebuild;

Which reduces the fragmenation to 4.17%, but it increases the filesize to 34.88mb. This effectively is undoing a big part of the original DBCC SHRINKDATABASE.

Notes
You can REBUILD or REORGANIZE all of your indexes on the system at one time, but this is not recommended. The REBUILD or REORGANIZE of all of the indexes will impact performance while it is running, and it may cause excessive transaction logs to be generated.

After doing a REORGANIZE of an index, it is suggested that statistics be updated immediately after the REORGANIZE.

Summary
It is my opinion that DBCC SHRINKDATABASE should never be run on a production system that is growing over time. It may be necessary to shrink the database if a huge amount of data has been removed from the database, but there are other options besides shink in this case. After any DBCC SHRINKDATABASE, if you chose to use it, you will need to REBUILD or REORGANIZE all of your indexes.

Even if you never use DBCC SHRINKDATABASE your indexes will end up getting fragmented over time. My suggestion is to create a custom Maintenance Plan which finds the most fragmented indexes and REBUILD or REORGANIZE them over time. You could for instance create a stored procedure that finds and REORGANIZES the 4 or 5 indexes that are the most fragmented. This could be run a couple times per night during a slow time allowing your system to automatically find and fix any indexes that are too fragmented.