Fragmentation size

  • Dear Friends

    1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

    2-what is the relationship between re-build or re-organise index by log file.

    Thanks lot

  • zi (12/26/2012)


    Dear Friends

    1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

    2-what is the relationship between re-build or re-organise index by log file.

    Thanks lot

    1) Fragmentation for the database as whole doesn't make sense in this context. You need to look at each index individually.

    2) I don't understand your question here. What do you mean?

    You should probably do a google search for "sql server rebuild vs reorganize index" and be prepared to spend some time reading.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • zi (12/26/2012)


    1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

    Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for replying

    - what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes

    -why when re-organise or re-build indexes make log file bigger.

    Thanks lot

  • zi (12/26/2012)


    - what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes

    Then you look at the fragmentation of the indexes and decide which to rebuild. I recommend you use something like Ola's scripts

    http://ola.hallengren.com/Versions.html

    -why when re-organise or re-build indexes make log file bigger.

    Because all modifications to the DB are logged and both index rebuilds and reorganise operations do a substantial amount of modifications

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you very much

  • zi (12/26/2012)


    Dear Friends

    1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

    2-what is the relationship between re-build or re-organise index by log file.

    Thanks lot

    These are actually interview questions.... important interview questions that every DBA should know by heart. The fact that you're posting these questions means you really need to spend some time practicing being a DBA before you answer another ad for the position. And, no... I'm not trying to be mean here. Call it "tough love" for someone looking for a job as a DBA. Buy yourself a copy of the SQL Server Develop Edition, install it on your laptop or home computer, and hit the books.

    You might also want to learn how to Google for such answers instead of relying on the kindness of others who might not actually know the answers to your questions. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here is an excellent difference between rebuilding the indexes & reorganising the indexes

    Eg.

    Index Rebuild : This process drops the existing Index and Recreates the index.

    USE AdventureWorks;

    GO

    ALTER INDEX ALL ON Production.Product REBUILD

    GO

    Index Reorganize : This process physically reorganizes the leaf nodes of the index.

    USE AdventureWorks;

    GO

    ALTER INDEX ALL ON Production.Product REORGANIZE

    GO

    Note: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

  • Thanks for replying

    Do you have a script to know the fragmentation size for the whole database not only table by table.

    Thanks lot

  • zi (12/30/2012)


    Thanks for replying

    Do you have a script to know the fragmentation size for the whole database not only table by table.

    Thanks lot

    To repeat:

    GilaMonster (12/26/2012)


    zi (12/26/2012)


    1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

    Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.

  • zi (12/30/2012)


    Do you have a script to know the fragmentation size for the whole database not only table by table.

    That's a completely nonsensical request as the concept of 'fragmentation size for the whole database' is meaningless. As I mentioned earlier, databases don't have fragmentation. Indexes do.

    Then you look at the fragmentation of the indexes and decide which to rebuild. I recommend you use something like Ola's scripts

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • zi (12/30/2012)


    Thanks for replying

    Do you have a script to know the fragmentation size for the whole database not only table by table.

    Thanks lot

    You don't need one. Just check the file fragmentation report of your favorite disk defragmentation tool for the file name(s) of the given database.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Try the script below to find out the fragmentation information. Hope it's a good start. Modified the values to fit the specs in your environment.

    /*

    Display statistics for index fragmentation for a selected database

    the 'DETAILED' option gives a more accurate results and

    slows some the results use NULL instead

    See http://msdn.microsoft.com/en-us/library/ms188917.aspx for further information

    */

    SELECTOBJECT_NAME(OBJECT_ID) TableName

    , ssi.name

    , index_type_desc

    , avg_fragmentation_in_percent

    --, fragment_count

    --, avg_fragment_size_in_pages

    , page_count

    FROM sys.dm_db_index_physical_stats(db_id('ULDB'),NULL,NULL,NULL, NULL)

    INNER JOIN sys.sysindexes SSI

    ON OBJECT_ID = SSI.id

    AND index_id = SSI.indid

    WHERE avg_fragmentation_in_percent > 15

    AND index_type_desc <> 'HEAP'

    AND page_count > 30

    ORDER BY avg_fragmentation_in_percent DESC, OBJECT_NAME(OBJECT_ID)

  • This is the script I use to rebuild/reorganized my indexes. Again modify when needed.

    /*

    Script copied from http://msdn.microsoft.com/en-us/library/ms188917.aspx

    and modified to fit our environment

    CHECK FOLLOWING TO MAKE SURE THE SCRIPT RUNS WITH THE RIGHT CONFIGURATIONS

    1) MAKE SURE YOU SPECIFY THE DATABASE TO REBUIL/REORGANIZED INDEXES FOR

    2) CHECK WHAT INDEXES ARE NEEDED TO BE REBUILD/REORGANIZE (HEAP, CLUSTERED, NONCLUSTERED)

    3) avg_fragmentation_in_percent VALUE

    4) page_count VALUE

    5) THE FRAGMENTATION RANGE SETTING WHICH DETERMINES EITHER TO REORGANIZED OR REBUILD THE INDEX

    6) CHECK THE MESSAGES WINDOW TO SEE THE ALTERED INDEX LIST

    */

    USE ULDB

    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')

    INNER JOIN sys.sysindexes SSI

    ON OBJECT_ID = SSI.id

    AND index_id = SSI.indid

    WHERE avg_fragmentation_in_percent > 15.0

    AND index_id > 0

    -- COMMENT THIS LINE IF YOU NEED TO REBUILD/REORGANIZED ALL INDEXES INCLUDING HEAP INDEXES

    AND index_type_desc <> 'HEAP'

    AND page_count > 30

    ORDER BY avg_fragmentation_in_percent DESC, OBJECT_NAME(OBJECT_ID)

    -- display temporary table before the changes

    SELECT * FROM #work_to_do

    -- 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;

    -- 15 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

    IF @frag < 15.0

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 15.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;

    -- display temporary table after the changes

    SELECT * FROM #work_to_do

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    GO

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply