Find blocking processes using recursion in SQL Server 2005
Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set (or subset) of records. This causes the second process to wait until the first process is done so it can acquire the records; this can cause other processes to be blocked if they are waiting on the resources that the second connection has acquired. These processes are ultimately dependent upon the first process to complete its work before they can complete theirs. This can create a daisy-chain of processes waiting to complete their work.
Source: Tim Chapman January 2008
Download & read the full article at:
Manager
Philippe Almog MSCD
SQL Server DBA Consultant
Israel
SQLPractices RSS- SET XACT_ABORT – Use Caution with Explicit Transactions in Stored Procedures July 5, 2008Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity. However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used. I’ll describe how to protect your application from problems following timeouts and other […]Philippe Almog
- Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 June 27, 2008This paper explains how batches are cached and reused in SQL Server 2005, and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations. Thanks to Arun Marathe & Shu Scott / Source Microsoft TechNet Download ; rea […]Philippe Almog
- Defining cascading referential integrity constraints in SQL Server June 20, 2008Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted. By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update [... […]Philippe Almog
- SQL Worst Practices Part4 May 9, 2008Making Databases Case Sensitive….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part3 May 9, 2008Not Using Primary Keys and Clustered Indexes….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part2 May 9, 2008Objects Not Owned by DBO….. Thanks to Andy Warren Download ; read the full article at:Philippe Almog
- SQL Worst Practices Part1 May 9, 2008In our business we spend a lot of time talking about ‘Best Practices’ – ways of doing things that over time have proved to be the most effective (or accurate, or whatever..). There are a couple problems with Best Practices. One is that there is no ‘book’ of them, they are scattered across a hundred [...]Philippe Almog
- Predeployment I/O Best Practices April 27, 2008SQL Server Best Practices — Published: June 5, 2007 Writer: Mike Ruthruff The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to [...]Philippe Almog
- Analysis Services Distinct Count Optimization April 26, 2008Analysis Services Distinct Count Optimization SQL Server Best Practices Article Thanks to Denny Lee Published: April, 2008 Applies To: SQL Server 2005 SP2 Download & read the full article at:Philippe Almog
- 2 Stored Procedures to Tune Your Indexes April 25, 2008Source: SQL Server Magazine APRIL 2008 Thanks to Bill McEvoy Download & read the full article at:Philippe Almog
- SET XACT_ABORT – Use Caution with Explicit Transactions in Stored Procedures July 5, 2008
-
Archives
- July 2008 (1)
- June 2008 (2)
- May 2008 (4)
- April 2008 (3)
- January 2008 (29)
-
Categories
-
RSS
Entries RSS
Comments RSS
