Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000
Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value” everytime. E.g. For INSERT it does not log after value on log as that will be any way logged in the TempTable. Similar for DELETE as It does not have to log After value as it is not there. This is big improvement in performance in SQL Server 2005 for TempDB.
Some other improvement in File System of operating system. (I am not listing them as it does not apply to SQL concepts).
Improvement in UP latch contention. Each file in TempDB is filled up with proportion with the free spaces it reduces the latch contention in TempDB while many users are accessing TempDB.
Worktable caching is improved along with memory management and pre-allocation resources. SQL Sever 2005 caches all the temporary objects (table variables, local tables). If table is smaller than 8 MB it is cached in the TempDB system Catalog, so when it is created again it is there and reduces the work for TempDB. If it is bigger than 8 MB TempDB uses background differed drop. When large object(temporary table) is dropped in TempDB, background process drops it, so main process does not have to wait for this to execute and complete. This improves performance for the application time.
TempDB is used by following feature of SQL Server 2005:
- Query
- Triggers
- Snapshot isolation and read committed snapshot (RCSI)
- MARS
- Online index creation
- Temporary tables, table variables, and table-valued functions
- DBCC CHECK
- LOB parameters
- Cursors
- Service Broker and event notification
- XML and LOB variable
- Query notifications
- Database mail
- Index creation
- User-defined functions
Best Practices and Recommendations for TempDB in SQL Server.
Analyze the existing workload and adjust the space for projected concurrent activities in SQL TempDB.
SQL Server does not cache the temp table if it is created as part of dynamic SQL.
Perform index maintenance and update statistics on TempDB as well, even thought objects in TempDB are not permanent.
Set Auto Grow to ON for TempDB.
Instant file initialization improves the performance of auto grow operations in TempDB.
Create TempDB database on other disk drive than other database. This will improve performance for database as different disk controller will improve the performance for disk input/output.
Reference : Pinal Dave (https://blog.sqlauthority.com) , MS TechNet Working with tempdb in SQL Server 2005
5 Comments. Leave new
Hi, I am wanting to execute a remote proc that is stored under a userid on another server ,from the SQL SERVER 2005 on my local. What is the sql script to do this.
hi,
i encounter one problem. well i have created a databse sfimport and its all .ldf and .mdf files are created on f drive and i have made the mode “simple” .There are approximately 10crores records in this db.
and now i am trying to update these records.
but the problem is its increasing the ldf file of tempdb database which is syatem database becuse of which my space on c drive is geeting very less and the query for update are geeting stop by giving the below error
“sg 1105, Level 17, State 2, Line 1
Could not allocate space for object ‘dbo.SORT temporary run storage: 152711031947264’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
”
but i dont know why , i am not doing anything with tempdb all my things are done on sfimport database which is on f drive . pls tell me the solution of this problem i am not able to understand this
pls reply me as soon as possible
I have to access temp table(created in one stored procedure)in another Sp, so for this can i use tempdb..#temp or global temp tables i.e. ##Temp. which one is efficient, because i guess, access using temdb..#Temp is as good as creating one connection. Please revert back to me.