Performance Tuning

Query Tuning is an advanced skill which involves analysing slowly performing queries and identifying the best approach to improve performance, from a wide range of options including re-writing queries, indexing queries, re-structuring database objects or even re-developing client software where appropriate.

The "best" approach often involves trade-offs between achieving performance vs the impact from change and also must take into account whether individual approaches are feasible in the circumstances. For example, re-structuring a database or re-writing client application code is usually not possible when tuning packaged software without permission from its vendor but, in another scenario, might be the ideal approach for an internal bespoke application.

Indexing of database tables is essential to achieving good performance because queries locate rows most efficiently via indexes rather than scanning tables or partitions.

Trade-offs also need to be made when making indexing decisions because creation of too many indexes can also slow down transactional updates to databases as well as make databases larger, increasing storage space, IOPS, logging and backups.

"Good" indexing requires a thorough knowledge of how query execution plans are optimised, whilst taking the above trade-offs into consideration and is often considered an art form.

The design of relational databases often involves trading off the integrity of its design (its "normalisation") to achieve higher levels of performance. Integrity of data stored in the database is preserved by breaking larger database entities into smaller entities which then require joining back together for query processing.

Mistakes sometimes also need correcting in database designs, such as use of incompatible column type contraints, foreign key constraint creation or configuration changes made such as recovery model, compression or index statistics maintenance settings.

MyDBA's expert consultants provide skilled guidance in all of these performance sensitive areas.

Inter query blocking guarantees integrity of transaction processing and is an important feature of modern database management systems but can also impact performance negatively if it occurs excessively.

Analysing which queries are the source of blocking hierarchies and tuning them is an essential part of database performance tuning, which MyDBA's expert consultants and tools can help you solve permanently.

MyDBA's performance tuning philosophy is firmly based upon use of data and observations to make decisions rather than simply implementating best practices which may work well in some but not all scenarios.

Our "SQLBench" cloud based, global monitoring platform is used to collect database telemetry centrally, to take the guess work out of performance tuning decisions.

Provisioning "right-sized" infrastructure and configuring it correctly is crucial to achieve high levels of database performance.

MyDBA provides expert advice on infrastructure decisions, including on prem hardware and cloud based options. We the collective experience from our real world client implementations to all infrastructure configuration work, including all major hardware OEM equipment and cloud configurations.

Microsoft SQL Server is sensitive to having adequate levels of compute resources, particularly CPU, Memory and Disk but excessive over-provisioning of these resources can also be costly in licensing, hardware or cloud compute costs, so "right-sizing" and configuration of database infrastructure is crucial to achieving high performance whilst also managing licensing and operational costs.