May 19, 2010 at 9:26 am
I am working on tuning a database with hundreds flying tables coming in every day (please don't ask me why:sick:) and is curious about how system tables are doing.
Not surprise me, the system tables are huge, and several of them are larger than 1 G. The next thing coming to my head is how should we maintain those tables, so I tried to find more information on these tables. I can’t use DBCC SHOW_STATISTICS against system tables, so I query sysindexes table and found rowmodctr haven't been reset to 0 on these tables for a while since the number is really big.
Now, my question is how we maintain system tables.
No Signature
May 19, 2010 at 10:51 am
Generally, you don't. I've never heard of anyone defragging a system index. Since you're operating in 2005/2008, you can't get direct access to the tables anyway, you're just looking at views.
"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 11:00 am
I agree with Grant. You don't defrag the system tables. The system tables are in the hidden resource database where access is limited.
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 11:24 am
CirquedeSQLeil (5/19/2010)
The system tables are in the hidden resource database where access is limited.
The definitions for many system views procs and functions are in the system resource DB, but the actual system tables (eg sysrowsetcolumns, sysrowsets, sysallocunits, etc) are in the user/system databases, just well hidden.
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 11:40 am
Thanks Gail. Well hidden is a bit of an understatement.:-)
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 11:59 am
Not really that well hidden.
SELECT * FROM sys.objects WHERE type = 'S'
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 12:02 pm
I understand MS doesn't want DBA to manually working on system tables. But how are these tables maintained?
No Signature
May 19, 2010 at 12:11 pm
The problem I am facing is that the system tables are too big. I don't know whether they are fragment or not, and I don't know whether I should do something or leave them there.
No Signature
May 19, 2010 at 12:14 pm
For the objects of type 'S' in each of the user databases, they really don't need and shouldn't need much maintenance.
For the additional objects such as dmvs, some of the data is pulled from the afore mentioned system tables in the user databases and some of the information is pulled from metadata. That data is reset with each SQL Server restart or database offline / online event.
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 12:15 pm
I am interested to know what are those system tables.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 19, 2010 at 12:15 pm
Yang-703993 (5/19/2010)
The problem I am facing is that the system tables are too big. I don't know whether they are fragment or not, and I don't know whether I should do something or leave them there.
Could you post your query and the results that is helping you to determine that your system tables are too big? How big is too big?
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 12:29 pm
Sample:
Table NameRow Count
sysobjvalues3211810
syshobtcolumns4000161
sysrowsetcolumns4000413
No Signature
May 19, 2010 at 12:32 pm
Based on the sample data, I might be inclined to think that those are a bit high. Please post the query you are using to determine those sizes.
Also, how large is your database overall?
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 12:41 pm
create table #tablesize
(
tablenamenvarchar(128),
rowchar(11),
reserved varchar(18),
datavarchar(18),
indexsize Varchar(18),
unusedvarchar(18)
)
DECLARE @Tablename sysname
DECLARE @sql nvarchar(200)
DECLARE Table_Cursor CURSOR FOR SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'S'
ORDER BY [name]
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @Tablename
WHILE @@fetch_status = 0
BEGIN
set @Tablename= 'sys.'+@Tablename
set @sql='sp_spaceused ''' + @Tablename + ''' ,true'
insert into #tablesize
EXEC (@sql)
FETCH NEXT FROM Table_Cursor INTO @Tablename
END
Close Table_Cursor
DEALLOCATE Table_Cursor
select
tablename as [Table Name],
row as [Row Count],
cast(substring(reserved, 1, len(reserved)-3) as float)/1000 as [Reserved (MB)],
cast(substring(data, 1, len(data)-3) as float)/1000 as [Data (MB)],
cast(substring(indexsize, 1, len(indexsize)-3) as float)/1000 as [Index Size (MB)],
cast(substring(unused, 1, len(unused)-3) as float)/1000 as [Unused Space (MB)]
from #tablesize order by 3 desc
drop table #tablesize
No Signature
May 19, 2010 at 12:51 pm
The rowcount piece is accurate in your script. The calculation of size is not. The calculation you use shows a difference of 20% in size for my system tables. You should be dividing by 1024, not 1000.
What size is your database?
How many 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
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply