Density of indexes -> HELP !

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Density of indexes -> HELP !

11 posts, Page 1 of 2

1 2 »»

Density of indexes -> HELP !

Rate Topic

Display Mode

Topic Options

Author

Message

well0549

Posted Tuesday, October 01, 2002 9:23 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, Visits: 2

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

Post #7124

nmoore

Posted Tuesday, October 01, 2002 10:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 18, 2012 9:37 AM
Points: 176, Visits: 172

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.

Nigel Moore
======================

Post #42673

Greg Larsen

Posted Tuesday, October 01, 2002 10:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #42674

well0549

Posted Wednesday, October 02, 2002 2:03 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, Visits: 2

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 impact

So what i am looking for is a way to catch the output in a table

Post #42675

Antares686

Posted Wednesday, October 02, 2002 4:44 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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)

Post #42676

well0549

Posted Wednesday, October 02, 2002 5:43 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, Visits: 2

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.........

Post #42677

SChase

Posted Thursday, October 03, 2002 10:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 03, 2002 12:00 AM
Points: 3, Visits: 1

I developed the following stored procedure to archive density information about clustered indexes for every user database:

CREATE PROC RecordAllIndexStats
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @tablename VARCHAR (128)
DECLARE @dbname VARCHAR (128)
DECLARE @command VARCHAR (1000)
DECLARE @startrundate VARCHAR(19)

SET @startrundate = GETDATE()

-- Create the table
CREATE 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 cursor
DECLARE databases CURSOR FOR
SELECT name from master..sysdatabases WHERE name NOT IN ('pubs', 'tempdb', 'Northwind', 'model', 'master')
OPEN databases
FETCH next FROM databases into @dbname
WHILE @@fetch_status = 0
BEGIN

DELETE FROM #fraglist
SELECT COUNT(*) FROM #fraglist

-- Declare tables cursor
SELECT @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 cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- 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 @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Get the the number of rows from sysindexes

SELECT @command =
'UPDATE #fraglist
SET Rows = a.rows
FROM ' + @dbname + '..sysindexes a
WHERE a.indid < 2
AND #fraglist.ObjectId = a.id'
EXEC (@command)

-- save fragmentation data

SELECT @command = 'INSERT INTO DBTools..AllIndexStats
SELECT ''' + @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 FragRows
FROM #fraglist a, ' + @dbname + '..sysobjects b, ' + @dbname + '..sysusers c
WHERE a.ObjectId = b.id
AND b.uid = c.uid
ORDER BY FragRows DESC'
EXEC (@command)

-- Close and deallocate the databases cursor
FETCH NEXT FROM databases INTO @dbname
END
DEALLOCATE databases

-- Delete the temporary table
DROP TABLE #fraglist
GO

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

Post #42678

goughj

Posted Thursday, October 03, 2002 11:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 20, 2002 12:00 AM
Points: 42, Visits: 1

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

Post #42679

SChase

Posted Thursday, October 03, 2002 11:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 03, 2002 12:00 AM
Points: 3, Visits: 1

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 RebuildFragmentedIndexes
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @tablename VARCHAR (255)
DECLARE @command VARCHAR (1000)

-- Create the table
CREATE TABLE #fraglist (
[TableName] VARCHAR (255))

INSERT INTO #fraglist
SELECT RTRIM(
''''
+ DatabaseName
+ '.'
+ TableOwner
+ '.'
+ TableName)
+ '''' [TableName]
FROM DBTools..AllIndexStats a, DEV..sysindexes b
WHERE 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 cursor
DECLARE tables CURSOR FOR
SELECT TableName from #fraglist
OPEN tables
FETCH NEXT FROM tables into @tablename

WHILE @@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 @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Delete the temporary table
DROP TABLE #fraglist
GO

Post #42680

SChase

Posted Thursday, October 03, 2002 11:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 03, 2002 12:00 AM
Points: 3, Visits: 1

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 c
WHERE 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

Post #42681

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions