SQLServerCentral / Administration / SQL Server 7,2000 / Density of indexes -> HELP ! / Latest Posts

RE: Density of indexes -> HELP !

Jeff W — Thu, 03 Oct 2002 13:17:00 GMT

Good Posts!!Just remember not to confuse the 'Density' of a statistic with 'fragmentation' in an index. These are two completely seperate topics.

RE: Density of indexes -> HELP !

SChase — Thu, 03 Oct 2002 11:54:00 GMT

I actually run the RecordAllIndexStats before AND after the RebuildFragmentedIndexes procedure. This gives me a before/after picture with the following query:SELECT RTRIM(a.DatabaseName + '.' + a.TableOwner + '.' + a.TableName) [TableName], RTRIM(a.IndexName) [IndexName], a.ScanDensityPct [a.Dens], b.ScanDensityPct [b.Dens], a.Row_Count [a.RowCount], a.FragRows [a.FragRows], b.FragRows [b.FragRows], a.FragRows - b.FragRows [FragDiff], a.RunDate [a.RunDate], b.RunDate [b.RunDate]FROM DBTools..AllIndexStats a, DBTools..AllIndexStats b, DEV..sysindexes cWHERE a.IndexName = c.name AND c.indid = 1 AND a.DatabaseName *= b.DatabaseName AND a.TableOwner *= b.TableOwner AND a.TableName *= b.TableName AND a.IndexName *= b.IndexName AND b.RunDate = (SELECT MAX(RunDate) FROM DBTools..AllIndexStats) AND a.DatabaseName IN ('DEV') AND a.FragRows > 0 AND a.ScanDensityPct < 90 AND a.RunDate = (SELECT DISTINCT MAX(RunDate) FROM DBTools..AllIndexStats WHERE RunDate < (SELECT MAX(RunDate) FROM DBTools..AllIndexStats))ORDER BY a.FragRows - b.FragRows DESC

RE: Density of indexes -> HELP !

SChase — Thu, 03 Oct 2002 11:47:00 GMT

The SAP database that I'm working with is currently only 16GB but anticipated to grow beyond 500GB when put into production. So, I use the density stats in my AllIndexStats table to rebuild only those indexes that have a density that's less than 90%. This reduced the tuning part of my Maintenance Plan from 3hrs to just 10mins.CREATE PROC RebuildFragmentedIndexesAS SET NOCOUNT ONSET ANSI_WARNINGS OFF DECLARE @tablename VARCHAR (255)DECLARE @command VARCHAR (1000) -- Create the tableCREATE TABLE #fraglist ( [TableName] VARCHAR (255))INSERT INTO #fraglistSELECT RTRIM( '''' + DatabaseName + '.' + TableOwner + '.' + TableName) + '''' [TableName]FROM DBTools..AllIndexStats a, DEV..sysindexes bWHERE a.IndexName = b.name AND b.indid = 1 AND DatabaseName IN ('DEV') AND FragRows > 0 AND ScanDensityPct < 90 AND RunDate = (SELECT MAX(RunDate) FROM DBTools..AllIndexStats)ORDER BY FragRows DESC -- Declare databases cursorDECLARE tables CURSOR FOR SELECT TableName from #fraglistOPEN tablesFETCH NEXT FROM tables into @tablenameWHILE @@fetch_status = 0 BEGIN -- Do the showcontig of all indexes of the table SELECT @command = 'DBCC DBREINDEX (' + @tablename + ','''',0)' EXEC (@command) SELECT @command = 'CHECKPOINT' EXEC (@command) FETCH NEXT FROM tables INTO @tablenameEND-- Close and deallocate the cursorCLOSE tablesDEALLOCATE tables-- Delete the temporary tableDROP TABLE #fraglistGO

RE: Density of indexes -> HELP !

goughj — Thu, 03 Oct 2002 11:32:00 GMT

Just tried the proc and table on a server here. It worked great and was quick. SChase, you might post that in the scripts section of this community. Thanks again!Jody

RE: Density of indexes -> HELP !

SChase — Thu, 03 Oct 2002 10:52:00 GMT

I developed the following stored procedure to archive density information about clustered indexes for every user database:CREATE PROC RecordAllIndexStatsAS SET NOCOUNT ONSET ANSI_WARNINGS OFF DECLARE @tablename VARCHAR (128)DECLARE @dbname VARCHAR (128)DECLARE @command VARCHAR (1000)DECLARE @startrundate VARCHAR(19) SET @startrundate = GETDATE() -- Create the tableCREATE TABLE #fraglist ( [ObjectName] CHAR (255), [ObjectId] INT, [IndexName] CHAR (255), [IndexId] INT, [Level] INT, [Pages] INT, [Rows] 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) -- Declare databases cursorDECLARE databases CURSOR FOR SELECT name from master..sysdatabases WHERE name NOT IN ('pubs', 'tempdb', 'Northwind', 'model', 'master')OPEN databases FETCH next FROM databases into @dbnameWHILE @@fetch_status = 0 BEGIN DELETE FROM #fraglistSELECT COUNT(*) FROM #fraglist -- Declare tables cursorSELECT @command = 'USE ' + @dbname + ' DECLARE tables CURSOR FOR SELECT TABLE_SCHEMA + ''.'' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''EXEC (@command) -- Open the cursorOPEN tables -- Loop through all the tables in the databaseFETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS = 0BEGIN-- Do the showcontig of all indexes of the table SELECT @command = 'USE ' + @dbname + ' INSERT INTO #fraglist EXEC (''DBCC SHOWCONTIG ([' + @tablename + ']) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'')' EXEC (@command) FETCH NEXT FROM tables INTO @tablenameEND-- Close and deallocate the cursorCLOSE tablesDEALLOCATE tables-- Get the the number of rows from sysindexesSELECT @command = 'UPDATE #fraglistSET Rows = a.rowsFROM ' + @dbname + '..sysindexes aWHERE a.indid < 2AND #fraglist.ObjectId = a.id'EXEC (@command) -- save fragmentation data SELECT @command = 'INSERT INTO DBTools..AllIndexStatsSELECT ''' + @startrundate + ''' AS RunDate, ''' + @dbname + ''' [DatabaseName], c.name AS TableOwner, Left(a.ObjectName, 30) AS TableName, LEFT(a.IndexName, 50) AS IndexName, CAST(a.ScanDensity AS int) AS ScanDensity, a.Rows AS Row_Count, CAST(ROUND(a.Rows * ((100 - a.ScanDensity)/100),0) AS INT) AS FragRowsFROM #fraglist a, ' + @dbname + '..sysobjects b, ' + @dbname + '..sysusers cWHERE a.ObjectId = b.id AND b.uid = c.uidORDER BY FragRows DESC'EXEC (@command) -- Close and deallocate the databases cursor FETCH NEXT FROM databases INTO @dbname ENDDEALLOCATE databases -- Delete the temporary tableDROP TABLE #fraglistGOHere's the table they're inserted into:CREATE TABLE [AllIndexStats] ( [RunDate] [datetime] NOT NULL , [DatabaseName] [sysname] NOT NULL , [TableOwner] [sysname] NOT NULL , [TableName] [sysname] NOT NULL , [IndexName] [sysname] NOT NULL , [ScanDensityPct] [int] NOT NULL , [Row_Count] [int] NOT NULL , [FragRows] [int] NOT NULL ) ON [PRIMARY]GO

RE: Density of indexes -> HELP !

well0549 — Wed, 02 Oct 2002 05:43:00 GMT

I think this is the only option, To write a DTS Package that does it all.But i remember i saw something on a web page somewhere that did it immeadiately.but i have been looking for two days now so i guess i will create the DTS package.Unless somebody else has the trick for me.........

RE: Density of indexes -> HELP !

Antares686 — Wed, 02 Oct 2002 04:44:00 GMT

The problem is it comes from multiple output recordset not a singe. You may be able to write a DTS package with Active Script to do what you want using NextRecordset to move between the 3 outputs as you need or just the first 2."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: Density of indexes -> HELP !

well0549 — Wed, 02 Oct 2002 02:03:00 GMT

Hmmm I know i get three resultsets......that is why I am having this problem.Switching to TXT is not an option.suppose running a script on a production server with thousands of tables.I guess it would have a undesired performance impactSo what i am looking for is a way to catch the output in a table

RE: Density of indexes -> HELP !

Greg Larsen — Tue, 01 Oct 2002 10:58:00 GMT

You might consider creating a TXT file from the output of DBCC SHOW_STATISTICS and processing through that TXT file inserting only the data you want into your table.Gregory Larsen, DBAIf you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

RE: Density of indexes -> HELP !

nmoore — Tue, 01 Oct 2002 10:45:00 GMT

The thing is, I am almost certain that you are mistaken in thinking that you get a resultset as the output from DBCC SHOW_STATISTICS (<table>, <index>).If you take a closer look at the results it returns it actually resembles 3 separate resultsets.

quote:
if you issue a DBCC SHOW_STATISTICS (<table>, <index>)you will get a resultset with the density.

You may be able to do what you want through DTS.

Density of indexes -> HELP !

well0549 — Tue, 01 Oct 2002 09:23:00 GMT

Hello everybody,if you issue a DBCC SHOW_STATISTICS (<table>, <index>)you will get a resultset with the density.What I want is the result in a RECORD in my DB.Does anybody know of a way to retrieve the density for an index and store the result in a table........Thanks in advance........I tried the following : ( But it didn''t work )create table #temp1([Updated] datetime,[Rows] int,[Rows Sampled] int,[Steps] int,[Density] real,[Average key length] int,[Columns Names] sysname,[RANGE_HI_KEY] int,[RANGE_ROWS] int,[EQ_ROWS] int,[DISTINCT_RANGE_ROWS] int,[AVG_RANGE_ROWS] int)insert into #temp1 EXEC ('DBCC SHOW_STATISTICS (<table>, <index>')