SQL Server Query & Index Optimization
The combination of our many years of expertise and our toolset enable us to determine optimal query and index structure quickly and accurately.
With decades of experience, our SQLRx team has helped many of our clients, big and small to optimize their SQL databases.
We’ve learned that well-performing queries require a combination of good code design and indexing support. If either of these is deficient, query performance degrades while producing undue pressure on the server’s hardware components.
Tuning queries requires an in-depth knowledge of T-SQL code, SQL Server execution plans, index structure, and performance metrics.
SQLRx combines our experience and award-winning toolset enable us to determine:
- Which query statements perform poorly because they consume excessive amounts of system resources
- Exactly how existing indices are used
- Whether any functional duplication exists
- Index constructs that are missing
Accomplishments
Sample results include the discoveries of tables that have grown significantly over time without the benefit of any indices, elimination, and consolidation of unused or duplicated indices, and very large tables that were scanned completely.
95% |
Single Read Queries
In a 200 million-record table, decreased single read queries from 9.7 MILLION to 483,000.
90% |
Single Query Random Record Searches
In a 2.46 BILLION-record table, decreased single query random record searches from 48 million to 4.7 million.
92% |
Key Lookups
Decreased key lookups from 816,032/sec to only 63,292/sec.
97% |
Indices in a single table
Reduced number of indices for single table from 144 to 4.
90% |
Collective Query Processor Times
Decreased query:
Processor times from 20 hours to 2 hours
Elapsed time from 39 hours to 6 hours
Our Process
Once a performance study has been completed, the process of remediating the uncovered performance problems begins.
SQLRx has a proven methodology for tuning queries and implementing a streamlined indexing structure to support these queries.
We conduct a Windows Hardware & Software Performance Analysis study.
We examine individual database tables that are utilized by the most resource-intensive queries.
Examine each query to:
- Determine the joins and where clauses that potentially impact performance the most
- If views are involved, expand their definitions even if they are nested to uncover how tables are utilized
- When possible, obtain an actual execution plan to verify these hypotheses
- Once the most important tables have been identified, examine the current indexing structure along with the recommendations provided by SQL Server
- Identify any redundant and unused indices as candidates for removal
- Determine whether any index support is missing, and if so, attempt to implement a consolidated index
- Recommend query and index changes to address poor query performance
Examine large tables to:
- Identify queries that use the table
- Determine why the queries perform full scans instead of single-record lookups or range scans
- Recommend query and index changes to address poor query performance
Once the specified number of queries has been examined, we provide you with a summary of the recommended changes.
Testimonials
We've just celebrated our second year-end without serious SQL Server problems. The index tuning suggestions made greatly improved the performance … efforts can be felt throughout …
Travis Bish
Ceridian Corporation
Atlanta, GA
We are extremely pleased with the results ... worked with our staff during the analysis and tuning phases … resulted in a lot of knowledge transfer … documentation was detailed and well organized.
Joab Schulteis
Atlas World Group
Evansville, IN
You steered us in the right direction!!!! The EFT process went from 6-7+ hours [down] to 10 minutes in PRODUCTION. WOW!
Financial Services Company