How do we maintain system tables?

  • 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

  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I understand MS doesn't want DBA to manually working on system tables. But how are these tables maintained?

    No Signature

  • 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

  • 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

  • I am interested to know what are those system tables.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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

  • Sample:

    Table NameRow Count

    sysobjvalues3211810

    syshobtcolumns4000161

    sysrowsetcolumns4000413

    No Signature

  • 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

  • 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

  • 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