April 6, 2010 at 3:48 pm
Before I could just run select name form sysobjects where type = 'S'
In SQL Server 2005 things changed. Everything is run in the views. However, I have this dumb app which creates tons of empty objects by millions. It doesn't always clean up after itself. I need to only select the system tables and run update stats and reindex on them.
Please help!
thanks,
B.
if one wants it.. one will justify it.
April 6, 2010 at 9:46 pm
This will get you the system tables. I'll leave it up to you to do the reindexing / updating of stats.
select *
from sys.objects
where type = 'U'
and is_ms_shipped = 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 6, 2010 at 9:55 pm
WayneS (4/6/2010)
This will get you the system tables. I'll leave it up to you to do the reindexing / updating of stats.
select *from sys.objects
where type = 'U'
and is_ms_shipped = 1
That is probably the simplest and most straight forward way to get retrieve those objects.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 7:00 am
I get only dtproperty in return if I execute that. What about what use to be sysindexes, sysobjects, sysprotects, etc, etc.. can see any of those anymore in the not a view way? How can a maintenance be run only on the system tables then? (Feel sorry, I wasn't on that Microsoft SQL Server 2005 development group meeting when they were deciding the fate of sysobjects... no one would come out same way they came in :)))
I have about 800000 empty tables which this "dumb" app created and I believe that "sysobjects" must be re-indexed. If I just simply run a Maint wizard it creates a re-index job which runs across entire database and dies.. obviously, because there are so many objects in the database. Vendor who wrote this "piece of mind" doesn't immediately have a solution to clear them away. However, this app is critical and I want to make sure it is maintained properly to the best SQL Server can get.
so I thank you for the idea, but I still need help.
-B.
if one wants it.. one will justify it.
April 7, 2010 at 10:46 am
In sql 2005 those objects are still existent but they are views. The rest of the pertinent system objects that are the upgrade versions of those objects are also views.
However, for backward compatibility sake, change the query as follows:
Select * from sys.objects where is_ms_shipped = 1 and type = 's'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 10:55 am
boris_shvartsman (4/6/2010)
... I need to only select the system tables and run update stats and reindex on them. ...
OK, I'll be the dumb non-DBA here and ask the question: Can this even be done? I mean "update stats and reindex" on system tables in SQL 2005? Most of them aren't even tables are they?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 10:59 am
RBarryYoung (4/7/2010)
boris_shvartsman (4/6/2010)
... I need to only select the system tables and run update stats and reindex on them. ...OK, I'll be the dumb non-DBA here and ask the question: Can this even be done? I mean "update stats and reindex" on system tables in SQL 2005? Most of them aren't even tables are they?
Correct, most are views. There are a few that appear to be tables. I see re-indexing non-system objects as the first priority.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 11:14 am
thanks all for chatting about it.
So, if they are now all views, is the actual info stored in the meta-data somewhere? Where then? Views are only snapshots of tables.. So.. where are the tables.. if they even exist.
if one wants it.. one will justify it.
April 7, 2010 at 11:19 am
the tables are stored in the protected hidden resource db.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 11:21 am
if you don't mind being asked, where? and how is that "thing" is being re-indexed? That pretty much proves my "gut" feeling about it..
thanks a million for your help.
if one wants it.. one will justify it.
April 7, 2010 at 11:26 am
boris_shvartsman (4/7/2010)
if you don't mind being asked, where? and how is that "thing" is being re-indexed? That pretty much proves my "gut" feeling about it..thanks a million for your help.
Here is more info on the database.
http://msdn.microsoft.com/en-us/library/ms190940.aspx
Reindexing those tables is going to prove inconsequential - it's not like sql 2000. You will get the biggest bang for your effort by checking your user defined tables and indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 11:29 am
I agree 100%.. no doubt, but I worry that those "dead" tables in the sysobjects are somehow pulling the performance... reading your link..
if one wants it.. one will justify it.
April 7, 2010 at 11:34 am
boris_shvartsman (4/7/2010)
I agree 100%.. no doubt, but I worry that those "dead" tables in the sysobjects are somehow pulling the performance... reading your link..
Fair enough.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 11:36 am
CirquedeSQLeil (4/7/2010)
the tables are stored in the protected hidden resource db.
Well, some of it is, but a lot of it is not even there. Rather, much of it is in SQL Server internal data structures. The system catalogs and DMVs access these through special functions that are much like CLR TVFs (but are generally much better).
So they are effectively black boxes in that regard. And as for the actual tables, etc. in the ResourcesDB, you cannot change *anything* in it or you will void your warranty with MS (it's also really hard to do).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 11:38 am
RBarryYoung (4/7/2010)
CirquedeSQLeil (4/7/2010)
the tables are stored in the protected hidden resource db.Well, some of it is, but a lot of it is not even there. Rather, much of it is in SQL Server internal data structures. The system catalogs and DMVs access these through special functions that are much like CLR TVFs (but are generally much better).
So they are effectively black boxes in that regard. And as for the actual tables, etc. in the ResourcesDB, you cannot change *anything* in it or you will void your warranty with MS (it's also really hard to do).
Thanks for the clarification and the emphasis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply