Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Checking Your Database Fragmentation Level Expand / Collapse
Author
Message
Posted Thursday, June 03, 2004 10:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:01 PM
Points: 2,007, Visits: 6,065
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
Post #119100
Posted Monday, June 06, 2005 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 01, 2009 1:56 PM
Points: 125, Visits: 4

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

 

Post #188004
Posted Thursday, June 09, 2005 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2008 9:34 PM
Points: 22, Visits: 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))<> ''''

 

Post #189142
Posted Wednesday, June 15, 2005 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 18, Visits: 208
I'm also have problems with this stored procedure.  Have you had any luck with it?
Post #190950
Posted Monday, June 20, 2005 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2008 9:34 PM
Points: 22, Visits: 2
Stll not having any luck.
Post #192110
Posted Sunday, August 28, 2005 11:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 13, 2007 5:23 PM
Points: 37, Visits: 4

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

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

Dave H

Post #214778
Posted Friday, November 18, 2005 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #238038
Posted Friday, November 18, 2005 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:19 PM
Points: 166, Visits: 93

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

Post #238124
Posted Friday, November 18, 2005 8:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 292, Visits: 258

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!



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #238131
Posted Friday, November 18, 2005 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #238134
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse