Sql indexing: Best practices

malraff
malraff used Ask the Experts™
on
hi all

some of our tables indexs are badly fragmented - up to 90% !

so i am going to set up a new indexing routine, and would appreciate some input..

the sql server is basically unused at the weekends apart from backups etc so i was thinking that i may as well do a full rebuild on all indexs every weekend regardless of fragmentation? i know from reading on the subject that a rebuild isnt deemed necessary unless frag >=30% and a reorganise when > 5% <30%

but will it have any negative effect if i do the full rebuild every week?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Database Architect
Commented:
hi

first look at this query it will return indexes with over 25% frag (this is best practice),
will not return sys objects (no need),

will generate the reorgenize and rebuild per fragmented index for you.

as for when to use what then very simple i rearly use reorgenize only when i cant rebuild online
and i want to minimize impact on production the i use it .

you cant rebuild clustered indexes online when the table has a TEXT or XML or any "NON" standard datatype so you can rebuild offline (result in looking and havy impact on production)
so use the reorgenize.

there is no problem with rebuilding indexes every day week month its all about the Need and impact on your server.

i usualy rebuild the realy heavily used objects weekly and the rest when ever need.


SELECT db_name(a.database_id) dbname	,	object_name(s.OBJECT_ID) TableName,
i.name,page_count*8/1024 sizeMB	 ,a.avg_fragmentation_in_percent,
'ALTER INDEX ['+  i.name +'] ON ['+ object_name(s.OBJECT_ID) +'] REORGANIZE WITH ( LOB_COMPACTION = ON )' DDL_reorg,
'ALTER INDEX ['+  i.name +'] ON ['+ object_name(s.OBJECT_ID) +'] rebuild with (online=on)' DDL_rebuild
FROM sys.dm_db_partition_stats s join
sys.indexes i on (i.object_id=s.object_id and i.index_id=s.index_id)
join sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a 
ON a.object_id = i.object_id 	AND a.index_id = i.index_id
where a.database_id = db_id()
and avg_fragmentation_in_percent > 25
And object_name(i.OBJECT_ID) not like 'sys%'
and (page_count*8/1024) > 0
and i.name is not null
order by  sizeMB desc ,avg_fragmentation_in_percent desc 

Open in new window

Author

Commented:
cheers for this Ashilo

i am pretty new to the ins/outs of indexing and when i checked the maintenance plan that is supposedly re indexing i saw that it setup to rebuild every week - its reporting that this is happening fine! but why when i run scripts to check fragmentation do i get lots of entries suggesting other wise?

the script i use to check the db is and i have uploaded the results

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('SysproCompanyW'),
NULL, NULL, NULL , NULL);


indexs-sql.xlsx
Aaron ShiloChief Database Architect
Commented:
There's no guarantee that any index rebuild of any form will leave you with 0% fragmentation. Don't worry about small amounts of fragmentation and don't worry about fragmentation on very small indexes. I'm willing to bet this index is 4 pages in size, well under the size where you should start worrying.
Aaron ShiloChief Database Architect

Commented:
and another thig

under the INDEX_TYPE column in your excell you see HEAP this means that this isent an index but just the table.

so anyway you cant defragment a table with no clustered index.

Author

Commented:
I'm willing to bet this index is 4 pages in size - how would i check the size?
Aaron ShiloChief Database Architect

Commented:
im my query you will find a column named "sizeMB"  its pages*8kb / 1024 for megabites.

BTW the index itself can be a bit bigger but the number of fragments will be small.

Author

Commented:
can i add a line to your query to restrict it to look at 1 db?
Aaron ShiloChief Database Architect

Commented:
change line 12 : and (page_count*8/1024) = 1
Aaron ShiloChief Database Architect

Commented:
sorry about that.

this query is relevant for the specific DB your using at the time .

Author

Commented:
strange one - when i run this query on our test db it runs fine(its a backup of our live hence exactly the same structure- data just old),
but if i run it on the live db i get

Incorrect syntax near '('.

refering to line
join sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a
Aaron ShiloChief Database Architect

Commented:
look at the compatibilaty level of the database under sys.databases
it should be 90 or higher.

Author

Commented:
thats it - we run currently in 2000 mode due to legacy software issues - due to move soon though
Aaron ShiloChief Database Architect

Commented:
Good Luck
Top Expert 2012

Commented:
>>but will it have any negative effect if i do the full rebuild every week? <<
If you are using Full Recovery Model then make sure you make frequent Transaction Log backups (say every 10 minutes) or you will very quickly blow up the size of your Transaction Logs.

Author

Commented:
we do run full recovery so i must keep that in mind!
AnujSQL Server DBA
Top Expert 2011
Commented:
I use Michelle Ufford Index Defragment Script, its awesome. There are so many features and they are customizable too. More over its free to use script.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial