Decreasing the unused space of a SQL Server table

Tags:
SQL Server Indexes
SQL Server tables
How can I decrease the unused space of a SQL Server table which is indexed?

Answer Wiki

Thanks. We'll let you know when a new response is added.

In all OLTP environments, virtually all indexes will become fragmented over time. Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, and consequently, there will be more I/O required to satisfy each SELECT. And the more fragmented your data and indexes become, the slower your application will be, and the more space your data will take up. What can you do to address this? You can reindex them on a regular basis.

So What’s Available Out-of-the-Box?

On a basic level, you can use the Database Maintenance Wizard to perform reindexing, and create Maintenance Plans to do the job. This will work if you are prepared to accept its inherent limitations. Firstly, the reindexing, which is set up and done by the Maintenance Wizard, is indiscriminate. It will reindex everything, regardless of whether it needs it or not. Now if you have a large database with large tables and lots of indexes, this may be problematic, as it may take too long, certainly longer than your available maintenance window, to indiscriminately reindex the entire database. And that’s the problem – it’s all or nothing, you can’t segment your database tables in any way at all.

So what else can you do? You can write a script to reindex selected tables. This way you can segment your database and reduce the time spent during your maintenance window on reindexing. This time is something you will want to reduce to a minimum, as reindexing exclusively locks tables, preventing users from accessing it while the indexing is occurring. So you could, for example, reindex one-fifth of your tables each night per working day of the week, so all are done at least once a week. However, this is also indiscriminate – you will be reindexing tables whether their data and indexes are fragmented or not.

This is where selective reindexing is recommended. You need to be able to check your tables’ indexes and data fragmentation, retain the data, and then act on it, to reindex in a discriminate and deterministic way. Only by taking a systematic approach such as this, can you be assured that you are only reindexing the table data and indexes that actually need it. And only in this way can you minimize the amount of time taken to reindex. Reducing reindexing time is crucial, as during a full reindex, if you don’t want to negatively affect your users.

So How Do We Work Out What Needs to be Done?

We use the command

DBCC SHOWCONTIG()

One of the big advances between SQL Server 2000 and previous versions of SQL Server was in this simple, yet crucial command. DBCC SHOWCONTIG is the tool supplied with SQL Server to check how fragmented an index is. In previous versions of SQL Server (7.0 and earlier), this command would only output text. This is fine if the command is being used on a manual basis. However, for automation purposes, it creates serious problems. It means you need to cycle though each table and output to text file, then construct a cumbersome process for reading and interpreting the textual output in order to obtain the information you’re after.

SQL Server 2000 introduced a key clause to the DBCC SHOWCONTIG() command, namely WITH TABLERESULTS. This means you can run the command and capture the output straight into a table, rather than having to output to text file and include a layer of clumsy XP_CMDSHELL manipulation of text files.

This means that in SQL Server 2000, you can construct a procedure to cycle through your tables, running DBCC SHOWCONTIG on them, capturing the fragmentation information from the command on each into a table. You can then cycle through the results, conditionally taking defragmentation action on the indexes, depending on how fragmented they are. This is what the accompanying stored procedure does.

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

AS

/* T.Pullen

This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP. This SP was initially based on a code sample in SQL Server 2000
Books Online.

Must be run in the database to be defragmented.

*/

— Declare variables

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)

–check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)
BEGIN
PRINT ‘This procedure should not be run in system databases.’
RETURN
END

–begin Stage 1: checking fragmentation
— Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type =’U’
AND si.indid < 2
AND si.rows > 0

— Create the temporary table to hold fragmentation information
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

— Open the cursor
OPEN tables

— Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar

WHILE @@FETCH_STATUS = 0
BEGIN
— Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (‘ + @tableidchar + ‘) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
FETCH NEXT
FROM tables
INTO @tableidchar
END

— Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

— Report the ouput of showcontig for results checking
SELECT * FROM #fraglist

— Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0

— Write to output start time for information purposes
SELECT ‘Started defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE())

— Open the cursor
OPEN indexes

— Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON

SELECT @execstr = ‘DBCC DBREINDEX (‘ + “‘” +RTRIM(@objectowner) + ‘.’ + RTRIM(@tablename) + “‘” +
‘, ‘ + RTRIM(@indexname) + ‘) WITH NO_INFOMSGS’
SELECT ‘Now executing: ‘
SELECT(@execstr)
EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END

— Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

— Report on finish time for information purposes
SELECT ‘Finished defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE())

— Delete the temporary table
DROP TABLE #fraglist
GO

Usage

This stored procedure should be created in the master database, which will enable it to be run in any user database on a server.

It is run by calling it in a user database, and passing it a parameter (MAXFRAG). This is a percentage value. What this means is to defragment any indexes whose scan density fall below this value. For example, if you want to defragment any indexes who scan density is less than 95%:

USE pubs
GO

EXEC sp_deframent_indexes 95.00

Limitations

This procedure depends upon the measure scan density, but scan density is not a valid measure of fragmentation of indexes which span multiple files. If your indexes do span multiple files, you will need to modify this SP to fragment on the basis of another measure (e.g. Logical Frag). However, this kind of modification is beyond the scope of this article; if your indexes span multiple files, you’ll need to do more work.

How does it work and what does it do?

The stored procedure has two distinct parts.

Stage 1

In this part, the stored procedure checks index fragmentation by running the command:

DBCC SHOWCONTIG (‘tablename’) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

on every table in the database. The results of this command are stored in a pre-created temporary table, #fraglist. Here, we are utilizing the benefit of the WITH TABLERESULTS clause of DBCC SHOWCONTIG, and really this feature alone saves an inordinate amount of hassle and effort that were suffered in previous versions of the product to obtain the same result.

You should note that this stored procedure works in databases which contain tables that are not dbo-owned, as well as (the more common) dbo-owned tables. I discovered that my original version would not work in all situations when a software vendor supplied us with a new system whose database contained only non-dbo-owned tables. I set up this defragmenting procedure to run on it and was rudely awoken to the shortcomings in my original when its first run on the new system, and failed completely. This is actually an issue in the defragment section (Stage 2), where the table has to be referred to by name, whereas in Stage 1, the DBCC SHOWCONTIG command references the table by object_id.

Stage 2

Here, we use another cursor to loop through the #fraglist table and conditionally run:

DBCC DBREINDEX()

on tables whose Scan Density has fallen below the threshold specified by the parameter passed to the procedure. The results of this execution are printed into the output file after the contents of the table #fraglist, so that you can review the tables’ and indexes’ fragmentation, as shown above in the screen shot, and also review the action taking by reviewing the printed list of execution of DBCC DBREINDEX(). From these you can also deduce the duration of the reindexing of each index.

Why Not Use DBCC INDEXDEFRAG() to Reduce Blocking?

The answer is, use it if you want or need to! If your requirements are 24X7 operations and the exclusive table locks that DBCC DBREINDEX() take are unacceptable to your business, you can substitute it for DBCC DBREINDEX(). However, you will need to adapt the syntax as they are not the same (thanks, Microsoft!). If you do not know the difference, here is a brief summary: DBCC DBREINDEX() is a complete, through, bottom-up index recreation operation which will exclusively lock the table while it runs by necessity. DBCC INDEXDEFRAG() is a less complete, but online operation, which will attempt to improve your indexes’ condition as far as it can while not causing blocking and (hopefully) not disrupting OLTP. I must admit I have never used DBCC indexdefrag(), as my systems do not have strict 24X7 no-blocking requirements, fortunately, so I cannot vouch for is efficacy or otherwise. I have read that it is not as effective as a full DBCC DBREINDEX. However, it will surely be better than nothing, so if your database runs a global website which must never stop, as is common nowadays, you should adapt this stored procedure to use it instead.

Add it as a Scheduled Task

This is a relatively trivial task for a moderately experienced DBA, so I will not include code for it here. Add it as a stand-alone task or include it as a step in your existing maintenance job. Make sure you set the job-step or job to output to a text file so you can retain and look at the all-important output file.

Conclusion and Summary

Hopefully, this code and article will help you to achieve a better and more refined approach to your database server maintenance. Maintenance windows nowadays are precious commodities and should be kept to an absolute minimum in terms of both impact on users and duration whilst also preserving an efficient and well-maintained database server. Implementing this procedure as a scheduled task, with careful monitoring of its output, should help you, to some degree; achieve your ends more effectively.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following