temp tables and locking issues

From: Zidgan (rcloninger_at_triad.rr.com)
Date: 06/16/04

  • Next message: Micaela: "Unicode chars" 
    Date: Wed, 16 Jun 2004 23:01:19 GMT

    I am a developer and am by no means an expert in SQL. I have used temp
    tables frequently, especially in nested stored procedures against our
    SQL Server 2000 database. However, recently I have found many
    articles on the web warning about performance issues with temp tables.

    One concern is locking. Each article seems to explain it differently.
    One article simply said not to use them because the tempdb is locked
    while the "select into" was running. Another article seemed to imply
    the tempdb is locked only while the database is trying to define the
    temporary table on a SELECT INTO command. Still another said it was
    locked during a transaction. Today I tried a number of tests trying
    to ascertain what was locked and when. So far I have not been able to
    validate any of these things.

    I would appreciate a clarification on this, and possibly some sample
    code, perhaps against the Northwinds database, that could show the
    effect of the locking. If it is a problem, I would like to
    demonstrate it to the other developers. The questions are:

    Is the tempdb only locked during the time the table is being created?

    Is it locked during the entire time of the Select Into process?

    Is the tempdb locked during any transactions using it?

    I appreciate advice from the experts. Thanks

  • Next message: Micaela: "Unicode chars"

    Relevant Pages

    • Re: tempdb size question
      ... TempDB is the soul of SQL and everything you can do to gain performance, ... For example, SQL OLTP with 4 processors, I create five files, 4 with the same ... database size. ... TEMP be in total? ...
      (comp.databases.ms-sqlserver)
    • Re: TempDB Concurency
      ... Actually we're still on SQL 7...this issue is taking DBA and development time ... this one because when it starts acting up ALL of the processes using tempdb ... >> temp tables without any true blocking activity, ... > done when a migration to SQL 2k5 will be warranted. ...
      (microsoft.public.sqlserver.server)
    • Re: TempDB Concurency
      ... with the multiple files for Tempdb and probably without the trace flag. ... It has a section near the bottom on proper use of temp tables in a sp to ... > Actually we're still on SQL 7...this issue is taking DBA and development time> away from the migration. ...
      (microsoft.public.sqlserver.server)
    • Re: SQL Server 2000, Raid 1 or Raid 5 for Applications
      ... This depends entirely on the degree of use of tempdb. ... "By segmenting tempdb onto its own RAID ... Andrew J. Kelly SQL MVP ... drives RAID 10 with user files and TempDB data files ...
      (microsoft.public.sqlserver.setup)
    • Re: Tempdb after restart
      ... If SQL has to auto-grow tempdb there are severe ... performance penalties. ... Microsoft SQL Server MVP ... I support the Professional Association for SQL Server ...
      (microsoft.public.sqlserver.server)
    Loading