SQL optimisation checklist 07 September 2009 03:42 Coding (0) 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 SET NOCOUNT ON 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 GROUP BY or ORDER BY. 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