Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

...

  • 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
titleCreate an alert response bundle

Create an alert response bundle with the Blocking Session Wait Time (Seconds) alert and related alerts.  For additional information, see Create alert response bundles.



Legal
Scroll pdf ignore
Newtabfooter
aliasIDERA
urlhttp://www.idera.com
Newtabfooter
aliasProducts
urlhttps://www.idera.com/productssolutions/sqlserver
Newtabfooter
aliasPurchase
urlhttps://www.idera.com/buynow/onlinestore
|
Newtabfooter
aliasSupport
urlhttps://idera.secure.force.com/
|
Newtabfooter
aliasCommunity
urlhttp://community.idera.com
|
Newtabfooter
aliasResources
urlhttp://www.idera.com/resourcecentral
|
Newtabfooter
aliasAbout Us
urlhttp://www.idera.com/about/aboutus
|
Newtabfooter
aliasLegal
urlhttps://www.idera.com/legal/termsofuse
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
IDERA WebsiteProductsPurchaseSupportCommunityAbout UsResources