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 |
|
niranjankumark
Posting Yak Master
164 Posts |
 Posted - 2008-11-26 : 08:00:05
|
| While execute qry( multiple table and single ) which has sort , group by , distinct ...SQL SERVER creates temporary table space for every query ...how to find temporary table space ??? and how to incresae ???hope DBA cant assign temporay table space for each table .. on execution only( for overall database) he can define ... |
|
|
karthickbabu
Posting Yak Master
151 Posts |
Posted - 2008-11-26 : 08:27:18
|
| USE DBNameGODBCC SHOWCONTIG (TempTableName)GO==================================================== you realize you've made a mistake, take immediate steps to correct it. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 08:30:35
|
quote:
Originally posted by karthickbabu
USE DBNameGODBCC SHOWCONTIG (TempTableName)GO==================================================== you realize you've made a mistake, take immediate steps to correct it.
??????? DBCC showcontig is used to show fragmentation level of table,indexes. SQL does it operation in TEMPDB and it is usually dropped once it is done. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-26 : 08:44:57
|
| For tempdb, I think its best to prepare for the worst case. Give it enough space, with autogrow if you can at the very onset.Different queries would use up tempdb differently.How a query would use up temp space can be estimated from its plan.This should be helpful,Query operators that use tempdbSort (including distinct sort) The sort operator needs tempdb space to sort the full rowset (the rowset that goes through the sort operator). Hash match This operator has two inputs—one to build the hash table and other to probe it. Depending on its size, a hash table might be in tempdb. Look at the number of rows and row size returned by the first input operator. Spool (including table spool, nonclustered index spool) This operator requires that the full input rowset be stored in tempdb. To estimate the space required by each operator, look at the number of rows and the row size reported by the operator. To calculate the space required, multiply the actual (or estimated) number of rows by the estimated row size. Be aware that the estimated number of rows and estimated row size can be incorrect by a very large margin, due to incorrect statistics and other issues. Adjust your estimates based on your knowledge of the data. |
|
|
|
|
|
|
|
|
system create Temporary table space