by James Earnshaw on April 06, 2024
Without going into detail (future posts will) here is an overview of query tuning in SQL Server.
Query tuning is the process of making SQL queries go faster. It's a technique used when other options, such as spending money on better hardware, are not possible. Query tuning is about knowing the best practices for authoring SQL queries and the anti-patterns to avoid.
At the core of query tuning is helping the SQL Server Query Optimizer do its job of finding the most efficient query execution plan. It's easy to mess this up by, for example, wrapping a predicate column in a function, which prevents it being SARGable (search argument) i.e. the Optimizer being able to use an appropriate index when one exists.
Example:
SELECT CustomerID FROM Sales.SalesOrderHeader WHERE CustomerID + 0 = 11000
Adding zero to CustomerID turns the left side of the predicate into an expression which prevents the Optimizer from using the index (IX_SalesOrderHeader_CustomerID). Instead it has to do an index scan rather than a seek.
The first step is to have a way of monitoring SQL Server's performance and identifying potential query(s) to tune. This includes establishing a baseline of performance. Then you perform the troubleshooting actions you think will improve the candidate query's performance. Then you measure the performance of the system again to see if the actions had any effect.
In practice it's an iterative process in which this cycle is repeated. There's an element of experimentation involved because it's not always obvious the what problem is; it could turn out to be a server tuning problem (i.e. it needs more CPU and/or RAM). The Pareto principle crops up too because it's likely that only a few queries out of all the queries will be slowing down the system.
Query tuning aims to increase performance so it's helpful to know what causes bad performance in the first place. The common causes are:
Query tuning is a big topic and this is only the briefest of overviews. In future posts I'll dive deeper into these topics.