Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Let the optimizer drive

By Tony Davis,

There is something disconcerting in the idea of the Query Optimizer. For a programmer, the desire to define not only what the result of a process should be, but also how that process should take place, is pretty ingrained. Many find deeply unsettling the idea that the Query Optimiser merely uses your SQL code to try to understand what data you need, and then decides for itself on the most efficient plan to retrieve that data.

The optimizer is a highly-impressive and complex piece of software, capable of evaluating tens of thousands of different possible join orders, physical operators, data access paths and methods, and so on, in the blink of an eye, and coming up with a highly efficient plan, in most cases. For many it is a black box and they would rather that it remained so.

However, a great deal has changed in the last couple of years to demystify the whole subject of execution plans, the physical 'showplan' operators, query statistics, and query compilation.  We now have a group of people such as Grant Fritchey, Gail Shaw, Fabiano Amorim, Craig Freedman, Holger Schmeling, Paul White and Benjamin Nevarez, who are writing about these topics in an approachable way. For anyone involved with databases, it is worth reading their work as it helps to understand and give context to a number of 'best practices' that might otherwise seem like arbitrary decrees.

Beyond the obvious cautions regarding use of ad-hoc queries, or letting statistics become out-of-date, the overall message for the developer is clear: let the optimizer drive. The developer can, and should, concentrate on creating efficient and effective processes, write straightforward queries that are sympathetic to the database's indexing strategy, and allow the Query Optimiser to create the most effective plan.

When a stored procedure turns out to be a slow performer in development, for example, it's tempting, having stared at the execution plan, to decide that the optimizer got it wrong, and to try to 'hint' your way out of trouble, or alternatively to throw more single-use indexes at the problem. Instead, it's better to back off a bit and see if the algorithm itself is inefficient, the process is long-winded. In other words, give the Query Optimiser the space it needs to do its job well.

Cheers,

Tony.

Total article views: 168 | Views in the last 30 days: 1
 
Related Articles
FORUM

Query Optimizer

Query Optimizer

FORUM

Query Optimizer

Query Optimizer

FORUM

Query Optimize

Query Optimize

FORUM

Witch query is more efficient??

Witch query is more efficient??

ARTICLE

Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and t...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones