January 29, 2013 at 9:21 am
Currently in my SQL 2008 R2 env's -- I have an efficient proc in place which runs nightly at 7pm on all DB's and ReIndexes ONLY THOSE OBJECTS satisfying ReIndex criteria:
• DBCC SHOWCONTIG to identify objects (tables/indexes) requiring ReIndex (either Rebuild or Reorg) -- based on customizable thresholds including:
---- o Fragmentation % (optimally, the object is 0% fragmented)
---- o Scan Density % (optimally, the object is 100% dense)
---- o DBCC SHOWCONTIG renders these values for each object
• Determine whether to Rebuild or Reorg the object
• Perform Reindex operation ONLINE leaving the active object available for CRUD
• Transmits eMail summarizing the detailed activity
PROBLEM -- DBCC SHOWCONTIG still works in SQL 2012 but is planned for deprecation AND it pulls from sysindexes NOT a DMV..
Can someone point me to a similar proc for 2008 R2 or 2012 that will replace my current proc?
thanks..
January 29, 2013 at 12:04 pm
Hi, I use the following script, is this any help?
CREATE PROCEDURE dbo.[IndexRebuild] AS
DECLARE @TableName NVARCHAR(500);
DECLARE @SQLIndex NVARCHAR(max);
DECLARE @RowCount INT;
DECLARE @Counter INT;
DECLARE @IndexAnalysis TABLE
(AnalysisID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName NVARCHAR(500),
SQLText NVARCHAR(max),
IndexDepth INT,
AvgFragmentationInPercent FLOAT,
FragmentCount BIGINT,
AvgFragmentSizeInPagesFLOAT,
PageCount BIGINT)
BEGIN
INSERT INTO @IndexAnalysis
SELECT [objects].name,
'ALTER INDEX [' + [indexes].name + '] ON [' + [schemas].name + '].[' + [objects].name +'] ' +
(CASE
WHEN ([dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20 AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40) THEN 'REORGANIZE'
WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40 THEN 'REBUILD' END) AS zSQL,
[dm_db_index_physical_stats].index_depth,
[dm_db_index_physical_stats].avg_fragmentation_in_percent,
[dm_db_index_physical_stats].fragment_count,
[dm_db_index_physical_stats].avg_fragment_size_in_pages,
[dm_db_index_physical_stats].page_count
FROM [sys].[dm_db_index_physical_stats](DB_ID(),NULL,NULL,NULL,'LIMITED' ) AS [dm_db_index_physical_stats]
INNER JOIN [sys].[objects] AS [objects]ON ([dm_db_index_physical_stats].[object_id] = [objects].[object_id])
INNER JOIN [sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id])
INNER JOIN [sys].[indexes] AS [indexes]ON ([dm_db_index_physical_stats].[object_id] = [indexes].[object_id] AND [dm_db_index_physical_stats].index_id = [indexes].index_id )
WHERE index_type_desc <> 'HEAP'
AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20
END
SELECT @RowCount = COUNT(AnalysisID) FROM @IndexAnalysis
SET @Counter = 1
WHILE @Counter <= @RowCount
BEGIN
SELECT @SQLIndex = SQLText FROM @IndexAnalysis WHERE AnalysisID = @Counter
EXECUTE sp_executesql @SQLIndex
SET @Counter = @Counter + 1
END
GO
January 29, 2013 at 1:23 pm
http://ola.hallengren.com/Versions.html
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2013 at 1:27 pm
Hi Gail,
Is the script I posted from Ola's site? I wasn't trying to take credit for anyone's script, it's just something I use.
Andrew
January 31, 2013 at 6:18 am
I also like Michele Ufford's scripts[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply