June 23, 2008 at 3:23 am
Hi there,
Does anyone perhaps know what kind of index options there is on standard edition SQL Server. I know I can't use DBCC REINDEX.
What other options do I have.
Regards
IC
June 23, 2008 at 3:53 am
Hi
Are you sure its not this
DBCC DBREINDEX ('dbo.table, '', 70)
June 23, 2008 at 4:05 am
yes, thats what I meant.
Sorry about mispelling.
SQL Server Standard does not allow Online indexing. DBCC DBREINDEX is one them I can't use.
June 23, 2008 at 5:15 am
DBCC DBReindex works on SQL 2005 Standard Edition.
You should no longer use it though, use ALTER INDEX instead - see books online for details.
June 23, 2008 at 6:09 am
But you are right, you can't rebuild an index using the 'online' option on the standard edition of SQL Server 2005. You can only rebuld indexes online using SQL Server 2005 Entrprise edition...most of the other rebuild options are available though.
Gethyn Elliswww.gethynellis.com
June 23, 2008 at 6:55 am
When doing a DBCC DBREINDEX consider a friendlier Fillfactor. There is no rule, but generally it goes like this: If you have a highly transactional system and have often Page splits consider a fillfactor between 70-85.
you can check your fragmentation using the following;
select db_name(Database_id) as dbname,
OBJECT_NAME(object_id,database_id) as ObjectName,
index_id,
index_type_desc,
avg_fragmentation_in_percent,
avg_fragment_size_in_pages,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats('database_id here',NULL,NULL,NULL,'DETAILED')
look into avg_fragmentation_in_percent as your baseline and then check the avg_page_space_used_in_percent for a detailed view of the Page Fullness.
then consider your indexing strategy.
Maninder
www.dbanation.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply