SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

Who is online?  84 guests and 0 members
home  »  blogs  »  Parikshit Savjani  »  SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

change text size: A A A

posted  1/16/2012 4:25:40 PM  by Parikshit Savjani -  Views: [10362] 

The title for this post might sound ironical since rebuild of the index is used to remove the fragmentation of the index so how does rebuild of index affect the fragmentation of index?  Well, this is what I would like to explain in this blog post.
 
Generally, for rebuilding of indexes (alter index … rebuild) optimizer tries to generate a serial plan with no parallelism, however for large tables, the index rebuild compels the optimizer to generate parallel plan to distribute the keys across multiple threads to perform parallel sorts so that indexes rebuild can be performed faster at the expense of higher CPU.
 
The Parallel Index rebuild Query Plan uses range partitioning where in each of the thread is given a range of keys which it sorts and build a smaller index subunits.Finally there is Gather stream operator which links all the index subunits together (logically) to form a logically contiguous index page links but physically non-contiguous.
 
Thus when the optimizer decides to go for parallel plan to rebuild the index, the parallel threads does extent allocation in noncontiguous allocation and thereby doesn’t eliminate the fragmentation completely and still leads to some fragmentation due to extent allocation in non-contiguous location.
 
Let me illustrate this with an example
 
create table T1
(
a int primary key,
b char(80)
)

 
Performing large number of random inserts in the table which will cause fragmentation in the table
Declare @i int;
SET @i = 1
while (@i <=500000)
begin
insert into T1 values(@i,'Test')
insert into T1 values(1000000-@i,'Test')
set @i = @i + 1
end
Let us know check the fragmentation in the clustered index of the table caused due to random inserts
select database_id,object_id,index_id,index_level,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')

database_id object_id index_id  index_level    avg_fragmentation_in_percent    fragment_count        avg_fragment_size_in_pages      page_count 
12     197575742         1              0               66.7894587701899                     2358                        1.49660729431722                    3529        
12     197575742         1              1               90.9090909090909                      11                            1                                              11        
12     197575742         1              2               0                                                 1                             1                                               1        
 
As we see in the above output, the leaf level of the clustered index (level 0) is around 67% fragmentated with 2358 fragments with around 1.5 pages per fragments. Hence the total clustered index takes around 3541 pages (3529+11+1).
Now let me rebuild the index by using the following command
alter index all on T1 rebuild WITH(MAXDOP=2)
Even if I do not specific MAXDOP=2 , SQL instance on my laptop  will use parallelism of 2 by default since it is a dual-core machine.
After rebuild the index, let us check the fragmentation again.
select database_id,object_id,index_id,index_level,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')
database_id object_id      index_id  index_level avg_fragmentation_in_percent   fragment_count   avg_fragment_size_in_pages  page_count 
12               197575742   1             0              0.0868809730668983                  8                      143.875                               1151  
12               197575742   1             1              0                                                2                      1                                          2  
12               197575742   1             2              0                                                1                      1                                          1  
As expected we see in the above output, the fragmentation in the leaf level of the index is reduced to 0.09% with 8 fragments at leaf level and 2 fragments at the intermediate level. The leaf level has on an average 144 pages per fragments which intermediatel level has 1 page per fragments
Now let us truncate the table,populate the table again with the same script above and rebuild the index but this time WITH MAXDOP=1 setting and check the fragmentation of the index 
TRUNCATE TABLE t1;
GO

Declare @i int;
SET @i = 1
while (@i <=50000)
begin
insert into T1 values(@i,'Test')
insert into T1 values(100000-@i,'Test')
set @i = @i + 1
end

alter index all on T1 rebuild WITH(MAXDOP=1)
GO

select database_id,object_id,index_id,index_level,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')

database_id object_id             index_id   index_level   avg_fragmentation_in_percent   fragment_count   avg_fragment_size_in_pages   page_count
12              197575742             1            0                 0                                             3                        383.333333333333                1150 
12              197575742             1            1                 0                                             1                        2                                            2 
12              197575742             1            2                 0                                             1                        1                                            1 
As we see in the output now, the leaf level of the clustered index has now only 3 fragments with each fragments having around 384 pages on an average per fragment. Further the entire leaf level now requires only 1150 pages as compared 1151 pages when the index is rebuild WITH MAXDOP=2. if we compare the intermediate level we now have only 1 fragments which consists of 2 pages as compared to 2 fragments observed previously

A fragment is a chunk or a segment in the datafile with continuous allocation of pages. So more number of fragments is an indication of more number of dis-continuous allocation of segments hence more fragmentation.
My laptop didn't had enough resources nor did I have patience for data load, but if you want to see difference more prominent, you can load more rows in the above script and if you have more cpus and hence more parallel threads used for rebuild, the amount of fragments will be huge
Using the above learning we conclude the following
 
Conclusion
For large databases/datawarehouse generally of the order of TBs which are hosted on the  servers with large number of CPUs i.e SMP architecture, while rebuilding indexes it is recommended to rebuild with (MAXDOP=1). This can lead to index rebuild being slower but at the same time we ensure the indexes are completely defragmentated and further serial plan uses less resources (CPU and memory) as compared to parallel plan which causes high utilization of CPU and Memory.
Further, due to same reason in SQL 2008 R2 Fastrack Datawarehouse, Microsoft recommends to rebuild the index WITH (MAXDOP=1).
So, I would not recommend to use serial plan for rebuild indexes always. The answer is our favourite "Its depends", if the parallel index rebuild doesn't use much resources and reduces the fragmentation to a tolerable limit we can go for the parallel plan which is default. However if either the resource utilization caused by the index rebuild is high or if the fragmentation of the index is not reduced within the desired limit, we should go for MAXDOP=1
 
Further Reading
------------------
 

Hope this helps!!!
If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
 
Parikshit Savjani

Twitter: @talktosavjani
 
  Rate This Blog Entry:  register  or  login

Author

Parikshit Parikshit Savjani (Member since: 1/15/2012 6:38:05 AM)

View Parikshit Savjani 's profile

Comments (7)

Prashant
1/16/2012 6:06:42 PM Prashant said:

Good one..nicely explained.

by
AmitBansal
1/16/2012 11:52:11 PM Amit Bansal said:
Nice post
by
RamJaddu
1/18/2012 10:43:23 AM RamJaddu said:

Nice one

by
Göran Peterson
1/18/2012 11:16:35 AM Göran Peterson said:

Excellent post! Very helpful.

by
goraghu
1/22/2012 5:34:52 PM Raghu Gopalakrishnan said:

Thanks for this. Good learning. Will help me in the environment I support.

by
Gaurang
1/31/2012 9:58:35 AM Gaurang said:

Good Post.

by
Neelam
6/7/2012 8:20:39 AM Neelam said:
Nice post
by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Parikshit Savjani's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 249  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 313  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 326  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 800  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 2028  - Comments: 5
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 986  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 368  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 1536  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1702  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1786  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2493  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...