Query to rebuild and reorganize depending on fragmentation

Question

Post reply

Query to rebuild and reorganize depending on fragmentation

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

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

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 1

srilu_bannu

Hall of Fame

Points: 3100

December 14, 2009 at 4:42 pm

#1092293

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

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 2

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 4

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?

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 6

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?

Jack Corbett SSC Guru Points: 184393 More actions · Answer 7

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 8

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

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."

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 10

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 11

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."