SQL Server Query Optimization and SQL Query Tuning
The process of SQL Server query optimization can involve a wide variety of
strategies and techniques. To optimize SQL Server queries and incease performance, we carefully consider and balance each of the following:
- SQL Database Structure Optimization — to improve query optimization, make sure the tables contain the right information and the indexes are structured
correctly for efficient access. For example, creating missing indexes for fields that are used in the query predicate
will avoid scanning large tables when you only need a small fraction of the rows. By creating and rebuilding appropriate indexes, system performance and query optimization is commonly increased by 10x.
- Application Optimization — systems with users that are running (and waiting for) complex queries to complete
can block each other, causing locking and blocking contentions and timeouts if the database is not properly designed. SQL
Server profiling often reveals application code that requests far more data than necessary.
For instance, many applications display thousands of rows using a paged display
paradigm, and then
only display one page at a time. It is far more efficient (faster) to request only the rows for the specific pages are
- Language optimization — to improve query optimization, make sure the query is written to take full advantage of the data structures and
the features of the engine. There are many query tuning methods, such as avoiding needless joins, groupings,
and scalar user defined functions. Legacy queries may be optimized by using advanced language features that were
introduced in the current version of SQL Server 2012.
- Hardware optimization — to improve SQL query optimization, make sure the database server isn’t being held back by obsolete configurations or data load problems. SQL Server is disk I/O intensive, so ensuring fast hard drive access, and appropriately balancing I/O requests across
the physical devices can improve system performance significantly.
- Intent optimization — to improve query optimization, make sure you are storing the right information and are asking for relevant results. For example, you may have an SSRS report with several calculated fields, but no one actually makes any use of
one of the calculated fields. Eliminating this unused calculation from the report will boost your query optimization and reduce the time required
to generate the report.
The methods one employs to optimize SQL Server queries are as much art as science — skills obtained through years of
experience — and typically involve some level of trial, measurement and error to achieve optimal overall SQL query optimization and database performance
efficiencies. The 80/20 rule applies to SQL Server query optimization: it is often possible to realize
significant improvements in overall system performance (10x is typical) by remediating the top five most obvious issues.
If your SQL Server database solution is up and running, but you are experiencing
significant data reliability and/or query optimization problems, PCA can help you discover
the underlying design and/or implementation causes, and provide proven SQL Server Query Optimization
techniques to improve overall system performance and reliability.
PCA provides expert
SQL Server database performance audits
and SQL Consulting Services to
insure optimal SQL Server query optimization, reliability and data integrity. We do not rely on just the SQL Query Optimizer. We know
what to look for, and how to apply the most effective remedial database
optimization techniques to speed up and stabilize your MS SQL Server Business Application.
"In our experience, a 10x improvement in database performance is often realized by addressing
just the top five issues – the 80/20 rule definately applies"