none
Fragmentation after rebuild RRS feed

  • Question

  • Hello all, we have a high fragmentation on tables, reason for it was that jobs for reorg/rebuild were failing because of deadlocks. After a bit of investigation I found out it is probably because of MAXDOP parameter set to 4. So I created new maintenance plan just basic:

    1STEP, set MAXDOP to "1"

    2STEP, Rebuild on all User DBs

    3STEP, UPDATE statistics on All User DBs

    4STEP set MAXDOP back to "4"

    Job completed successfully after 4h 25min.

    But problem is that fragmentation after index rebuild is still same why? How it is possible?

    Tuesday, November 5, 2019 7:42 AM

All replies

  • Heaps? Do not you have clustered index on those tables?

    Autoshrink or manual shrink? Do you have a job that does those commands?

    And take a look at the indexes where page count >1000


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, November 5, 2019 8:10 AM
    Answerer
  • Hello Uri, Thanks for your reply,

    Yes there are also clustered indexes, yes we have job for log shrink for all SIMPLE DBs

    Tuesday, November 5, 2019 8:56 AM
  • But you don't shrink data files? Are you 100% certain? Absolutely 100%?

    Shrinking is the only thing I can think of that can cause this massive fragmentation in such a short time period. Perhaps running a trace to see if data files are shrunk? Something like http://sqlblog.karaszi.com/looking-for-strange-in-your-sql-server/. You can adjust the trace to your liking and also add a file target to get details about file file size changes.

    Besides, we do not recommend shrinking of log files either. What do you benefit from having the log files go up and down in size all the time? You just bay the price with reduced performance...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 5, 2019 9:01 AM
    Moderator
  • This is very much possible if you are rebuilding small indexes see my article below.

    Why is index still fragmented after rebuild ?

    Let me know if you need further explanation


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, November 5, 2019 9:01 AM
    Moderator
  • Shanky thx for reply, I'm aware that rebuilding small indexes doesn't have to have effect, but as you can see in screen there are indexes with more then 200 000 pages - which I don't think are so small.

    Tuesday, November 5, 2019 9:06 AM
  • So  you said that you have a clustered index on the  table with page count >1000?

    Can you show me script  you rebuild clustered index and its fragmentation before and after?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 5, 2019 9:08 AM
    Answerer
  • Shanky thx for reply, I'm aware that rebuilding small indexes doesn't have to have effect, but as you can see in screen there are indexes with more then 200 000 pages - which I don't think are so small.

    OK thanks I missed that. I need to see the script you are using to rebuild and reorganize. And also script to get fragmentation information. Are these tables heaps or are they having CI on them ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Tuesday, November 5, 2019 9:13 AM
    Moderator
  • Task for rebuild was just created in Maintenance plan to rebuild all Indexes in all user databases.

    Now I tried to rebuild manually just fragmented Inexes it is working fine also tried to do Rebuild With following script >

    --REBUILD ALL INDEXES IN DB
    --SPECIFY FILLFACTOR
    DECLARE @TableName VARCHAR(255)
    DECLARE @sql NVARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 90 
    DECLARE TableCursor CURSOR FOR
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
    FROM sys.tables
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    GO
    

    And it is working fine...
    Just don't understand why Maintenance plan for Rebuild Indexes did not work.

    Tuesday, November 5, 2019 12:44 PM
  • Please do not rely on MP , moreover  take a look at Ola's script to maintain  indexes I have been using for years without problem 

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 5, 2019 12:48 PM
    Answerer
  • You can go digging in your maint plans history tables and report files to see if there are any traces of why it didn't execute those commands. Without any details and potential error messages, we can't guess...

    Or, do as many of us are doing, use Ola Hallengren's script for database maintenance.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 5, 2019 3:36 PM
    Moderator
  • to have no fragmentation requires having contiguous space available, which is very impractical to achieve in a production server.
    What I do is have the PRIMARY filegroup for small tables, then 2 Filegroups for tables that I want to be highly contiguous. This is typically for performance validation in a test environment, and was more relevant when storage was on hard disks. It could also be if you want to the Fast Track Data Warehouse.
    Starting point is: larges tables are in a FG1, and FG2 is empty.
    Rebuild clustered indexes one at a time from FG1 to FG2 (with SORT_IN_TEMPDB) 
    If this is a test environment, disable the nonclustered indexes first (do not do this on production) then re-enable when the clustered index is done.

    Later, rebuild back from FG2 to FG1

    Note: there will typically be one large fragment for each thread on a parallel rebuild, so use MAXDOP whatever if this is OK. Also, the MAXDOP can build in the INDEX REBUILD statement WITH (MAX_DOP = xxx). Or you could do MAXDOP 1.
    This technique probably originated with Mike F, formerly of HP's TPC-H team.


    jchang

    Friday, November 8, 2019 9:31 PM