SQL Server Performance Forum – Threads Archive

Blocking issues

What are the steps to know that a blocking was happned and wht are the steps to resolve it

–Ahhh, such a vast question is one sentense, well SQL Books online give a very good article on it..Its really good thats why I dare to paste whole content <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If you wish to know about locking information happening on your production/ dev box, then use: - USE master EXEC sp_lock OR select * from master..sysprocesses where blocked<>0 – Querying directly on system tables are not recommended. Understanding and Avoiding Blocking Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on different client computers. Note Some of the actions needing locking protection may not be obvious, for example, locks on system catalog tables and indexes. Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks. Common blocking scenarios include: Submitting queries with long execution times. A long-running query can block other queries. For example, a DELETE or UPDATE operation that affects many rows can acquire many locks that, whether or not they escalate to a table lock, block other queries. For this reason, you generally do not want to intermix long-running decision support queries and online transaction processing (OLTP) queries on the same database. The solution is to look for ways to optimize the query, by changing indexes, breaking a large, complex query into simpler queries, or running the query during off hours or on a separate computer. One reason queries can be long-running, and hence cause blocking, is if they inappropriately use cursors. Cursors can be a convenient method for navigating through a result set, but using them may be slower than set-oriented queries. Canceling queries that were not committed or rolled back. This can happen if the application cancels a query; for example, using the Open Database Connectivity (ODBC) sqlcancel function without also issuing the required number of ROLLBACK and COMMIT statements. Canceling the query does not automatically roll back or commit the transaction. All locks acquired within the transaction are retained after the query is canceled. Applications must properly manage transaction nesting levels by committing or rolling back canceled transactions. Applications that are not processing all results to completion. After sending a query to the server, all applications must immediately fetch all result rows to completion. If an application does not fetch all result rows, locks may be left on the tables, blocking other users. If you are using an application that transparently submits Transact-SQL statements to the server, the application must fetch all result rows. If it does not (and if it cannot be configured to do so), you may be unable to resolve the blocking problem. To avoid the problem, you can restrict these applications to a reporting or decision-support database. Distributed client/server deadlocks. Unlike a conventional deadlock, a distributed deadlock cannot be automatically detected by MicrosoftÂ® SQL Serverâ„¢ 2000. A distributed client/server deadlock may occur if the application opens more than one connection to SQL Server and submits a query asynchronously. For example, a single client application thread has two open connections. It asynchronously starts a transaction and issues a query on the first connection. The application then starts another transaction, issues a query on another connection, and waits for the results. When SQL Server returns results for one of the connections, the application starts to process them. The application processes the results until no more results are available because the query generating the results is blocked by the query executed on the other connection. At this point, the first connection is blocked, waiting indefinitely for more results to process. The second connection is not blocked on a lock, but tries to return results to the application. However, because the application is blocked, waiting for results on the first connection, the results for the second connection are not processed. To avoid this problem, use either: ———————————- A query time-out for each query. A lock time-out for each query. For more information, see Customizing the Lock Time-out. A bound connection. For more information, see Using Bound Connections. SQL Server is essentially a puppet of the client application. The client application has almost total control over (and responsibility for) the locks acquired on the server. Although the SQL Server lock manager automatically uses locks to protect transactions, this is directly instigated by the query type sent from the client application and the way the results are processed. Therefore, resolution of most blocking problems involves inspecting the client application. A blocking problem frequently requires both the inspection of the exact SQL statements submitted by the application and the exact behavior of the application regarding connection management, processing of all result rows, and so on. If the development tool does not allow explicit control over connection management, query time-out, processing of results, and so on, blocking problems may not be resolvable. Guidelines for designing applications to avoid blocking include: Do not use or design an application that allows users to fill in edit boxes that generate a long-running query. For example, do not use or design an application that prompts the user for inputs but rather allows certain fields to be left blank or a wildcard to be entered. This may cause the application to submit a query with an excessive running time, thereby causing a blocking problem. Do not use or design an application that allows user input within a transaction. Allow for query cancellation. Use a query or lock time out to prevent a runaway query and avoid distributed deadlocks. Immediately fetch all result rows to completion. Keep transactions as short as possible. Explicitly control connection management. Stress test the application at the full projected concurrent user load. Deepak Kumar –An eye for an eye and everyone shall be blind

]]>