HomeServicesTechnologyAnalyticsOur ClientsAbout PCAContact Us

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 displayed.
  • 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.