May 19, 2010 at 12:57 pm
You are right. However, what should I do with these tables?
No Signature
May 19, 2010 at 1:02 pm
There really isn't anything "to do" with the tables. If you have lots of objects in your database, you're going to have lots of rows in the system tables. As long as you're passing consistency checks, there's little to be done or worry about with the system tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2010 at 1:05 pm
These tables will increase in size based on your database and the number of objects in your database.
You don't need to do anything with these.
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
May 19, 2010 at 1:12 pm
I don't know... If you see data like this
Table Namesysobjvalues
Row Count3213517
Reserved (MB)5740.40625
Data (MB)4807.484375
Index Size (MB)22.7890625
Unused Space (MB)910.1328125
No Signature
May 19, 2010 at 1:41 pm
Yang-703993 (5/19/2010)
I don't know... If you see data like thisTable Namesysobjvalues
Row Count3213517
Reserved (MB)5740.40625
Data (MB)4807.484375
Index Size (MB)22.7890625
Unused Space (MB)910.1328125
Yeah, 3 million rows could be a bit off-putting, but it's still a system database. I don't know precisely what this one does, but BOL says it maintains properties on all other objects, so it's going to be dependent on how many objects are in your database. You said you have a lot, so it's no surprise that these tables are large.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2010 at 1:55 pm
I wonder if it may be out of date statistics.
You could try running an sp_updatestats. This may have an effect on your results. Also, I am curious if you are seeing a lot of tables being created and dropped in this database. There was a significant jump in rowcount for sysobjvalues between your two runs.
That is a couple of things you could try (sp_updatestats and find if you have a lot of table creates occurring).
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
May 19, 2010 at 2:11 pm
As I mentioned, hundreds tables fly in and drop off each day, and that is why I concern about system tables. I know this is out off relational database concept, but I can't do anything on database design part at this point(Maybe later, I hope). So I hope I can find a way to 'help' system table to be more efficiency.
BTW, sp_updatestats doesn't work against system table.
No Signature
May 19, 2010 at 2:13 pm
Do you have a reason to think the size of the system tables is adversely effecting DB performance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2010 at 2:19 pm
Yang-703993 (5/19/2010)
As I mentioned, hundreds tables fly in and drop off each day, and that is why I concern about system tables. I know this is out off relational database concept, but I can't do anything on database design part at this point(Maybe later, I hope). So I hope I can find a way to 'help' system table to be more efficiency.BTW, sp_updatestats doesn't work against system table.
I just ran sp_updatestats to verify, and I do show in my results that it did run against the system tables. Also, here is an article that backs that up. http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part2_p1.aspx
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
May 19, 2010 at 2:20 pm
GilaMonster (5/19/2010)
Do you have a reason to think the size of the system tables is adversely effecting DB performance?
My inclination would be that the 100's of tables added daily would be more of a problem than the large system tables.
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
May 19, 2010 at 2:25 pm
CirquedeSQLeil (5/19/2010)
GilaMonster (5/19/2010)
Do you have a reason to think the size of the system tables is adversely effecting DB performance?My inclination would be that the 100's of tables added daily would be more of a problem than the large system tables.
and I'd personally be more concerned about contention on the allocation structures than table scans on the system tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2010 at 2:51 pm
CirquedeSQLeil (5/19/2010)
I just ran sp_updatestats to verify, and I do show in my results that it did run against the system tables. Also, here is an article that backs that up. http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part2_p1.aspx
Thanks, Jason. I think update statistics will help a bit.
No Signature
May 19, 2010 at 2:54 pm
GilaMonster (5/19/2010)
CirquedeSQLeil (5/19/2010)
GilaMonster (5/19/2010)
Do you have a reason to think the size of the system tables is adversely effecting DB performance?My inclination would be that the 100's of tables added daily would be more of a problem than the large system tables.
and I'd personally be more concerned about contention on the allocation structures than table scans on the system tables.
I totally agree. I hope I have ability to convince the development team to redesign database. Please pray for me, thanks.
No Signature
May 19, 2010 at 2:56 pm
You're welcome and good luck.
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
May 19, 2010 at 2:59 pm
Gail, how do I monitor allocating structures activities and their empact on I/O performance? Thanks in advance.
No Signature
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply