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

Old Forums

CLOSED - General SQL Server

Reindexing the system tables

Author

Topic

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 04:15:43

Hi there,

is there a way to reindex the sytem tables?

I know I cannot DBXX DBREINDEX or INDEXDEFRAG for they are not suported, nor delete and recreate the indexes on those tables.

sp_fixindex does also not work

Any ideas?

Cheers,
Frank

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-12 : 07:50:57

No, they can't be reindexed. The easiest way to FUBAR a SQL Server is to play around with the system tables. Don't do it.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 08:01:04

Ever seen SAP R/3 on SQL2k?

No workaround at hand? I mean apart from using DTS to copy to another server

Cheers,
Frank

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-12 : 08:03:37

No, I haven't.

When you say "system tables", do you mean the regular sysobjects, sysindexes etc. that SQL Server uses? Or does SAP create its own tables and mark them as system tables?

Either way, if SAP is doing something that prevents you from administering your server, you should contact them before messing around with it.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 08:08:08

Me, too.

I'm asking this question on behalf of a friend which is unanswered yet

Yes, the regular system tables
...and as you may know SAP is coming with over 27.000 tables. Now my sysobjects tables hae over 275.000 Rows, the sysindexes over 60.000 and the syscolumns over 7.000.000 rows....

Some time later this one came in

I''ve created a test database with 10.000 tables each with 1 clustered and 1 nonclustered index.

...
After my creation the system tables (sysobjects, sysindexes, syscolumns) became wery fragmented :
DBCC SHOWCONTIG scanning ''sysindexes'' table...
Table: ''sysindexes'' (2); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 699
- Extents Scanned..............................: 92
- Extent Switches..............................: 697
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.61% [88:698]
- Logical Scan Fragmentation ..................: 51.50%
- Extent Scan Fragmentation ...................: 58.70%
- Avg. Bytes Free per Page.....................: 2765.9
- Avg. Page Density (full).....................: 65.83%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
...

I guess a premier support call to SAP support

Cheers,
Frank

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-12 : 13:54:02

hmm, it seems odd. SP_fixindex uses the dbcc dbreindex command, but this procedure is marked as system object. Only system procedures can use dbreindex against system tables.

Why it does not work? It should work in the same way as dbcc dbreindex, just make sure the database is running on single user mode.

Just a side note: I administer some SAP R3 with SQL Server 7.0 and SQL 2k, as well as on Oracle, Informix and DB2. Working with the couple SQL/SAP is 1000000 easier than with Oracle/SAP or Informix/SAP. SAP Support is useless as well. We never got ONE SINGLE ANSWER from them, but we have to rely on these infamous support because SQL Server is ISV licensed by SAP, so you cannot open a call directly to MS/Oracle/Informix/IBM. That is because I hate (it is not a hyperbole)SAP R/3.

regards,

Sérgio Monteiro
Trust in no Oracle

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-13 : 02:18:35

Not sure, if it was tried on single user mode ?

However, it seems to work for some system tables except sysobjects and sysindexes.
Here is the error message:
"Server: Msg 2598, Level 16, State 1, Line 1
Clustered indexes on sysobjects and sysindexes cannot be re-created.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any ideas?

Cheers,
Frank

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-13 : 02:21:27

ooh, and I forgot.

Although I do not admin SAP, I am doomed to use it for certain purposes. They have some very cool GUI stuff implemented in 4.6.b, but apart from this I hear you !!!

Cheers,
Frank

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-13 : 02:23:29

Once again too early and only one cup of coffee.

Here's the answer from SAP support

This is a Microsoft "feature" they told to me.
And really it is not at all an SQL issue.
Try to have a database with several hundreds or thousends of tables and you will get the same problem.
We have called MS as well as we are Premier Support Partners.
What they told as was as good as nothing:
"You have to copy your database into a new database. It will recreate your system tables"
A bull****! This is not really a customer friendly answer!
Making a copy of a database having several hundreds of GB (assuming you have as much of spare space), after test is everything is OK.
And all this during a maintenance window of one night!!!

Cheers,
Frank

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-14 : 01:16:11

quote:
Originally posted by shsmonteiro
Why it does not work? It should work in the same way as dbcc dbreindex, just make sure the database is running on single user mode.

The story concerning sp_fixindex is just a half true. It is doing dbcc dbreindex on all table having the object_id > 100 and dbcc dbrepair for the system objects.

Cheers,
Frank

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-14 : 01:58:59

Just for giggles I tried running the following on a test database..

*WARNING DO NOT TRY THIS 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

This completely destroyed the sysobjects table.

When run against the syscolumns it worked like a charm but sysindexes failed giving this error...

quote:
Could not find row in sysindexes for database ID 7, object ID 2, index ID 1. Run DBCC CHECKTABLE on sysindexes.

In summary... DON'T

DavidM

"SQL-3 is an abomination.."

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-14 : 02:36:30

Hey, that seems pretty cool!!

For some time now I am looking for a script to corrupt a db. Looks like a good candidate

Cheers,
Frank

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-15 : 00:01:43

You're right a5xo3z1. My mistake! I did not pay attention to the IF clause.

Anyway, have you tryed to use the dbcc dbrepair option?

I have tried it against a test DB:

alter database sice set single_user
go
dbcc dbrepair('northwind', repairindex, syscolumns, 1)

before it:

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Logical Scan Fragmentation ..................: 14.29%
- Extent Scan Fragmentation ...................: 85.71%
- Avg. Bytes Free per Page.....................: 3595.7
- Avg. Page Density (full).....................: 55.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

after it:

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 4
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 220.5
- Avg. Page Density (full).....................: 97.28%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

alter database sice set multi_user

regards,

Sérgio Monteiro
Trust in no Oracle

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-15 : 01:24:03

Thanks again, I've forwarded your results to my friend. I think he is about to give up on this.
Btw, how large are your SAP db's?

Cheers,
Frank

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-15 : 03:29:49

Hi Sérgio,

here's his answer:

Of cours I did.

See my previous notes.
sp_fixindex is doing dbcc dbreindex for all objects where object_id > 100 and dbcc dbrepair for all objects where object_id < 100.

And there I have seen that dbrepair do not reindex the sysobjects and the sysindex tables.

Cheers,
Frank

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-17 : 22:45:02

Hi a5xo3z1,

your friend is right, clustered index cannot be reindexed. BTW, 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."

PS: The biggest SAP we have using SQL Server is 150GB large, and still uses SQL 7.0 (we're migrating to 2k). This is 10% of our biggest SAP using Oracle, which uses some 1.5TB Or Informix using 1 TB.

regards,

Sérgio Monteiro
Trust in no Oracle

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-08-18 : 00:12:13

First, a couple of comments:

quote:
Originally posted by shsmonteiro
Just a side note: I administer some SAP R3 with SQL Server 7.0 and SQL 2k, as well as on Oracle, Informix and DB2. Working with the couple SQL/SAP is 1000000 easier than with Oracle/SAP or Informix/SAP. SAP Support is useless as well. We never got ONE SINGLE ANSWER from them.

I would have to disagree with you on this. Administering SAP on Oracle is a breeze. Every major SAP implementation runs on Oracle, so SAP's technical knowledge of SAP/Oracle interactions is staggering. Plus, the people that Oracle has working in germany supporting SAP are unbelievably knowledgeable (anyone know Juergen K?)

Not only that, but SAP makes available a tool called SAPDBA which makes all administrative tasks easy - easy like Enterprise Manager - but in a command line kind of way. I do not think this tool exists on other platforms, but I may be wrong.

You can also do some other administrative tasks through the SAPGUI. Administration through the sapgui using the db admin/monitoring tcodes is mostly the same across all platforms. However, SAPDBA does many more (and different) administrative tasks than can be accomplished through the sapgui.

quote:
Originally posted by shsmonteiro
PS: The biggest SAP we have using SQL Server is 150GB large, and still uses SQL 7.0 (we're migrating to 2k). This is 10% of our biggest SAP using Oracle, which uses some 1.5TB Or Informix using 1 TB.

Maybe this is why administering your SQL is so easy - that is like a toy database in Oracle (at 150GB atleast). We have sandbox and dev instances that are that size coming out our ears.

:)

Now for my 2 cents:

quote:
Originally posted by a5xo3z1
is there a way to reindex the sytem tables?

OK, back to the main question at hand. What if we went at this a different way and just re-created the master databse from scratch? You could do this in conjunction with sp_detach/sp_attach and you would be done.

You of course would have to script any objects in Master that weren't there by default. There might be some user issues, so you may need to recreate users and their permissions (no big deal though). You also may have to re-apply SP3a as well. I'm thinking that SAP probably has some special collation requirements as well, so that would have to be looked at too.

Have a look at this note for some information on the rebuildm tool:
http://support.microsoft.com/default.aspx?scid=kb;en-us;298568

Also check BOL

See SAP note 151603 for an SAP specific example

-ec

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-18 : 02:52:22

Now this is getting really interesting.

I've forwarded last two post to my friend!

Cheers,
Frank

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-18 : 20:56:32

eyechart,

quote:

quote:
--------------------------------------------------------------------------------
Originally posted by shsmonteiro
Just a side note: I administer some SAP R3 with SQL Server 7.0 and SQL 2k, as well as on Oracle, Informix and DB2. Working with the couple SQL/SAP is 1000000 easier than with Oracle/SAP or Informix/SAP. SAP Support is useless as well. We never got ONE SINGLE ANSWER from them.
--------------------------------------------------------------------------------

I would have to disagree with you on this. Administering SAP on Oracle is a breeze. Every major SAP implementation runs on Oracle, so SAP's technical knowledge of SAP/Oracle interactions is staggering. Plus, the people that Oracle has working in germany supporting SAP are unbelievably knowledgeable (anyone know Juergen K?)

Not only that, but SAP makes available a tool called SAPDBA which makes all administrative tasks easy - easy like Enterprise Manager - but in a command line kind of way. I do not think this tool exists on other platforms, but I may be wrong.

I have to strongly disagree with you. Speaking only and purely from DBA point of view, there is no point of comparisoin between Oracle and SQL Server. Now, I do know the SAPDBA tool that really makes your life easier. But, in some situations (mainly working with Informix) SAPDBA is a big pain. Even on Oracle, we've found some situations when SAPDBA generated a eorg script without the semicolon for create index, and you know the result. (The DBA working on this had full confidence in SAPDBA, not my case.). Also, Did you faced Oracle instaces that simply do not shutdown, because SAP leaves ghost connections? Or What do you think of Rule based queries bypassing the Oracle CBO, just because SAP has problems when Statistics are updated on tables with LONG columns. Just few examples.

Well, I know that Oracle people working in Germany to support SAP are knowledgeable. I really believe that, just as people working on IBM-Oracle EMEA Center of Competence are knowledgeable, and people working on SAP-MS Alliance are knowledgeable. Thats not the point. We have lots (really lots) of open issues with SAP, for which their answer was "restart machine... nop... apply note ... nop... do it... nop... do that...contact Oracle, MS or IBM". If at last we have to contact RDBMS vendors, why not go to them directly? Because SQL and Oracle are ISV licensed, and we have no CSI.

SAP is a great Application when people working with it realizes that sap is just it: an Application, and do not try to extrapolate it to becaome a RDBMS manager or OS manager.

UFFFFFF!!!! Breath.... Calm down... Calming down.... Cool