Query to rebuild and reorganize depending on fragmentation

  • Thank you very much for all the replies..........

  • Hi every one,

    After running the script(to rebuild the query based on fragmentation ) if i run this again:

    DECLARE @dbid smallint;

    SET @dbid = DB_ID();

    SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag,

    page_count

    FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

    WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation

    --AND index_id > 0 -- Ignore heaps

    --AND page_count > 25;

    Why is it showing same results again (i mean the excel )?what concerns me is after running the script to rebuild query the results for above query should vary(in terms of fragmentation).

    Correct me if i am wrong...

  • Well, all your indexes, not HEAPS are 27 pages or less which probably means defragmentation won't help performance any way. With small tables you tend to get more mixed extents which may not be able to be totally removed. Remember that SQL Server stores data in groups of 8 8kb pages (extents), so an index that has 27 pages will likely have at least some pages on mixed extents.

  • So ,can i proceed with the script below by just un commenting Page_count>25 in where clause and updating it to 27 since it is not effecting performance anyway(and i am getting the same results each and every time i run the script)....

    or Can you give me any suggestion on this?

  • You already asked how many pages you should have before rebuilding and the BOL answer is 1000 and I provided a link as to where that number came from. You need to determine what is best for your system. I wouldn't worry about indexes with fewer than 100 pages, but, again, you need to see what works best for you.

  • I am sorry for that....

    With small tables you tend to get more mixed extents which may not be able to be totally removed. Remember that SQL Server stores data in groups of 8 8kb pages (extents), so an index that has 27 pages will likely have at least some pages on mixed extents.

    Can you tell me solution for this?

  • There isn't one that I am aware of. Basically it is not an issue. Odds are that any tables that small are likely in the buffer cache so you rarely go to disk to get them.

  • Hi ,

    I want to schedule this query which rebuild indexes based on fragmentattion and the Database in the query gets updated daily at 6:00 AM.Since the database has many small tables when is the best to schedule the query?every day or once in a week or month???????

    Thanks

  • srilu_bannu (1/6/2010)


    Hi ,

    I want to schedule this query which rebuild indexes based on fragmentattion and the Database in the query gets updated daily at 6:00 AM.Since the database has many small tables when is the best to schedule the query?every day or once in a week or month???????

    Thanks

    Schedule should depend on the period over which the tables will be fragmented, which depends on the DML operations that happen on your system.

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • Here is the problem my fragmentation does not change Since i have many small tables even if i rebuild the indexes (if you look into my previous posts you will understand).Now , what should i do since i cannot tell when exactly the fragmentation changes...

    Correct me if i am wrong

  • srilu_bannu (1/11/2010)


    Here is the problem my fragmentation does not change Since i have many small tables even if i rebuild the indexes (if you look into my previous posts you will understand).Now , what should i do since i cannot tell when exactly the fragmentation changes...

    Correct me if i am wrong

    You can schedule this job on a weekly or daily basis depending on your DB activity and the amount of time it takes to finish rebuilding indexes on all the tables..so as to avoid any interruption to other processes on account of unailability of table data..

    HTH...

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 11 posts - 16 through 25 (of 25 total)

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