Rebuilding Indexes on System Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Rebuilding Indexes on System Tables

Author

Topic

rnb0524
Starting Member

2 Posts

Posted - 2003-05-16 : 10:21:12

I am trying to rebuild indexes on system tables by using a script that will be called from a batch job. Does anyone have any ideas of how to do this? I know DBCC DBREINDEX does not support use on sytem tables.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-16 : 11:48:40

System tables aren't always real tables and you shouldn't try to do this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-16 : 11:50:06

Rebuild indexes on system tables? Why on earth would you want to do that? Since you can not use DBCC DBREINDEX to rebuild the system tables indexes, it is not possible to do what you want anyway.

Tara

rnb0524
Starting Member

2 Posts

Posted - 2003-05-16 : 12:20:24

If you set up a job in SQL Server Agent, it IS possible to rebuild indexes on system tables. And sp_fixindex can be used within a script which would either repair or rebuild system table indexes but I do not like what it does. I was looking for a way to rebuild the indexes through a batch job.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-16 : 13:15:09

Well if you can do it through a job in SQLServerAgent, then you can also do it in a batch job easily. So please tell us how you can do it in a job in SQLServerAgent and we'll help you convert it to a batch job.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 14:30:03

Well...
While you're at it why don't you try:

BEGIN TRAN

SELECT * FROM sysobjects (HOLDLOCK)
WHILE 1<>0
BEGIN
END

COMMIT TRAN

On second thought, better not.

Brett

8-)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-11-26 : 09:21:22

I have a similar problem here. Given that we cannot reindex system tables, and that messing with them is bad, e.g. we probably should not mess with sp_fixindex, what should I do regards the following :

dbcc showcontig ('sysxlogins') with FAST, TABLERESULTS, NO_INFOMSGS

gives me :

ObjectName : sysxlogins
ObjectId : 33
IndexName : sysxlogins
IndexId : 1
Level : 0
Pages : 162
Rows : NULL
MinimumRecordSize : NULL
MaximumRecordSize : NULL
AverageRecordSize : NULL
ForwardedRecords : NULL
Extents : 0
ExtentSwitches : 156
AverageFreeBytes : NULL
AveragePageDensity : NULL
ScanDensity : 13.375796178343949
BestCount : 21
ActualCount : 157
LogicalFragmentation : 49.382717132568359
ExtentFragmentation : NULL

Now as i understand it : Scan density should be as close to 100 as possible (it is a %) so 13.3 bad ! LogicalFragmentation refer's to percentage of out-of-order pages , and should be as low as possible.

A quote by Kalen Delaney on a totally seperate site : "You shouldn't worry about fragmentation until you get several dozen pages at least." - well I have 162 pages, so that seems to qualify.

Since I am experiencing Login performance problems in terms of creation, any pointers/thoughts ?

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources