Page History

...

By default, locked sessions waiting for a lock wait forever, which is not optimum behavior from an end-user point of view. You can change this default behavior by adding SET LOCKTIMEOUT 60000 to the top of the first T-SQL batch after making a connection.

The Active Alerts view provides additional information on the Blocking Session Wait Time (Seconds) alert. To access this information, refer to the right-click context menu after having organized your alert view by metric. This menu grants access to real-time alert information, historical view, block details, and online help specific to the alert.

...

• Performing as much work as possible before the transaction performs its first update, delete, or insert. For example, add any necessary SELECT statements.
• Grouping all UPDATES, DELETES, and INSERTS as closely as possible within a transaction with as few SELECTS as possible separating them.
• Committing the transaction as soon as possible after the final DML statement.
• Avoiding Avoid any stops for user input once the transaction begins. Be sure to gather all user inputs before the transaction starts.
• Avoiding Avoid the use of server-side cursors during a transaction as they slow execution considerably.
• Minimizing or eliminating the number of SQL re-compilations made to the object if a stored procedure and/or trigger is invoked inside a transaction. See the SQL RE-compilations counter for steps to dramatically reduce recompiles.
• Increasing the speed of transaction throughput such that it becomes less likely that one transaction waits for the preceding transaction. You can improve transaction throughput speed by:
  • Adding Add more disks to your RAID solution.
  • Replacing your disks with faster disks.
  • Switching your RAID array from a RAID 5 to a RAID 10 solution. Note that each write IO results in two writes using RAID 10 vs. four for RAID 5 (100% more efficient with writes).
  • Switching the RAID controller cache mode from Write-through to Write-back so long as the RAID controller has some form of battery backup. This change increases the system's ability to handle write IOs by an order of magnitude.
  • Adding more cache memory to the RAID controller.
  • Adding more RAM to the server.
  • Adding another CPU to the SMP computer.
  • Upgrading the CPU, memory, and motherboard with faster models.
  • Minimizing the number of Context Switches by turning on Use NT Fibers in SQL Server.
  • Switching on the Boost SQL Server Priority.