Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Density of indexes -> HELP !


Density of indexes -> HELP !

Author
Message
well0549
well0549
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 2
Hello everybody,

if you issue a DBCC SHOW_STATISTICS (<table>, <index>Wink

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



nmoore
nmoore
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 173
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>Wink.

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

you will get a resultset with the density.




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

Nigel Moore
======================
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 290
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
well0549
well0549
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
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



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: Moderators
Points: 8432 Visits: 780
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)



well0549
well0549
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
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.........



SChase
SChase
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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



goughj
goughj
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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



SChase
SChase
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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



SChase
SChase
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search