9. Always restrict the size of SP or UDF
SQL Server maintains a data/execution plan in cache of 300 sec. If a SP/UDF is executed its execution and context plan is cached by SQL Server for 300 sec, and if a call happens to the same SP/UDF within 300 sec the cached execution plan (context plan may be same or changed depending on the parameters) is used by SQL Server for execution else it will reload the entire SP/UDF and then execute it. If the size of the SP/UDF is large then the time take for loading it into memory is more and hence there is a performance hit. If the SP/UDF is smaller enough to load faster, it adds up to the performance gain. Generally restrict the size of the SP/UDF to be around 4-5KB.
10. High Read intensive queries
High read intensive queries i.e. queries which retrun large amount of data (rows) from a database will require more I/O then CPU. Such queries should be run on a single processor rather than spanning it across multiple processors. Either set the “Max Degree of Parallelism” to 1 using sp_configure (this affects entire database) or use the MAXDOP option to restrict the particular query to use only one processor.
11. Always use UDF instead of SP when it is supposed to return a scalar value
When deciding between an SP and a UDF to return a scalar value, it is recommended to use a UDF. Performance wise both an SP and UDF are equal. A UDF provides the facility to be used within a SELECT query and in a WHERE clause.
12. Use Locking hints / Isolation Levels as required
Use the locking hints especially NOLOCK with read only queries to avoid locking/blocking other processes using the same object(s). When a batch/SP needs to be run under non-blocking condition use the transaction isolation (read uncommitted) level. This also improves the performance of the queries as SQL Server does not have to cater the lock resources.
13. Avoid using OR in WHERE/JOIN clauses in a Query
Using the OR clause in a WHERE or JOIN would make SQL Server not use correct indexes. It is sometimes ok to use the OR in WHERE but it’s dangerous to use OR in a JOIN. If it’s a mandate (depends on the requirement) to use OR in the JOIN then divide the query in two parts and combine the data using UNION or UNION ALL. This will provide a drastic improvement in the performance.
14. Avoid using IN and NOT IN clauses
The usage of IN and NOT IN clauses in WHERE conditions will make SQL Server check for all the values within the IN clause, and this will degrade the performance if the number of values within the IN are too many. (IN clause query can be replaced using JOINs.) Instead of IN/NOT IN use EXITS/NOT EXITS which will check for the very first existence and continue with the query, improving the performance.
15. Always have a CLUSTERED INDEX defined on a table
When designing (Normalization) a table it is recommended to have CLUSTRED INDEX created on the table. Having a clustered index sorts the data w.r.t. the column data and any subsequent non-clustered index on that table will use this clustered index (sorted) to seek the data. Also it will help reduce fragmentation within the data/index pages. Consider having a clustered index on a primary key column(s) or define an identity column for the table and qualify that for the PK and clustered index.
When planning for a Index (clustered or non-clustered), and depending on the table functionality (high read or write intensive) specify a FILLFACTOR. If the table is more write intensive specify a fillfactor between 85-90% depending on the size of the row that would be inserted. For read only tables (OLAP) its not required to have a fill factor. Having a proper fill factor would minimize the page splits avoiding SQL Server time to service the page splits.
16. Turn off the AUTO SHRINK
For databases with more write operations, always ensure that the AUTO SHRINK property is turned OFF. Database shrink operation shrinks the database files and also causes fragmentation of indexes which will hinder the over all performance. Always run the database shrink operation during off peak hours immediately followed by a defragmentation of indexes.
17. Run UPDATE STATISTICS regularly
For databases with more write operations, the statistics will go out of date causing SQL Server to use old statistics causing performance problems. Hence always have a scheduled job (off peak hours) to update the statistics of all the tables (or transactional tables) regularly.
]]>