SQL Server Performance Coding Standards
By : Amol Ramesh Kulkarni1. Avoid using “*” in SELECT queries
Always specify the required list of columns in the select list. This will ensure that only the columns required by the query are returned to avoid unnecessary I/O and processing.
2. Always use variables of the appropriate data type and size
Use appropiate data types to avoid implicit data type conversion being perfromed by SQL Server and appropiate sizes to avoid excess usage of memory.
For example, assigning an integer type to a varchar is allowed but SQL Server will implicitly convert the integer data type to varchar which causes additional overhead.
For a column/variable like Age a data type of type integer is required. For this int, smallint or tinyint types can be used. But using int or smallint would require 4 and 2 bytes respectively of memory which is very large to accommodate the Age variable. Hence using tinyint would be a better choice, which would occupy 1 byte and provide a range between 0 and 255.
3. Keep transactions as small as possible
Try and avoid using large transactions. Using large transactions across an entire batch can cause other processes to be locked/blocked and the resources involved in the transaction cannot use them until the transaction is completed (committed or rolled back) causing performance issues in the database or even causing deadlocks.
4. Avoid excessive usage of temp tables
Excessive usage of temp tables may cause tempdb contention which can degrade the overall performance of the system. You can use table variables instead of temp tables, as this can use the memory allocated to that process and avoid usage of tempdb. It is recommended to use temp tables when dealing with considerably large amounts of intermediate data.
When using temp tables avoid using the SELECT INTO clause to create and insert data into temp tables. This will lock the entire tempdb and will cause blocking issues with other processes trying to use the tempdb resources. Always use the CREATE TABLE statement to create the temp table and then use INSERT INTO statement to insert data into the temp table. This wwill avoid tempdb contention and allow other process to use the tempdb resources.
5. Environmental settings
Always use “SET NOCOUNT ON” in stored procedures to avoid unnecessary data traffic on the network.
6. Avoid usage of cursors
Cursors are CPU intensive and make round trips to the CPU for every execution, degrading the overall performance of the batch. Instead use while loops to loop through the data rows. In SQL Server 2005, a CTE can be used to loop through data rows.
7. Never use a function towards left side in the WHERE clause
Using a function towards the left side in the WHERE clause can prevent SQL Server using an index. If needed use the function call on the right side in the WHERE clauses which allows the index to be used for the query.
8. Avoid using the DISTINCT clause
If you need to return a distinct set of rows, use a GROUP BY clause instead of DISTINCT. As the GROUP BY clause is evaluated before the DISTINCT clause.
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. 呵呵，不错。
页:
[1]
