March 17, 2014 at 4:44 pm
Hi,
I am using this query to get the fragmentation above 30% and i would like to schedule this index rebuild with online rebuild option. Could you please me to get this done on better way and do i need to update statistics ?
select sys.tables.name as TableName,sys.indexes.name as IndexName,sys.indexes.type_desc as IndexType,
sys.indexes.index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
from sys.tables
inner join sys.objects on sys.tables.object_id = sys.objects.object_id
inner join sys.indexes on sys.indexes.object_id=sys.tables.object_id
inner join sys.dm_db_index_physical_stats(DB_ID(N'PDS'), NULL, NULL, NULL , 'SAMPLED')
on sys.indexes.index_id=sys.dm_db_index_physical_stats.index_id
where sys.dm_db_index_physical_stats.avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc;
March 17, 2014 at 10:59 pm
check this out from BOL:
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 18, 2014 at 3:58 am
Instead of trying to write your own script, I'd suggest using one that's already written. Michelle Ufford's script is available[/url] and works quite well. You can schedule it to run nightly or weekly through SQL Agent.
Michelle's script handles this, but I want to answer your question anyway, if you rebuild an index, that updates the statistics with a full scan of the data. You don't then need to do another statistics update.
"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
March 18, 2014 at 9:29 am
March 18, 2014 at 10:54 am
free_mascot (3/17/2014)
check this out from BOL:
if you usee the one from BOL (and it works well enough) be sure to insert code into it so that it updates statistics on the index as well when it does a reorganise.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply