How to Minimize SQL Server Blocking

To help identify blocking in your databases, Microsoft has two separate stored procedures listed on their website (one each for SQL Server 7.0 and 2000) you can use to help identify blocking problems on your SQL Servers. On these pages are scripts you can use to create stored procedures that you can run anytime to help you identify blocking issues.

INF: How to Monitor SQL Server 7.0 Blocking (Q251004)

INF: How to Monitor SQL Server 2000 Blocking (Q271509)

[7.0, 2000] Updated 7-24-2006

*****

One way to help avoid blocking locks is to ensure that your tables have appropriate indexes. This is because the quicker that SQL Server can find the data it is looking for, the less time locks have to be in place, reducing the potential for blocking locks to occur in the first place.

For example, if SQL Server needs to perform an UPDATE to a row, and there is not an appropriate index to quickly find the row that needs to be UPDATED, then a table scan must be performed. While SQL Server is examining every row in the table (which can take a long time if there are many rows), SQL Server has to maintain an UPDATE lock on the entire table during the duration of the table scan, potentially causing much blocking.

Table scans are not the only performance problem. Poorly designed indexes, while perhaps faster than a table scan, can be much slower than a well-designed index, potentially contributing to blocking. It is always in your best interest to optimize indexes so they perform at their maximum. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

Another way to help identify potential blocking issues is to run DBCC OPENTRAN to identify open transactions. This will provide you with the SPID of the open transaction. Next, you can use DBCC INPUTBUFFER to find out what the last command that was run for this SPID. Sometimes, this can help you identify the cause of the blocking. [7.0, 2000, 2005] Updated 7-24-2006

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

2 Responses to “How to Minimize SQL Server Blocking”

  1. Good article.

    I think you wanted to say RECOVERY MODEL and not BACKUP.

    In addition, changing the backup method from full to simple will also reduce the overhead incurred by long running transactions. Once you are done with the long running activity, you can switch back to the full backup method. [6.5, 7.0, 2000] Updated 4-17-2006

    should be:


    In addition, changing the recovery model from full to simple will also reduce the overhead incurred by long running transactions. Once you are done with the long running activity, you can switch back to the simple recovery model. [6.5, 7.0, 2000] Updated 4-17-2006

  2. Good article.

    It is too help full to me

    Thanks..
    Anil

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |