Search
Subscribe
Categories
- Backup and Restore
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- Deadlocks
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Jobs
- Links
- Locking
- Memory
- Monitor Performance
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Cachestore
- SQL Error Logs
- SQL Optimizer
- SQL problems
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- Virtualization
- Wait Stats
Recent Posts
- How to check Operating System type with Powershell
- ALTER TRACE and developers
- Predict ALTER INDEX REORGANIZE finish time
- Cannot connect to SQL Server
- Timeout occurred while waiting for latch: class 'LOG_MANAGER'
- The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
- How to create a SQL Server Security Audit
- Cannot use Large Page Extensions: lock memory privilege was not granted
- Compare two files with Powershell
- Export-CSV Powershell
When you plan for TempDB configuration note the TempDB purpose.
User objects e.g tables, indices, table variables. Scope (user session or routine)
Internal objects .e.g join work files Scope (statement)
Version Stores to support row versioning
Some of the features using the TempDB are:
Cursors
Databse Mail
DBCC CHECKDB
Index Rebuild with SORT_IN_TEMPDB
Queries
Temp tables
UDF
-Create an IO profile of TempDB . Understand workload requirements.
-Placement of TempDB .Locate the TempDB on a separate disk to user databases.
-Place the TempDB on the fastest IO subsystem possible in your environment
-Sizing . Configure the TempDB size accurately particularly if performance decrease can’t be tolerated .
-Recovery Model , keep it SIMPLE.
-Set to Autogrow , see comment above re:Sizing. What rate of growth?TempDB grows raplidly , setting small growth rate may cause problems. Normally I set the auto-grow rate to 10% of the initial size. That does not mean the growth is set at 10% , it means that that if the initial size is 500 MB , the growth is set at 50 MB
-Multiple data files for TempDB – 1 data file per CPU. In a tightly consolidated environment , 1 tempDB file per CPU may be a luxury.
-Define the storage IO capability to the CPU processing capacity
-Split TempDB into multiple files. Place the TempDB files on different drives. Depending on the disk alignment architecture – each drive is assigned to a separate LUN . If using SVC – there are different considerations. Speak to your SAN administrator about the storage architecture .
- Configure the split TempDB files as equal sizes.
I posted notes on TempDB and Solid State Drives considetrations.
Source:Jack Vamvas (http://www.sqlserver-dba.com)
Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |