Sql indexing: Best practices
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I'm willing to bet this index is 4 pages in size - how would i check the size?
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.
BTW the index itself can be a bit bigger but the number of fragments will be small.
ASKER
can i add a line to your query to restrict it to look at 1 db?
change line 12 : and (page_count*8/1024) = 1
sorry about that.
this query is relevant for the specific DB your using at the time .
this query is relevant for the specific DB your using at the time .
ASKER
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_s
but if i run it on the live db i get
Incorrect syntax near '('.
refering to line
join sys.dm_db_index_physical_s
look at the compatibilaty level of the database under sys.databases
it should be 90 or higher.
it should be 90 or higher.
ASKER
thats it - we run currently in 2000 mode due to legacy software issues - due to move soon though
Good Luck
>>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.
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.
ASKER
we do run full recovery so i must keep that in mind!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_s
NULL, NULL, NULL , NULL);
indexs-sql.xlsx