Configuration Best Practices for SQL Server Tempdb–Multiple Files
Learn about best practices for configuring SQL Server tempdb for multiple data files.
Read MoreMost Popular SQL Server
Configuration Best Practices for SQL Server Tempdb–Initial Sizing
This three-part article presesnts best practices for configuring SQL Server tempdb, including where to place it, initial sizing and configuring multiple files.
Read MoreConfiguration Best Practices for SQL Server Tempdb–Placement
Learn best practices for where to place SQL Server tempdb.
Read MoreSQL Server Tempdb and Latch Contention
Use of SQL Server tempdb as temporary storage makes it likely to contain a disproportionate amount of creation and destruction of many small objects, which can lead to latch contention.
Read MoreSQL Server Table Variables Won’t Perform Quicker than Temporary Tables
There is a common misconception that table variables are in-memory structures and as such will perform quicker than temporary tables. Thanks to a DMV called sys.dm_db_session_space_usage, which shows tempdb usage by session, you can prove that’s not the case. After restarting SQL Server to clear the DMV, run the following script to confirm that your […]
Read MoreAbout the SQL Server Tempdb
The tempdb is the “scratch” database for SQL Server; it’s a temporary data store used by both applications and internal operations. It is does have some unique characteristics that affect how you use and manage it.
Read MoreSQL Server Latch Waits
Some SQL Server latches can come into contention with one another. This is intended and necessary as part of the need to serialize access. However, as with locking, this does raise the prospect of blocking, and consequently latch waiting.
Read MoreSQL Server Latch Types
There are hundreds of different types of SQL Server latches. Latch waits will occasionally show up in sys.dm_os_wait_stats, but you have to actively search for them.
Read MoreSQL Server Contention Indicators
Higher than normal wait stats does not in and of itself indicate a contention problem. You should look for the proportion of wait stats compared to others, and compare this to the throughput being achieved.
Read MoreMeasuring SQL Server Latch and Spinlock Contention
It’s important to recognize SQL Server latch and spinlock contention, and to be able to measure contention to determine the best possible tuning.
Read MoreRecognize Symptoms of SQL Server Latch and Spinlock Contention
If your transactions per second figure is dropping as you enable extra processor threads, and your average latch waits are increasing at a rate greater than the throughput, you may have latch contention.
Read MoreSQL Server Deadlocks
Ideally, despite locks, your database system will allow a lot of users at once, and each transaction will get in, make the change needed, and get out ; but locks mean blocking, and when transactions need to do multiple operations, this can lead to deadlocks.
Read More