Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pradeep_iete
Yak Posting Veteran
84 Posts |
 Posted - 2010-01-27 : 02:08:50
|
| Hi Friends, Suppose i have a query running as follows ; SELECT * FROM <table> ORDER BY table.Col1 ASCI have read that this query will be precessed in TEMP Database.Also i have read that when TEMP Database is used up by some process such as above query then all the resource of temp database gets locked.Does that mean that the Temp Database does not offer certain level of concurrency ?In ANy CASE , in run time how to observe that the TEMP Database resource is LOCKED or BLOCKING ?Correct me where ever i am wrong.Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 03:21:08
|
Its actually the TEMDB database, not "TEMP".If your query is huge it is possible that SQL Server will use TEMPDB to do the Order By, but mostly that will either use an Index on the actual table, or be done in memory.It is definitely NOT the case that only one query can use TEMPDB at once!The only case I know of where that can happen (which may be where your information came form) is:SELECT *INTO #TEMPFROM MyTableThis created the temporary table #TEMP (in TEMPDB) and places a CREATE TABLE Block on TEMPDB - thus another concurrent attempt to create a temp table in this way will be blocked. The problem with this is that the SELECT * FROM MyTable may take a very long time to execute, and the Create Table Block is maintained for the duration of the query. (Simple solution: pre-create the #TEMP table first  ) |
 |
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-01-27 : 03:47:11
|
Thanks Kirten for Guidancequote:
Originally posted by Kristen
Its actually the TEMDB database, not "TEMP".If your query is huge it is possible that SQL Server will use TEMPDB to do the Order By, but mostly that will either use an Index on the actual table, or be done in memory.
Kirten ,What is Defination of HUGE ? What are the parameters for HUGE Consideration so that ORDER BY takes place in TEMPDB ?ALSO Suggest me the command where i can observe wether TEMPDB is locked ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 04:37:46
|
| I don't know what HUGE is -when the query doesn't fit into memory, presumably.I expect the documentation has the details.I don't know how to observe if TEMPDB is locked. For that sort of thing I also look in the DOCs, rather than carrying such information in my head |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-27 : 07:56:34
|
quote:
Originally posted by Kristen
Its actually the TEMDB database, not "TEMP".If your query is huge it is possible that SQL Server will use TEMPDB to do the Order By, but mostly that will either use an Index on the actual table, or be done in memory.It is definitely NOT the case that only one query can use TEMPDB at once!The only case I know of where that can happen (which may be where your information came form) is:SELECT *INTO #TEMPFROM MyTableThis created the temporary table #TEMP (in TEMPDB) and places a CREATE TABLE Block on TEMPDB - thus another concurrent attempt to create a temp table in this way will be blocked. The problem with this is that the SELECT * FROM MyTable may take a very long time to execute, and the Create Table Block is maintained for the duration of the query. (Simple solution: pre-create the #TEMP table first )This issue was resolved in SQL 2005.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 08:53:21
|
| Thanks for that sodeep - I was thinking as much when I typed it.I still think better to pre-create the #TEMP table though. |
|
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-01-28 : 01:33:26
|
| Thanks Indeed Friends for important covering Stuff ,But now having digesting the Fact that "SELECT * INTO #TEMP FROM MyTable" does not LOCKS DOWN resource at TEMPDB behest, Can anyone of you illustrate the cases where TEMPDB gets Lock Down. I want to finish up with TEMPDB analysis with this thread only. |
|
|
|
|
|
|
|
|
Identify TEMP Database Based Locks