June 25, 2015 at 4:08 pm
Dear Experts,
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Please suggest a great method to do it.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
SchemaName sysname,
ObjectId INT,
IndexName sysname,
index_id INT,
page_count nvarchar(100),
[allow_page_locks] bit,
[allow_row_locks] bit,
[Fragmentation%] float,
partitionnumber Int,
type_desc nvarchar(400),
AllocUnitType nvarchar(1000),
fragment_count bigint,
avg_fragment_size_in_pages float,
[Online] Bit default 0)
Insert INTO #fragmentation
(
DBNAME,
TableName,
SchemaName,
ObjectId,
IndexName,
Index_Id,
page_count,
allow_page_locks,
allow_row_locks,
[Fragmentation%],
partitionnumber,
type_desc,
AllocUnitType,
fragment_count,
avg_fragment_size_in_pages
)
SELECT
db_name() DBNAME,
--ROW_NUMBER() OVER(ORDER BY p.object_id,p.index_id) Seq,
t.name TableName,
h.name SchemaName,
p.Object_Id,
i.name IndexName,
p.index_id AS Index_Id,
p.page_count AS record_count,
i.allow_page_locks allow_page_locks,
i.allow_row_locks allow_row_locks,
p.avg_fragmentation_in_percent Frag,
p.partition_number,
i.type_desc type_desc,
alloc_unit_type_desc AS AllocUnitType,
p.fragment_count,
p.avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
--where p.avg_fragmentation_in_percent >= 10.0
WHERE avg_fragmentation_in_percent >= @minFragPercent
AND avg_fragmentation_in_percent <= @maxFragPercent
AND p.index_id > 0
ANDp.page_count >= 4
AND fragment_count IS NOT NULL
AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY p.Object_Id;
Thanks.
June 25, 2015 at 4:11 pm
SQL-DBA-01 (6/25/2015)
ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
No they're not. Rebuilding the clustered index doesn't affect nonclustered indexes in any way.
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
June 25, 2015 at 4:33 pm
Thanks. Can you ping me a suitable MSDN link to show that.
Thanks.
June 25, 2015 at 4:40 pm
From https://msdn.microsoft.com/en-us/library/ms188388.aspx:
REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.
(emphasis mine)
Cheers!
June 25, 2015 at 4:45 pm
Thnx Guys.......Even I too knew but elder folks like to argue on the matter, so now I will paste the links for them to know.
Just one more point, I liked and thought to append here
Rebuilding or reorganizing a clustered index does not rebuild the non-clustered indexes. If you drop the clustered index and recreate it, that will force the non-clustered indexes to be rebuilt twice (once when the clustered index is dropped, and again when the clustered index is created).
Cheers to yuou all and a have a great evening. have fun
Thanks.
June 26, 2015 at 2:33 am
SQL-DBA-01 (6/25/2015)
Even I too knew but elder folks like to argue on the matter, so now I will paste the links for them to know.
Excuse me?
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply