a collection of technical fixes and other randon stuff


  • Join Us on Facebook!
  • Follow Us on Twitter!
  • LinkedIn
  • Subcribe to Our RSS Feed

SQL optimisation checklist

  • Place all your code in stored procedures where possible
  • Move declarations  to the start of stored procedures.
  • Are transactions required??? If there are no updates or inserts, then you should remove any transactions and rollback commands. These will just fill your transaction log up.
  • Repeat getdate() commands. Why not decalra a variable at the start of the stored procedure and re-use it later on in the code.
  • Count(*) and count(fieldname). If your deciding if there is data in a table or looping through a numbe rof rows. Use the EXISTS command instead. If a cound is required, just use one field instead of * as is reducess the number of reads.
  • Rollback transactions as soon as possible. Sometimes people build error messages before rolling back... these can be done outside the transaction.
  • XML use... try upgrading to the latest XML methods.
  • Prevent recompiliation
  • Avoid temporary table modifications
  • Reduce multiple lookups into one.
  • Move select statemnets to the start of stored procedures where possible. This reduces record locking times.
  • Modify multiple update statements to use CASE.
  • Reduce UNION commands
  • Get a tool to help you....MS query analyser is pretty valuable.
  • Avoid nested loops
  • Avoid functions in the WHERE clause as this affects performance.
  • Use performance monitors where available.
  • Use a fixed load rather than the maximum load when trying to improve performance
  • Call stored procedures using their fully qualified name
  • Split larger stored procedures into smaller ones and have  acontrolling procedure.
  • Do not use sp_ as a stored procedure prrefix
  • Consider returning the integer value as a RETURN statement instead of returning an integer value as part of a recordset.
  • Avoid temporary tables
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
  • Check the order of ields in the WHERE clause
  • Consider additional indexes on popular search fileds
  • Reduce the data returned (avoid select *)
  • Are ORDER BY and GROUP by required. - these are quite expensive operations
  • INcrease workerthreads
  • Increase CPUs / hard drive speed / Memory
  • Consider offloading work.
  • Use the select statements with TOP keyword or the SET ROWCOUNT
    statement, if you need to return only the first n rows.
  • Clustered indexes are more preferable than nonclustered, if you need
    to select by a range of values or you need to sort results set with
  • Try to avoid using the DISTINCT clause
  • Use table variables instead of temporary tables
  • Create indexes on XML columns

  • De-normalization

  • Avoid use of NULL