Optimize and tune queries
May include but is not limited to: optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP]
This one seems to be a rather large objective, but have no fear; we’ll slowly dig through it. Let’s take a look at the optimizer hints first.
As you may already know SQL Server optimizes your queries to perform as best as possible. However even the Query Optimizer can go wrong in some particular cases. As a last resort we have query hints to force some kind of execution. Generally you won’t have to use these kind of beasts, but it won’t hurt you to know about them.
There are three major kinds of hints:
- Join Hints
- Query Hints
- Table Hints
We won’t bother with join hints, because I don’t really see any possibility about them showing up on the exam. So let’s consider query hints first. You can specify query hints in the OPTION clause, at the end of a statement. There are a whole lot of them, and I don’t feel like copying them from MSDN, so search for the link at the end of this post. We’ve met with some query hints already at the transaction part. There are query hints for each locking level: READUNCOMMITTED, REPEATABLEREAD, READCOMMITTED, SERIALIZABLE can be specified in an OPTION clause.
You can optimize for given values for your local variables in a query. These values will be used for query optimization, but not for execution. The hint to use is OPTIMIZE FOR (@variable name [UNKNOWN | literal]). If you specify UNKNOWN, then the query optimizer will use statistical data to figure out the value to optimize.
A little about table hints: according to MSDN, I don’t really see a clear border between query and table hints. There’s a link for them at the end of this post, too. You use table hints in your FROM clause, with a WITH clause. The syntax is:
SELECT * FROM SomeTable WITH (HOLDLOCK)
You can specify locking granularity levels in a table hint (the values are the same as above), and you can specify which index to use. (WITH INDEX(0)). You can specify an ID or index name here. Notice that 0 is used for a clustered index scan (if no such thing exist on the table, than a table scan), and 1 is for a clustered index scan or seek. If 1 is specified, but no clustered index exist on the table, and error is raised.
So much for optimizer hints. The next thing to cover is Common Table Expressions (CTEs). These little beasts are very useful, because they allow you to recursively call a routine. Note that CTEs are only temporary objects; they won’t live through the execution of the current query.
CTEs are excellent when you need one of the following:
- Create a recursive query.
- When you don’t want to store metadata in memory, but need a view.
- Enabling grouping by a column that resulted from a scalar subselect, or a nondeterministic function.
- Referencing the resulting table multiple times in the statement.
The next objective is the topic of search conditions. What you should know is that from an optimization point of view NOT is a bad thing. Evade it as long as you can. Also don’t use too much ORs in a query, because they result in a much bigger data that must be scrolled through (for SQL Server, of course). Be aware of NULLs. Comparisons with a NULL value end up NULLs too.
The last objective here is the question of GROUPING SETS. Before SQL Server 2008 you could use two GROUP BY methods to return multiple levels of aggregated data in a result set: CUBE and ROLLUP. They are still available to use, but a third extension came in the form of GROUPING SETS(). You can achieve the same results with GROUPING SETS that you received with CUBE or ROLLUP. I’d consider reading through the MSDN article, because this topic needs some explanation I cannot provide to you – but some tables surely can.