SQL Query Optimization and Performance Guide
October 10, 2013 1 Comment
- Adjust performance for background services
Configure size of windows paging file(virtual memory) to be twice the size of physical memory
Turn off system protection feature for all disks except for C
Disable unneeded sql services
- Configure weekly defragmentation schedule for all disks
Storage on 2 Physical disks – standard solution
- Store log file on C
- Store data file on the second disk
Storage on 3 Physical disks – extended solution
- Store data file on second disk
- Store log file on third disk
- Store Windows Paging File(Virtual Memory) on C
Storage on 4 physical disks – optimal solution
- Store windows paging file on C
- Store primary data file on second disk
- Store log file on third disk
Create Secondary data file on fourth disk to store indexes
When creating indexes, select the secondary file group
Storage on external hard disk or flash memory
- Not allowed
- This will disable all optimizations performed by SQL Server
- This will limit read/write speed to 25Mbits/sec for external hard disk and slower for flash memory
Database Auto growth
- Do not set to grow in %. Use static value instead(100MB is good option)
- Create non-clustered index only for tables that have rate of SELECT much larger than rate of INSERT and UPDATE
- Do not create indexes for all table columns
- For indexes created on base tables(for example Person, Admission, AdmissionRequestDetail), set the index fill factor to 70 instead of 80(default option). This will enhance performance for INSERT.
Use the following procedure to analyze index fragmentation. You should “reorganize” indexes when the External Fragmentation value for the index is between 10-15 and the Internal Fragmentation value is between 60-75. Otherwise, you should rebuild indexes.
si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
Create non-clustered indexes only for columns that appear in:
- Where clause
- Order By clause
- Join clause
- All foreign keys
- Avoid indexing small tables
- Create indexed views for columns used by Linq query in the where clause, if the index is not created on the table but do not create the index on both.
Do not use Year(), Month() ,Day() functions in the where clause on a column even if it has an index. Using these functions and other similar functions will disable the index and will do a full table scan. So change the query to make use of the index. Example:
- Use the index usage report to check if there are unused indexes on the table in order to delete them. If the number of seeks is 0. The index can be deleted.
- Do not name the procedure sp_something. This will cause a delay when the procedure executes.
- Use Set Nocount On at the top of the procedure to avoid additional round trip to the server
- Try to avoid using exec of dynamic sql statements
Do not use a cursor to iterate over a set of records. Creating and using a cursor is very expensive and resource consuming. Instead, use a while loop with defined upper bound. This option is not available for SQL 2000. Example:
Do not use OR in the where clause. Instead, use UNION ALL to implement OR functionality but only if there is an index on the column used in OR operator. If there is no index, this optimization will slow down performance by doing multiple table scans. If there is an index this approach will speed up the query. Example:
person.Sex=0 or person.Sex=1
- Avoid using inline queries, use joins instead
- Avoid using inline function in the query. Instead, create a precomputed column with the function formula to be stored for selection in the query. May not be applicable for all cases.
- When writing queries containing NOT IN, the query will have poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS
- When you have the choice to use IN or BETWEEN clause in the query, always use BETWEEN. This will speed up the query
- When having multiple columns in where clause separated by AND operator, make sure to order the columns from least likely true to most likely true if the expected result is most likely true. Otherwise, order the columns from most likely false to least likely false. In other words, the condition that filters the least number of records must appear first in the where clause.
For queries that require immediate feedback to the user, use FAST n option to immediately return first n records while the query continues to fetch the remaining records. This option can be used in procedures and views but it does not apply for linq queries because .ToList() ignores this option. Example:
Scheduled Maintenance Plan
- Use daily maintenance plan to perform daily cleanup, update statistics, check integrity, rebuild indexes, organize indexes, shrink database files and perform a full backup.
- Do not include system databases in the plan. Select the option “User databases only”
- Make sure SQL Server Agent startup option is set to Automatic
- Schedule the plan to run daily at night. 1am is a good option.
The below plan is standard and can be used for most cases.
Check disk usage by top tables
- Monitor disk usage by most used tables. These will be the target for potential indexes.
- Use this approach to check if the application is performing repeated unnecessary reads