Checking Your Database Fragmentation Level

Question

Post reply

Checking Your Database Fragmentation Level

Nicholas Cain

SSC-Dedicated

Points: 33009
More actions
June 3, 2004 at 10:20 am

#162027

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column

Shamless self promotion - read my blog http://sirsql.net

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Bikeman Ten Centuries Points: 1207 More actions · Answer 1

Hi Nicholas,

Nice article, I enjoyed reading it.

BTW, what is the benifit of using the dts vs scheduling these steps from a job? I am assuming both will be done by someone with dba rights.

Thanks again

Preston Burroughs SSC Enthusiast Points: 124 More actions · Answer 2

This was a great article. I tried implementing, but have been having problems running the stored procedure USP_DBshowcontig_single_db. #table# name error. I have been having a provlem with this part of the stored procedure.

set @inserttable = ('INSERT INTO ADMINDB.DBO.INDEX_INFO_.'+@NAME+ 'ObjectName,

ObjectId,

IndexName,

IndexId,

Lvl,

CountPages,

CountRows,

MinRecSize,

MaxRecSize,

AvgRecSize,

ForRecCount,

Extents,

ExtentSwitches,

AvgFreeBytes,

AvgPageDensity,

ScanDensity,

BestCount,

ActualCount,

LogicalFrag,

ExtentFrag'

select ObjectName,

ObjectId,

IndexName,

IndexId,

Lvl,

CountPages,

CountRows,

MinRecSize,

MaxRecSize,

AvgRecSize,

ForRecCount,

Extents,

ExtentSwitches,

AvgFreeBytes,

AvgPageDensity,

ScanDensity,

BestCount,

ActualCount,

LogicalFrag,

ExtentFrag

FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

Christi Nicholson SSC Journeyman Points: 92 More actions · Answer 3

I'm also have problems with this stored procedure. Have you had any luck with it?

Preston Burroughs SSC Enthusiast Points: 124 More actions · Answer 4

Preston Burroughs

SSC Enthusiast

Points: 124

June 20, 2005 at 9:17 am

#566767

Stll not having any luck.

Dave Henderson SSC Rookie Points: 47 More actions · Answer 5

I got it working by adding a 5th single quote to the line:

FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

Dave H

hot2use SSCrazy Points: 2915 More actions · Answer 6

Hiyas.

Had the same problem with the quotes ... noticed in QA that everything was red so I added a fifth quote.

Anyway. Got a small addition here for the same sproc USP_DBshowcontig_single_db :

set @sql = 'SELECT TABLE_NAME = ' + @dbname + '..SYSUSERS.NAME' + ' + ''.'' + ' + '' + @dbname + '..SYSOBJECTS.NAME FROM ' + @dbname + '..SYSOBJECTS JOIN ' + @dbname + '..SYSUSERS ON ' + @dbname + '..SYSOBJECTS.uid = ' + @dbname + '..SYSUSERS.uid WHERE XTYPE =' + '''' + 'U' + ''''

It adds the owner name of the table to TABLE_NAME, which allows you to run the script on databases which have user tables not created by sa.

My piece replaces the line:

set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''

Good luck.

hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

kumarkaji@yahoo.com Old Hand Points: 303 More actions · Answer 7

Hi,

I am very new to these things.. can any of you guys tell me how to identify ideal fragmentation level...and how do i identify it..

Thanks in advance..

Kumar

Tatsu SSCertifiable Points: 7824 More actions · Answer 8

This is fantastic. I have a client who is having fragmentation problems in a heavily used medical records database and this could be just what I need to measure just how quickly the database is becoming fragmented so we can schedule the maintenance plans appropriately.

Thanks a lot!

[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog

hot2use SSCrazy Points: 2915 More actions · Answer 9

I'm not a fully fledged DBA, but looking at the scripts I'd assume that a scan density beloew 75 percent is bad.

Ken England has a good book on "Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook" which I'd recommend (ISBN# 1-55558-241-9). And then searching for DBCC SHOWCONTIG in Google should give you some more information at what to look for.

Regards

hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 10

No luck getting this to output. Eventually got everything to run, but email output simply reads: THETEXT

Stijn977 SSC Eights! Points: 962 More actions · Answer 11

Hi,

I am using a similar approach (running a SHOWCONTIG and use the output to decide which indexes need to be defragmented). However, i am always running into rapid growing log files once i start reindexing or DBCC INDEXDEFRAG.

Does anyone know a "safe" way on how to defragment those fragmentated ones without risking blowing up the logs?

Greets,

Stijn

noeld SSC Guru Points: 96590 More actions · Answer 12

There are two schools in this matter. Pick what suit you best

1. Run parallel Tlog backups at the same time the reindexing is happening eg. Run a Tlog backup every 2 min while the reindexing is going on

2. Make a Tlog Backup, Switch the recovery model to Bulk_logged, perform the Reindexing, switch back to Full and when performing the next TLog backup do it on a drive that have plenty of space

Cheers,

* Noel

Stijn977 SSC Eights! Points: 962 More actions · Answer 13

Thanks ... i'll see how it goes when i up the backup frequency when i'm reindexing.

Much appreciated,

Stijn

nmargol SSC Enthusiast Points: 134 More actions · Answer 14

Still can't get usp_dbshowcontig_single_db to work. I added the fifth quote but am still getting this error message: Invalid object name '#tablename'

Has anyone else had this error message and solved the problem?