August 8, 2012 at 7:12 am
I use the following stored proc to find and record my index fragmentation in SQL 2005 & 2008:
ALTER PROCEDURE [dbo].[usp_Index_Fragment_Daily_Log]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM master.dbo.IndexFragmentLog WHERE LogDate < (SELECT DATEADD(MM, -1, GETDATE()));
EXEC sp_MSforeachdb 'USE ?
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'', ''ReportServerTempDB'', ''Pubs'', ''Northwind'', ''AdventureWorks'', ''AdventureWorksDW'')
INSERT INTO master.dbo.IndexFragmentLog (DBName, TableName, IndexID, IndexName, FragAvg, UserSeeks, UserScans, PartitionNum, LogDate)
SELECT
d.name AS ''DBName'',
o.name AS ''TableName'',
ps.index_id AS ''IndexID'',
b.name AS ''IndexName'',
ps.avg_fragmentation_in_percent AS ''FragAvg'',
iu.user_seeks AS ''UserSeeks'',
iu.user_scans AS ''UserScans'',
ps.partition_number AS ''PartitionNum'',
GETDATE() AS ''LogDate''
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
INNER JOIN sys.objects o
ON ps.object_id = o.object_id
INNER JOIN sys.databases d
ON d.database_id = ps.database_id
INNER JOIN sys.dm_db_index_usage_stats iu
ON b.object_id = iu.object_id
WHERE ps.database_id = DB_ID() and
b.name is not null and
ps.avg_fragmentation_in_percent > 10 and
OBJECTPROPERTY(iu.OBJECT_ID,''IsUserTable'') = 1
ORDER BY ps.OBJECT_ID'
END
So, my question is this....how can I modify this to work on SQL 2000?
August 8, 2012 at 8:30 am
tim.cloud (8/8/2012)
I use the following stored proc to find and record my index fragmentation in SQL 2005 & 2008:ALTER PROCEDURE [dbo].[usp_Index_Fragment_Daily_Log]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM master.dbo.IndexFragmentLog WHERE LogDate < (SELECT DATEADD(MM, -1, GETDATE()));
EXEC sp_MSforeachdb 'USE ?
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'', ''ReportServerTempDB'', ''Pubs'', ''Northwind'', ''AdventureWorks'', ''AdventureWorksDW'')
INSERT INTO master.dbo.IndexFragmentLog (DBName, TableName, IndexID, IndexName, FragAvg, UserSeeks, UserScans, PartitionNum, LogDate)
SELECT
d.name AS ''DBName'',
o.name AS ''TableName'',
ps.index_id AS ''IndexID'',
b.name AS ''IndexName'',
ps.avg_fragmentation_in_percent AS ''FragAvg'',
iu.user_seeks AS ''UserSeeks'',
iu.user_scans AS ''UserScans'',
ps.partition_number AS ''PartitionNum'',
GETDATE() AS ''LogDate''
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
INNER JOIN sys.objects o
ON ps.object_id = o.object_id
INNER JOIN sys.databases d
ON d.database_id = ps.database_id
INNER JOIN sys.dm_db_index_usage_stats iu
ON b.object_id = iu.object_id
WHERE ps.database_id = DB_ID() and
b.name is not null and
ps.avg_fragmentation_in_percent > 10 and
OBJECTPROPERTY(iu.OBJECT_ID,''IsUserTable'') = 1
ORDER BY ps.OBJECT_ID'
END
So, my question is this....how can I modify this to work on SQL 2000?
You don't. The system views and DMV's don't exist in SQL Server 2000. Here is where I would start:
http://msdn.microsoft.com/en-us/library/aa258803(v=sql.80)
Be sure to check out the tableresults parameter with the DBCC SHOWCONTIG.
August 8, 2012 at 9:20 am
DBCC SHOWCONTIG is your only option in SQL 2000.
This code fragment should help you setting up the procedure:
IF OBJECT_ID('tempdb..#contig') IS NOT NULL DROP TABLE #contig;
CREATE TABLE #contig (
DatabaseName NVARCHAR(128) NULL
,ObjectName NVARCHAR(128) NULL
,ObjectId INT NULL
,IndexName NVARCHAR(128) NULL
,IndexId INT NULL
,LEVEL INT NULL
,Pages BIGINT NULL
,Rows BIGINT NULL
,MinimumRecordSize INT NULL
,MaximumRecordSize INT NULL
,AverageRecordSize FLOAT NULL
,ForwardedRecords BIGINT NULL
,Extents BIGINT NULL
,ExtentSwitches BIGINT NULL
,AverageFreeBytes FLOAT NULL
,AveragePageDensity FLOAT NULL
,ScanDensity FLOAT NULL
,BestCount BIGINT NULL
,ActualCount BIGINT NULL
,LogicalFragmentation FLOAT NULL
,ExtentFragmentation FLOAT NULL
)
EXEC sp_MsForEachDB 'USE [?];
IF ''?'' NOT IN (
''master''
,''tempdb''
,''model''
,''msdb''
,''ReportServerTempDB''
,''Pubs''
,''Northwind''
,''AdventureWorks''
,''AdventureWorksDW''
)
BEGIN
INSERT INTO #contig (
ObjectName
,ObjectId
,IndexName
,IndexId
,LEVEL
,Pages
,Rows
,MinimumRecordSize
,MaximumRecordSize
,AverageRecordSize
,ForwardedRecords
,Extents
,ExtentSwitches
,AverageFreeBytes
,AveragePageDensity
,ScanDensity
,BestCount
,ActualCount
,LogicalFragmentation
,ExtentFragmentation
)
EXEC(''DBCC SHOWCONTIG WITH TABLERESULTS'');
UPDATE #contig SET DatabaseName = ''?'' WHERE DatabaseName IS NULL;
END
'
SELECT *
FROM #contig
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply