SQLServerCentral / Discuss Content Posted by Nicholas Cain / Article Discussions / Article Discussions by Author / Checking Your Database Fragmentation Level / Latest Posts

RE: Checking Your Database Fragmentation Level

Jason Crider — Wed, 21 May 2008 13:08:25 GMT

It is possible to email using SMTP.Try [url=http://www.sqldev.net/xp/xpsmtp.htm]xpsmtp[/url].We are using it successfully.

RE: Checking Your Database Fragmentation Level

Aspurr — Fri, 09 May 2008 04:55:50 GMT

Hello Nicholas,I know this subject was discussed some time ago, but I am new to SQL and was wondering if you had enhanced the original script yet. I am in the process of trying to understand how it works and would value any new information about running on multiple databases. I was also wondering if it is possible to email using SMTP, when you do not have SQL configured to use a mapi applicaton (outlook e.t.c.)I am very new to becoming a SQL DB Admin. I have been around SQL for a while, but never got into the scripting sideAny help appreciated

RE: Checking Your Database Fragmentation Level

Nicholas Cain — Wed, 23 Nov 2005 05:28:00 GMT

Below is the correct code, looks like a typo in the article, somewhat embarassing, but no more than MS crashing SQL 2k5 and having to reboot in the NY launch event yesterday.-----------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)ASSET NOCOUNT ON DECLARE @tablename VARCHAR (128)DECLARE @dbname VARCHAR(20)DECLARE @sql VARCHAR(1000)DECLARE @inserttable VARCHAR(3200) -- Create the tableCREATE TABLE #DBFRAGMENT ( ObjectName VARCHAR (50), ObjectId INT, IndexName VARCHAR (100), 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) create table #tablename (table_name varchar(400)) --DECLARE DB CursorDECLARE databases CURSOR FOR SELECT NAME FROM MASTER.DBO.SYSDATABASES WHERE NAME = @NAME --Open the cursorOPEN databasesFETCH NEXT FROM databases INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + '''' print @sql insert into #tablename exec(@sql) -- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM #tablename -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN PRINT @TABLENAME -- Do the showcontig of all indexes of the table INSERT INTO #DBFRAGMENT EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END 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))<> ''''' --PRINT @INSERTTABLE EXEC (@inserttable) -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables delete from #tablename delete from #DBFRAGMENT FETCH NEXT FROM databases INTO @dbname END CLOSE databasesDEALLOCATE databasesdrop table #tablename--Delete the temporary tableDROP TABLE #DBFRAGMENTGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO

RE: Checking Your Database Fragmentation Level

nmargol — Tue, 22 Nov 2005 13:46:00 GMT

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?

RE: Checking Your Database Fragmentation Level

Stijn Vervaet — Tue, 22 Nov 2005 04:17:00 GMT

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

Much appreciated,

Stijn

RE: Checking Your Database Fragmentation Level

noeld — Mon, 21 Nov 2005 13:58:00 GMT

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,

RE: Checking Your Database Fragmentation Level

Stijn Vervaet — Mon, 21 Nov 2005 07:22:00 GMT

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

RE: Checking Your Database Fragmentation Level

RML51 — Fri, 18 Nov 2005 13:04:00 GMT

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

RE: Checking Your Database Fragmentation Level

hot2use — Fri, 18 Nov 2005 08:27:00 GMT

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

RE: Checking Your Database Fragmentation Level

Tatsu — Fri, 18 Nov 2005 08:21:00 GMT

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!

RE: Checking Your Database Fragmentation Level

kumarkaji@yahoo.com — Fri, 18 Nov 2005 08:16:00 GMT

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

RE: Checking Your Database Fragmentation Level

hot2use — Fri, 18 Nov 2005 03:08:00 GMT

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

RE: Checking Your Database Fragmentation Level

Dave Henderson — Sun, 28 Aug 2005 23:06:00 GMT

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

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

Dave H

RE: Checking Your Database Fragmentation Level

Preston Burroughs — Mon, 20 Jun 2005 09:17:00 GMT

Stll not having any luck.

RE: Checking Your Database Fragmentation Level

Christi Nicholson — Wed, 15 Jun 2005 12:04:00 GMT

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

RE: Checking Your Database Fragmentation Level

Preston Burroughs — Thu, 09 Jun 2005 10:12:00 GMT

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))<> ''''

RE: Checking Your Database Fragmentation Level

Bikeman — Mon, 06 Jun 2005 14:59:00 GMT

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

Checking Your Database Fragmentation Level

Nicholas Cain — Thu, 03 Jun 2004 10:20:00 GMT

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