Tempdb is instance wide Temporary workspace that SQL Server uses for various operations. Tempdb is created from Model Database whenever the SQLSERVER service is started/restarted. Tempdb has simple recover model. It is a rule of thumb that you should never keep tempdb on the same location where your user database is stored. It is always recommended that tempdb should locate in a high performing disk subsystem (preferably RAID 10 or RAID 0). It is also to be configured with enough size for better performance, so that system no need to do the space allocation dynamically. You can also create multiple tempdb datafiles to minimize on IAM and SGAM and there by improve the performance.
Very common operation which consume tempdb are appended below :-
(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.
To relocate Tempdb Follow these steps
(a) Alter the file path by following command
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
GO
(b) Restart the service and run sp_helpfile tempdb to confirm the above action
Refer :
Very common operation which consume tempdb are appended below :-
(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.
To relocate Tempdb Follow these steps
(a) Alter the file path by following command
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
GO
(b) Restart the service and run sp_helpfile tempdb to confirm the above action
Refer :
Tempdb in Sql Server 2005 -http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Shrinking -- http://support.microsoft.com/kb/307487
Concurrency enhancements for the tempdb database --
http://support.microsoft.com/kb/328551
Posts
No comments:
Post a Comment