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.
| Tip |
|---|
To access similar information on the Blocking view , select a specific SQL Server instance, point to Sessions, and then select the Blocking ribbon. |
Blocking Sessions Report
The Blocking Sessions Report displays blocking and blocked sessions' information. To access the Blocking Sessions Report in the Alert view, select the Show Block Details option in the right-click context menu.
...
| Tip |
|---|
To retrieve the blocking sessions report for SQL Diagnostic Manager, select the "Non-Query activities>Capture Blocking (SQL 2005+)" check box in the Activity Monitor Tab. |
Reduce blocked sessions wait time
If you experience more than one blocked session on an average day, your site may experience one of the following issues:
...
- 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.
| Tip | icon | false|
|---|---|---|
| ||
Create an alert response bundle with the Blocking Session Wait Time (Seconds) alert and related alerts. For additional information, see Create alert response bundles. |
| Scroll pdf ignore |
|---|
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
...
...