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 More
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 More
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 More
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 More
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 More
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 More
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
When more than 5,000 locks are taken out on the rows or pages of a particular table within a single T-SQL statement, lock escalation is triggered. Understanding lock escalation can help you in performance tuning.
Read More
Nonrepeatable reads can happen when multiple sessions are occurring at once in your SQL Server database instance. Learn more.
Read More
Database concurrency ensures that when multiple operations are occurring at once, the final result is still in agreement. Dirty reads can happen when multiple sessions are occurring at once in your SQL Server database instance.
Read More
Database concurrency ensures that when multiple operations are occurring at once, the final result is still in agreement. Lost updates can happen when multiple sessions are occurring at once in your SQL Server database instance.
Read More
SQL Server query plans in all forms are read bottom right to top left. Learn more.
Read More