SQL Server Performance

tempdb gone wild

Discussion in 'ALL SQL SERVER QUESTIONS' started by tcronines, Jun 5, 2014.

  1. tcronines New Member

    supporting 3rd party app, over weekend new patch put in. Performance took a dive, tempdb went from about 8 gig to stuck at 175 gig, any ideas where best place to look might be?
  2. davidfarr Member

    tempdb "stuck" at 175 GB ? Is your tempdb not configured for unrestricted growth or did you just run out of disk space ? Did the patch update complete successfully ?

    tempdb is deleted and recreated each time the SQL Service starts, so restarting your SQL service will shrink it down again.

    Consider the following points regarding what tempdb is actually storing, as per MSDN notes;
    The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
    •Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
    •Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
    •Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    •Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    If your 3rd party app or patch update was incorporating a large result that impacts on any of the points above, then your tempdb will grow accordingly.

    If huge tempdb growth is occuring persistently on a daily basis then the 3rd party app is likely to be using a 'reckless' amount of temporary objects for reporting or other functionaliaty. You would then either need to address the matter with the app vendor or restart your SQL Service daily to reset tempdb.
  3. tcronines New Member

    actually it was the vendor using a table variable inside of a SP. I removed the table variable and put in a #table. Went from 112000 logical reads to 12. Just found another instance of this in another sp, table variables are the evil empire
  4. davidfarr Member

    I'm somewhat surpised that the #table would make such a difference, since both @tables and #tables are stored in tempdb. Keep in mind however that some client applications, if developed using Link or Entity Framework, may have a problem executing stored procs containing #tables as opposed to @tables and return errors due to the change.
  5. tcronines New Member

    no this was a straight foward sp called by the application. I saw this before at my old place, I have seen some prophets like Ozar warn against them starting to see why

Share This Page