Simple tricks which DBA can apply to gain immediate performance gain.
- Table should have primary key
- Table should have minimum of one clustered index
- Table should have appropriate amount of non-clustered index
- Non-clustered index should be created on columns of table based on query which is running
- Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
- Do not to use Views or replace views with original source table
- Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
- Remove any adhoc queries and use Stored Procedure instead
- Check if there is atleast 30% HHD is empty – it improves the performance a bit
- If possible move the logic of UDF to SP as well
- Remove * from SELECT and use columns which are only necessary in code
- Remove any unnecessary joins from table
- If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
There are few hardware upgrades can be considered as well like separating index on different disk drive or moving tempdb to another drive. However, I am not suggesting them here as they are not quick way to improve the performance of query.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Leave a comment