SQL Server Performance

Tempdb Table Sizes

Discussion in 'General Developer Questions' started by MrTim, Jul 27, 2006.

  1. MrTim New Member

    Is is possible to get the sizes and/or number of rows of tables in the tempdb? I can see them in sysobjects, but sp_spaceused returns error 15009 (object does not exist).

    I read in a Sybase article the that some of numbers on the end of the table name relate to the SPID (maybe in hex). I haven't been able to work out if the same applies to SQL Server 2000. What I'd like to do is work our which process is causing the tempdb to grow so much, so either the SPID or the table name itself of the oversized tables will give some clues.

    Anyone got any ideas?
  2. FrankKalis Moderator

    You're correct, a temp table #t has internally a completely different name.
    How did you call sp_spaceused? Anyway, I would probably run a trace against tempdb to get some hints where to dig deeper.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  3. MrTim New Member

    I've tried something like:-

    select * from sysobjects
    where xtype = "U"

    exec sp_spaceused '#temp_det___________________________________________________________________________________________________________00000008DE95'

    Server: Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 76
    The object '#temp_det___________________________________________________________________________________________________________00000008DE95' does not exist in database 'tempdb'.
  4. FrankKalis Moderator

    Try this:


    USE tempDB
    EXEC sp_MSforeachtable 'sp_spaceused ''?'''

    This sp_MSforeachtable procedure is undocumented and if you are planning to use it on anything else but problem solving, you should keep in mind, that MS reserves the right to alter, modify or remove undocumented stuff at the service pack level. However, you can look at the code of that procedure and mimic it. But you'll find ugly use of dynamic sql and cursors. So, another method would probably look like:


    CREATE TABLE #t (c1 INT)
    CREATE TABLE #t1 (c1 INT)

    DECLARE @myname SYSNAME
    DECLARE @RowCnt INT

    SELECT name INTO MyTable
    FROM sysobjects
    WHERE xtype = 'U'

    SELECT TOP 1 @myname = name
    FROM MyTable
    WHERE [name] != 'MyTable'

    SET @RowCnt = @@ROWCOUNT
    WHILE @RowCnt > 0
    BEGIN
    EXEC sp_spaceused @myname
    DELETE FROM MyTable WHERE [name] = @myname
    SELECT TOP 1 @myname = name
    FROM MyTable
    WHERE [name] != 'MyTable'
    SET @RowCnt = @@ROWCOUNT
    END

    DROP TABLE #t, #t1, MyTable


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  5. MrTim New Member

    Interesting, but still returns error 15009 when used on tables in tempdb.
  6. FrankKalis Moderator

    Aargh, I see...<br /><br />Sorry fo that, need to think about it for a while. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  7. MrTim New Member

    It seems you can get info for temp tables you create yourself in the same session, but not other people's (which I want I would like to do).

    USE mydb
    CREATE TABLE #t1(a INT)

    USE tempdb
    EXEC sp_spaceused '#t1' --works fine because it was created in this session

    If you create another table called #t1 in another session, sp_spaceused won't report on it.

    I really want to get info on all tables in the tempdb to see what is causing problems.



  8. FrankKalis Moderator

    Dang, that was the missing hint given with a sledgehammer.

    From BOL: "Local temporary tables are visible only in the current session"
    And since sp_spaceused can be executed by anyone in public, it's likely that it will work only on your own temp tables.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  9. FrankKalis Moderator

    Hm, this is likely to be a kludge, but for me here it works and reports also on tables created in another session. <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT so.[name]<br /> , used AS "# of Pages"<br /> , rows AS "# of Rows"<br /> , (used * <img src='/community/emoticons/emotion-11.gif' alt='8)' /> / 1024 AS "# of MB"<br /> FROM sysobjects so<br /> JOIN sysindexes si<br /> ON so.id = si.id<br /> WHERE so.xtype = 'U'<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  10. MrTim New Member

    A kludge it maybe, but it works. You're a genious. I hadn't even thought of using sysindexes. Seems it also works even for temp tables without an index. I should now be able to figure out which processes are causing problems.

    Many thanks.
  11. FrankKalis Moderator

    Glad we've sorted it out. It's too hot for such kind of problems anyway. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Share This Page