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
There are several ways to view SQL Server query plans, but perhaps the easiest is to view the graphical plan using SQL Server Management Studio (SSMS).
Read More
To find performance issues caused by long lookup times, you can look into the contents of the SQL Server DMV sys.dm_os_memory_cache_hash_tables. Learn more.
Read More
When SQL Server Query Optimizer comes up with a plan, it caches the plan using parametrization options to ensure it can be reused as widely as possible.
Read More
Statistics provide critical information needed by SQL Server when performing query optimization, including details about the data and what the data looks like in each table within the database.
Read More
Learn how SQL Server Query Optimizer chooses parallel plans–any plan in which the Optimizer splits as applicable operator into multiple threads–and how this can impact performance.
Read More
Get a high-level overview of the secret inner workings of the SQL Server 2012 Query Optimizer.
Read More