Reindexing the system tables

Question

Post reply

Reindexing the system tables

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

To be honest, I don't like SAP.

It is definitely an overkill to most companies, apart from being far too expensive. But it's some kind of standard.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

So do I.

But my customers want it so we are delivering SAP.

On any kind of platform, on any kind of DB as we are an outsourcing company.

But our preference is WINTEL with SQLServer.

That one gives a very nice performance with an acceptable price.

And really on Windows this is the only DB wich is natively developped on that OS, all the others are just ported and you can feel the UNIX features behind the scene.

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
So do I.
But my customers want it so we are delivering SAP.
On any kind of platform, on any kind of DB as we are an outsourcing company.

...and the customer is king!

quote:
And really on Windows this is the only DB wich is natively developped on that OS, all the others are just ported and you can feel the UNIX features behind the scene.

in addition to this, I think that in the long run no other db will perform better, because MS develops the OS on which they run. No other vendor has that deep insight

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

I looked around and couldn't find anything other than them saying to drop and recreate. I even copied sysindexes and then tried to delete all of the rows and reinsert and that did not work.

Good luck.

Tom

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Gabor,

not sure if you've receive my mail. So here's the answer from another forum

...

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.

...

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

So guys (special thanks to Frank and Tom),

It seems there is no real answer.

By the way Frank ich habe dein Mail erhalten. Danke)

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.

Of cours you have to be in single user more to issue a dbcc dbrepair command (therefore to issue an sp_fixindex too)

This is the farest I could come.

On other hand I have the same opinion about SAP and administer SAP on different systems: On SQL is the easiest (and one of the fastest)

Gabor

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi Gabor,

just forwarding another reply

...

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

...

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

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.

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hi Gabor,

forwarded!

...

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."

...

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Hi Gabor,

another forward

quote:
Originally posted by eyechart
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

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 11

But Frank,

THIS IS A PRODUCTIVE DATABASE WHERE OVER 1000 PEOPLE ARE CONNECTED TO!!!

I won't play with rebuilding the master database within my maintenance windows.

It's is just too risky to do that as a normal maintenance task.

So after having read and tested several possibilities this is what I will do:

- put the database in single user mode

- reindex all the system tables (excepting sysobjects and sysindexes) with sp_fixindex or dbcc dbrepair

- put dbcc pintable on sysobjects and sysindexes

By doing so I will lose a bit more memory but at least the execution time wil remain constant.

Anyway here is hint for the Yukon developers:

Guys, you should think on the maintenance of the system tables too!

Your database can rum more them 10 tables/database!!! YES! So be prepared!

Bye,

Gabor

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

quote:
THIS IS A PRODUCTIVE DATABASE WHERE OVER 1000 PEOPLE ARE CONNECTED TO!!!

hey, that's a forward from another forum

quote:
I won't play with rebuilding the master database within my maintenance windows.
It's is just too risky to do that as a normal maintenance task.

I remember somewhere I read an advice to Server admins:

'Live fast, overclock your server'

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff W SSCertifiable Points: 5207 More actions · Answer 13

I too have SAP/R3 on SQL Server 2000 (was 7.0).

Even on some of my other RDBMS's I have a lot of fragmentation in my system tables. I can see where there would be problems with rebuilding indexes on sysobjects and sysindexes. Seems like a deadlock situation to me 🙂

Anyway, There needs to be a way without recreating the database. That's just not right!! That sysindexes table is read everytime there is a read in the database, and it goes for that first IAM.

Just thought I would share my opinion 🙂

"Keep Your Stick On the Ice" ..Red Green

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 14

Jeff,

No deadlock if you are in single user mode (as dbcc dbrepair and sp_fixindex oblige you to do anyway).

I hope in the Yukon version they will think about this.

Bye

Gabor

Bye
Gabor

Antares686 SSC Guru Points: 125444 More actions · Answer 15

I agree you are supposed to be putting the DB in Single User Mode or DBO Use Only for some of the indexes to work. However, I did notice when I tested this that there are some indexes for some of the system tables it makes no difference on at all. ALso you cannot set master that way except when you start the SQL Server service in single user mode using the -m option.