Page History
...
- 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 > >
...