Query Shaping: a brief lecture by Edward Haynes
As a SQL Server developer and performance optimisation specialist I have been working on innovative techniques to enhance database query performance outcomes. This 15+ year journey has resulted in the creation of an optimisation concept and methodology I call Query Shaping.
The concept of query shaping is built on a database query exhibiting performance characteristics in relation to
- Interactions with the SQL Server database engine;
- An SQL Server construct with specific behavioural patterns (shape).
A database query is a virtual construct created and maintained by SQL Server. This means the design of the query also directly impacts the behaviour of SQL Server. In other words as you change the design of the database query you effectively alter the SQL Server construct and pattern (shape) of behaviour that SQL Server exhibits while maintaining the database query construct.
Understanding the above facilitates an advanced methodology that involves the ability to re-engineer the design of a database query with the very purpose of manipulating the behavioural shape of SQL Server. When applied carefully the behaviour of SQL Server can be manipulated in such a way to improve database query efficiency – often with significant gains in performance.
The material I presented at the ANU sponsored Phil Dooley Science in the Pub event to a general audience represents introductory material for a larger lecture presentation that aims to provide a more comprehensive and rigorous presentation of Query Shaping. A couple of these introductory slides are shown below:
Query Shape
This slide uses abstraction to build a picture of database query behaviour based on contrasting query designs – the red query is more load bearing and inefficient while the green query is less load bearing delivering a more performant outcome.
A key point here is I have often encountered situations where it is possible to re-engineer an inefficient database query so that it shifts towards the green shape delivering a more performant outcome.
Query Shaping
The following slide uses abstraction techniques to visualise and build a framework of SQL Server behaviour patterns based on database query design features – the categories presented are not exhaustive:
This slide will be followed over the coming months by more comprehensive lecture material.
Query Performance Diagnostics
Currently I have spent over a year building a highly specialised database query performance diagnostic tool that is now recently finished. This necessary tool will be used to demonstrate and quantify performance changes in a database query as it is taken through different phases of re-engineering. The tool is tailored with Query Shaping in mind and includes a behavioural parser to help identify performance degradation due to parameter sniffing.
Moving Forward
The performance diagnostic tool will be published soon as a freely available stored procedure designed to work with SQL Server 2008 R2 and higher. Following this I will continue building the lecture material with a completion date of a few months into next year (2020).
It is important to note that in order to understand Query Shaping it really needs to be demonstrated.
