SQL Performance Improvement
- SQL query optimization basics
- Add missing indexes
- Check for unused indexes
- Avoid using multiple OR in the FILTER predicate
- Avoid too many JOINs.
Missing Indexes
SQL Server, via the Management Studio GUI, execution plan XML, or missing index DMVs, will let us know when there are missing indexes that could potentially help a query perform better:
This warning is useful in that it lets us know that there is a potentially easy fix to improve query performance. It is also misleading in that an additional index may not be the best way to resolve a latency issue. The green text provides us with all of the details of a new index, but we need to do a bit of work before considering taking SQL Server’s advice:
Avoid Running Queries in a Loop
Running queries in a loop can significantly slow your runtime. In some cases, you may be able to bulk insert and update data, which is far more efficient than using loops.
How can you select which queries to optimize?
Before you can optimize any queries, you have to decide which ones are best to optimize. Unfortunately, many people skip this critical step, but by targeting specific, troublesome queries with significant impacts on execution time, you can dramatically increase performance.
If you are less selective when deciding which queries to optimize, you may end up wasting time and money by optimizing those that don’t significantly contribute to performance, don’t impact other queries, or don’t result in problems users will notice.
When starting the MS SQL database query optimization process, look for queries that are consistently or occasionally slow, have red flags, or are major contributors to the total execution time.
If you are less selective when deciding which queries to optimize, you may end up wasting time and money by optimizing those that don’t significantly contribute to performance, don’t impact other queries, or don’t result in problems users will notice.
When starting the MS SQL database query optimization process, look for queries that are consistently or occasionally slow, have red flags, or are major contributors to the total execution time.
Queries That Majorly Contribute to Total Execution Time
If more than five percent of total execution time can be attributed to a single query, try to optimize the query in question. Focusing your efforts on optimizing major contributors rather than those that don’t significantly contribute to performance will enable you to use your time more efficiently.