Versions Compared

Key

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

...

  • 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 any stops for user input once the transaction begins. Be sure to gather all user inputs before the transaction starts.
  • Avoiding 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 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

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

 

SQL  Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >

...