February 23, 2004 at 3:42 pm
What is considered Best Practice for the reindexing of database system tables? What process is used?
February 23, 2004 at 7:09 pm
I don't think I have ever reindexed system tables and doubt it is a good idea to do so. From what I have seen SQL Server system objects generally take care of themselves.
Are you having problems with indexes on system tables?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 28, 2004 at 3:50 pm
Actually you cannot reindex the system tables, although there is an s_proc for this with sp_fixindex. sp_fixindex is doing dbcc dbreindex for all objects where object_id > 100 and dbcc dbrepair for all objects where object_id < 100. So dbrepair does not reindex the sysobjects and the sysindex tables.
This is an answer from Paul Randal DBCC Technical Lead, Microsoft SQL Server Storage Engine
in sqlserver public forum
"It's not possible to defragment system tables in SQL Server 2000. You should find that your system tables' fragmentation does not impede your system's performance due to the way they are accessed and that the hot pages should be in the buffer pool anyway."
There has been a long thread on this here in the past but I can't find it right now.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply