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

Density of indexes -> HELP ! Expand / Collapse
Author
Message
Posted Tuesday, October 01, 2002 9:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, October 01, 2002 10:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:19 AM
Points: 176, 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>).

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
Posted Tuesday, October 01, 2002 10:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
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
Posted Wednesday, October 02, 2002 2:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Wednesday, October 02, 2002 4:44 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Thursday, April 17, 2014 3:54 PM
Points: 8,369, Visits: 733
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
Posted Wednesday, October 02, 2002 5:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Thursday, October 03, 2002 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 03, 2002 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 03, 2002 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 03, 2002 11:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12»»

Permissions Expand / Collapse