When coming from a programming background, it’s natural to want to frame conditional operations within the familiar if.. else constructs. This happens in a lot of SQL code I have worked with (and I used to be a contributor also). The technique below is based on a common scenario. Say you want to insert new [...]
Archive of posts filed under the Concurrency category.
SET TRANSACTION ISOLATION LEVEL
This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation [...]
Understanding SQL Server Deadlocks
In order to fix deadlocks, it’s critical to understand why they occur. I’ve gotten some push back sometimes from readers when I write articles that do not give cookie cutter solutions, but the “teach a man to fish” adage couldn’t be more true for deadlocks. All of it revolves around locking, which is a central [...]
Configuring Parallelism
Parallelism is a powerful feature in SQL Server designed to allow greater bandwidth for high impact queries. Most of the time however, I’ve seen too many queries qualifying for parallelism and essentially bringing the SQL Server box to it’s knees. To understand parallelism, we need to understand SPIDs (Server Process ID’s). These SPID’s act essentially [...]
Turn On Snapshot Isolation to Use Version Store
The following statement turns snapshot isolation on: ALTER DATABASE sqlserverplanet SET ALLOW_SNAPSHOT_ISOLATION ON GO ALTER DATABASE sqlserverplanet SET READ_COMMITTED_SNAPSHOT ON Once snapshot isolation is on, rows that have been modified will fill the version store. To find the amount of space the version store is using, execute: SELECT version_store_in_kb = version_store_reserved_page_count*8192/1024 FROM sys.dm_db_file_space_usage Popular search [...]
