Re: temp tables and locking issues
hkvats_1999_at_yahoo.com
Date: 06/25/04
- Next message: hkvats_1999_at_yahoo.com: "Re: Transact Max Statement"
- Previous message: hkvats_1999_at_yahoo.com: "Re: Import multiple MDB files"
- In reply to: Zidgan: "temp tables and locking issues"
- Messages sorted by: [ date ] [ thread ]
Date: 25 Jun 2004 02:05:46 -0700
Hi Zidgan,
Your post made me think, how we can avoid the use temporary tables....
and i tried to finds some articles for exact explainations....
Here is my finding after reading several articles on web and testing
these in SQL Server .....
When you create a temporary tables......... following steps taken by
SQL Server...
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read
activity)
5) DROP TABLE (write activity)
4) Release the locks
Now it can be concluded....
> Is the tempdb only locked during the time the table is being created?
- DB Locked
>
> Is it locked during the entire time of the Select Into process?
> DB Lockaed
>
> Is the tempdb locked during any transactions using it?
> DB Locked
for details you can refer
http://www.sql-server-performance.com/jg_derived_tables.asp article...
Regards
Hari Sharma
Zidgan <rcloninger@triad.rr.com> wrote in message news:<4ej1d09ceucb3hulhp0jme5ld8p09r1nti@4ax.com>...
> 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: hkvats_1999_at_yahoo.com: "Re: Transact Max Statement"
- Previous message: hkvats_1999_at_yahoo.com: "Re: Import multiple MDB files"
- In reply to: Zidgan: "temp tables and locking issues"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
