SQL Server Performance Forum – Threads Archive

re-building the index

I have run a script which will manage fragmentation in all system indexes in the executing database.The script execute fine for most of the system objects except for the following dbs.Why I am getting a "re-building the index" message for these objects.Does all other system objects have no fragmentation issue. What to do now in this case? Server: Msg 50001, Level 18, State 1, Line 1
Clustered index sysobjects on table sysobjects is 20 percent fragmented. Consider re-building the index. View C:Frag_Info.log for more info.
Server: Msg 50001, Level 18, State 1, Line 1
Clustered index sysindexes on table sysindexes is 18 percent fragmented. Consider re-building the index. View C:Frag_Info.log for more info.
Server: Msg 50001, Level 18, State 1, Line 1
Clustered index syscolumns on table syscolumns is 28 percent fragmented. Consider re-building the index. View C:Frag_Info.log for more info.
Server: Msg 50001, Level 18, State 1, Line 1
NonClustered index ncsyscolumns on table syscolumns is 33 percent fragmented. Consider re-building the index. View C:Frag_Info.log for more info.
Server: Msg 50001, Level 18, State 1, Line 1
Clustered index syscomments on table syscomments is 33 percent fragmented. Consider re-building the index. View C:Frag_Info.log for more info.

I don’t know what scrip are you using.
But I suppose that scrip only check % of fragmentation and inform to you when is below some value, and in the others databases just that value is still high. Check the best scrip (to me) by Tom Pullen. http://www.sql-server-performance.com/tp_automatic_reindexing.asp Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Haha, that’s a really funny script you have!
It is running against the system tables. In SQL Server 2000 you cannot reindex those system tables mentioned above via DBCC REINDEX or sp_fixindex. Here’s an answer regarding this by Paul Randal in the public MS newsgroups:
"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." Paul Randal is Dev Lead of the SQL Server storage engine. So certainly someone who knows what he’s talking about. —
Frank
http://www.insidesql.de

Just for giggles. Try this on a test database: <pre id="code"> USE TEST GO exec sp_configure ‘allow updates’, 1 go RECONFIGURE WITH OVERRIDE go create unique clustered index [sysobjects] on sysobjects(id) with drop_existing go exec sp_configure ‘allow updates’, 0 go RECONFIGURE WITH OVERRIDE </pre id="code"> Chances are more than good to corrupt the whole db. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

Any one of you ever tried to defragment system tables and reindex system indexes.i am wondering whether fragmentation of system tables will cause any performance issue or not.

I don’t worry about fragmentation on the system tables as there will not be much activity of data inserts/deletes, so leave it to SQL servre. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by aaronsandy Any one of you ever tried to defragment system tables and reindex system indexes.i am wondering whether fragmentation of system tables will cause any performance issue or not. <hr height="1" noshade id="quote"></blockquote id="quote"> Not to sound combative here, but do you actually read the responses you get? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

Frank I feel you’re not the first person to raise such doubt, and one should consider the given options with more ground work at their end. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sigh [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

]]>