October 1, 2002 at 9:23 am
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>')
October 1, 2002 at 10:45 am
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
======================
October 1, 2002 at 10:58 am
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
October 2, 2002 at 2:03 am
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
October 2, 2002 at 4:44 am
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)
October 2, 2002 at 5:43 am
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.........
October 3, 2002 at 10:52 am
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
October 3, 2002 at 11:32 am
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
October 3, 2002 at 11:47 am
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
October 3, 2002 at 11:54 am
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
October 3, 2002 at 1:17 pm
Good Posts!!
Just remember not to confuse the 'Density' of a statistic with 'fragmentation' in an index. These are two completely seperate topics.
"Keep Your Stick On the Ice" ..Red Green
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply