In SQL Server, a block occurs when a process has a lock on a resource (a table or row in a database) that will not allow other processes to use that resource and another process’ attempts to use it. This behavior is a normal part of database operations and will impact the number of users that you can support concurrently. When blocking is minimized and server resources are sufficient enough to have a larger number of users connected and functioning, this is called high concurrency. When a high level of blocking occurs or server resources are insufficient so that you can support very few users, this is called low concurrency.
Your goal will be to have short block durations. A threshold often specified is one second. If your blocks last for more than one second, you will increase contention and lower concurrency. Users will feel that the system is sluggish or unresponsive. You can analyze blocking in your server by querying the sys.dm_exec_requests database management view (DMV). Look at the blocking_session_id column and seek for values greater than 0. When you find a row that meets this filter, the value in the blocking_session_id column is the SPID of the blocking process. At this point, if the process is very problematic, you can stop the process with the KILL SPID Transact-SQL command, where SPID is replaced with the numeric SPID value shown in in the query results.
You can also use the Activity Monitor to stop a blocking process. To do this, right-click on the server instance you wish to view and select Activity Monitor in the Object Explorer in SQL Server Mangagement Studio (SSMS). Find the process you want to stop, right-click it and select Kill Process. You can also right-click a connection and select Details to see the actual code being executed.
Unlike the blocks, which will go away eventually when the blocking process finishes with the tasks it is performing, deadlocks must be resolved by force – either random force or structured force. A deadlock occurs when process one has an exclusive lock on resource one and process two has an exclusive lock on resource two. Then process one tries to access resource two during the execution of the transaction that has resource one locked, and process two tries to access resource one during the execution of the transaction that has resource two locked. Do you see the picture? This could be called an infinite lock, but SQL Server will kill the process it feels has the lowest priority (random force). You can also specify the priority of a process so that SQL Server will give preference to it in a deadlock scenario (structured force). You use the SET DEADLOCK_PRIORITY statement to do this.
Understanding and managing locks, blocks and deadlocks is a key factor in SQL Server performance management.
By: Tom Carpenter has been working in IT for almost 20 years and has been training since 1997. He has written several books on various certification technologies such as network infrastructure technologies as well as server administration, SQL server, and will soon be releasing a book on SharePoint. Tom has taught over 30,000 students and currently teaches Windows 7 administration courses, Server administration courses, and Microsoft SQL Server administration courses.