Tips
Capturing SQL Server Deadlock Information in XML Format
In your recent tips on SQL Server dead locks (How To: Graphical Deadlock Chain and Deadlock Priority Configuration) I can see the value of using Profiler to capture the process related information. Do any other options exist in Profiler to capture the information is an easier format that I can review? If so, what is the format and how can I begin to take advantages of this configuration in Profiler?
Execute SQL Server SELECT command without locking
We have two different situations in our SQL Server 2000 and 2005 application where we suspect we have locking caused by SELECT statements. First, we have reports that need to run in our production environment from a third party application. These seem to be the worst offenders since some of the reports are ad-hoc in a sense and others are the same report issued repeatedly. Second, as a portion of our OLTP application we have some fairly complex SELECT statements referencing fairly complex VIEWs. These are isolated to a specific portion of the application and we have a good sense of their usage. In either of these situations can you offer any suggestions to reduce the locking?
Finding and troubleshooting SQL Server deadlocks
One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.
How to identify blocking in SQL Server 2005 and 2008
In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID’s in SQL Server 2005 and later versions? Is there only one way to find out which spids are blocking? Are there any commands that I can run against multiple SQL Server versions? Check out this tip to learn more about locking and blocking.
Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity. This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc. What's really necessary is taking the first step to determine where the locking is occurring. With all of the changes from SQL Server 2000 to 2005, what is the best way...
Optimistic Locking in SQL Server using the ROWVERSION Data Type
Using the default SQL Server READ COMMITTED isolation level, my application sometimes falls victim to the dreaded "lost update" condition where two of my users edit the same row for update but the user who submits his/her change last overwrites changes made by the other user. Is there a good way to check for this and prevent it?
Processing Data Queues in SQL Server with READPAST and UPDLOCK
One common processing problem that a DBA can encounter is processing rows from a table used as a data queue. Queue processing is functionality where one or more processes INSERTs rows into a database table representing a work queue with each row representing a business action that must be performed. At the same time, one or more processes SELECTs records from the same queue table in order to execute the business action required by the application while later deleting the processed row so it is not processed again. Typically, the reading processes use polling to interrogate the queuing table for any new rows that require execution of a business action. If done incorrectly this can cause problems.
SQL Server Deadlock Priority Configuration
In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If the deadlocks become severe enough, I need to drop everything I am doing to manage the issue in order for the users to stay productive in the business application. If I could manage the deadlocks in an easier manner, that would be good motivation to move to SQL Server 2005. Does SQL Server 2005 have any new deadlock related features?
SQL Server Profiler Graphical Deadlock Chain
I have read your tip on SQL Server locking, blocking and deadlocking (Finding and troubleshooting SQL Server deadlocks) which are applicable to some of issues I have been recently facing. I have been noticed locking on my SQL Server and have heard about the issues from my users. I have been trying to troubleshoot it, unfortunately, I have been having a hard time understanding the issues with large deadlock chains. Does SQL Server have any way of identifying, understanding and trouble shooting deadlocks other than your previous tip (Finding and troubleshooting SQL Server deadlocks)?
Understanding SQL Server Blocking
In my previous article, I provided you with a script that presented the current locks being held in the active database. While this script also provided limited information on blocking that was occurring, it did not give a fully-informative insight on that matter. I alluded to a different solution for reviewing blocking and this is the subject of this article. For those of you who've not yet read the Understanding SQL Server Locking tip I suggest doing so as it gives a high-level view of sys.dm_tran_locks, locking, and the objects we'll be discussing in this tip.
Understanding SQL Server Locking
Help! My SQL Server database is locking. That can't be good! What should I do?
Understanding the SQL Server NOLOCK hint
I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain the what NOLOCK does and whether this is a good practice or not?
Using a Clustered Index to Solve a SQL Server Deadlock Issue
At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving the dreaded deadlock error.
Using SQL Server Indexes to Bypass Locks
One of the issues you will face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?
Top 10
Understanding the SQL Server NOLOCK hint
I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain the what NOLOCK does and whether this is a good practice or not?
Finding and troubleshooting SQL Server deadlocks
One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.
Using a Clustered Index to Solve a SQL Server Deadlock Issue
At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving the dreaded deadlock error.
How to identify blocking in SQL Server 2005 and 2008
In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID’s in SQL Server 2005 and later versions? Is there only one way to find out which spids are blocking? Are there any commands that I can run against multiple SQL Server versions? Check out this tip to learn more about locking and blocking.
Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity. This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc. What's really necessary is taking the first step to determine where the locking is occurring. With all of the changes from SQL Server 2000 to 2005, what is the best way...
Processing Data Queues in SQL Server with READPAST and UPDLOCK
One common processing problem that a DBA can encounter is processing rows from a table used as a data queue. Queue processing is functionality where one or more processes INSERTs rows into a database table representing a work queue with each row representing a business action that must be performed. At the same time, one or more processes SELECTs records from the same queue table in order to execute the business action required by the application while later deleting the processed row so it is not processed again. Typically, the reading processes use polling to interrogate the queuing table for any new rows that require execution of a business action. If done incorrectly this can cause problems.
Optimistic Locking in SQL Server using the ROWVERSION Data Type
Using the default SQL Server READ COMMITTED isolation level, my application sometimes falls victim to the dreaded "lost update" condition where two of my users edit the same row for update but the user who submits his/her change last overwrites changes made by the other user. Is there a good way to check for this and prevent it?
Understanding SQL Server Locking
Help! My SQL Server database is locking. That can't be good! What should I do?
SQL Server Deadlock Priority Configuration
In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If the deadlocks become severe enough, I need to drop everything I am doing to manage the issue in order for the users to stay productive in the business application. If I could manage the deadlocks in an easier manner, that would be good motivation to move to SQL Server 2005. Does SQL Server 2005 have any new deadlock related features?
Understanding SQL Server Blocking
In my previous article, I provided you with a script that presented the current locks being held in the active database. While this script also provided limited information on blocking that was occurring, it did not give a fully-informative insight on that matter. I alluded to a different solution for reviewing blocking and this is the subject of this article. For those of you who've not yet read the Understanding SQL Server Locking tip I suggest doing so as it gives a high-level view of sys.dm_tran_locks, locking, and the objects we'll be discussing in this tip.
Last 10
Using a Clustered Index to Solve a SQL Server Deadlock Issue
At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving the dreaded deadlock error.
Understanding the SQL Server NOLOCK hint
I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain the what NOLOCK does and whether this is a good practice or not?
How to identify blocking in SQL Server 2005 and 2008
In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID’s in SQL Server 2005 and later versions? Is there only one way to find out which spids are blocking? Are there any commands that I can run against multiple SQL Server versions? Check out this tip to learn more about locking and blocking.
Understanding SQL Server Blocking
In my previous article, I provided you with a script that presented the current locks being held in the active database. While this script also provided limited information on blocking that was occurring, it did not give a fully-informative insight on that matter. I alluded to a different solution for reviewing blocking and this is the subject of this article. For those of you who've not yet read the Understanding SQL Server Locking tip I suggest doing so as it gives a high-level view of sys.dm_tran_locks, locking, and the objects we'll be discussing in this tip.
Understanding SQL Server Locking
Help! My SQL Server database is locking. That can't be good! What should I do?
Execute SQL Server SELECT command without locking
We have two different situations in our SQL Server 2000 and 2005 application where we suspect we have locking caused by SELECT statements. First, we have reports that need to run in our production environment from a third party application. These seem to be the worst offenders since some of the reports are ad-hoc in a sense and others are the same report issued repeatedly. Second, as a portion of our OLTP application we have some fairly complex SELECT statements referencing fairly complex VIEWs. These are isolated to a specific portion of the application and we have a good sense of their usage. In either of these situations can you offer any suggestions to reduce the locking?
Optimistic Locking in SQL Server using the ROWVERSION Data Type
Using the default SQL Server READ COMMITTED isolation level, my application sometimes falls victim to the dreaded "lost update" condition where two of my users edit the same row for update but the user who submits his/her change last overwrites changes made by the other user. Is there a good way to check for this and prevent it?
Using SQL Server Indexes to Bypass Locks
One of the issues you will face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?
Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity. This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc. What's really necessary is taking the first step to determine where the locking is occurring. With all of the changes from SQL Server 2000 to 2005, what is the best way...
Processing Data Queues in SQL Server with READPAST and UPDLOCK
One common processing problem that a DBA can encounter is processing rows from a table used as a data queue. Queue processing is functionality where one or more processes INSERTs rows into a database table representing a work queue with each row representing a business action that must be performed. At the same time, one or more processes SELECTs records from the same queue table in order to execute the business action required by the application while later deleting the processed row so it is not processed again. Typically, the reading processes use polling to interrogate the queuing table for any new rows that require execution of a business action. If done incorrectly this can cause problems.
