There are many factors which affect performance of SQL query. In this post I would like to explain few of them.
1. Avoid querying all columns of a table and query only required columns.
2. Use “NOLOCK” wherever possible in select queries.
3. Use Execution Plan or Estimated Execution Plan to compare queries with regards to their cost.
4. Use “Database Engine Tuning Advisor” to get information about missing indexes.
5. Avoid creating too many indexes on your own rather create on suggested by tuning Advisor.
6. Add more memory. This solution can be especially helpful if the server runs many complex queries and several of the queries execute slowly.
7. Use more than one processor. Multiple processors allow the Database Engine to make use of parallel queries.
8. If the query uses cursors, determine if the cursor query could be written using either a more efficient cursor type (such as fast forward-only) or a single query. Single queries typically outperform cursor operations. Because a set of cursor statements is typically an outer loop operation, in which each row in the outer loop is processed once using an inner statement, consider using either a GROUP BY or CASE statement or a sub query instead.
9. If an application uses a loop, consider putting the loop inside the query. Often an application contains a loop that contains a parameterized query, which is executed many times and requires a network round trip between the computer running the application and SQL Server. Instead, create a single, more complex query using a temporary table. Only one network round trip is necessary, and the query optimizer can better optimize the single query.
10. Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query.
11. Use query parameterization to allow reuse of cached query execution plans. If a set of queries has the same query hash and query plan hash, you might improve performance by creating one parameterized query. Calling one query with parameters instead of multiple queries with literal values allows reuse of the cached query execution plan.
12. Use the query_plan_hash to capture, store, and compare the query execution plans for queries over time. For example, after changing the system configuration, you can compare query plan hash values for mission critical queries to their original query plan hash values. Differences in query plan hash values can tell you if the system configuration change resulted in updated query execution plans for important queries. You might also decide to stop execution for a current long-running query if its query plan hash in sys.dm_exec_requests differs from its baseline query plan hash, which is known to have good performance.
13. Make use of the query governor configuration option. The query governor configuration option can be used to prevent system resources from being consumed by long-running queries. By default, the option is set to allow all queries to execute, no matter how long they take. However, you can set the query governor to limit the maximum number of seconds that all queries are allowed to execute for all connections, or just the queries for a specific connection. Because the query governor is based on estimated query cost, rather than actual elapsed time, it does not have any run-time overhead. It also stops long-running queries before they start, rather than running them until some predefined limit is hit.
14. Check List for analyzing slow running queries.
a. Slow network communication.
b. Inadequate memory in the server computer, or not enough memory available for SQL Server.
c. Lack of useful statistics
d. Lack of useful indexes.
e. Lack of useful indexed views.
f. Lack of useful data striping.
g. Lack of useful partitioning.
15. Microsoft SQL Server performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Using this type of query plan, SQL Server supports vertical table partitioning, sometimes called columnar storage.
SQL Server employs three types of join operations:
· Nested loops joins
· Merge joins
· Hash joins
If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons
If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other.
Hash joins can efficiently process large, unsorted, nonindexed inputs. They are useful for intermediate results in complex queries because:
· Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
· Query optimizers estimate only intermediate result sizes. Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.
The hash join allows reductions in the use of denormalization. Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Hash joins reduce the need to denormalize. Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.
Thanks! Very Helpful.
ReplyDelete