2

I have read some articles on SQL Server query optimization. The point I get is SQL Server generates a query execution plan for each stored procedure when it's executed for the first time. Those execution plans are stored in the database, and will be used for future executions of the same SPs.

The part I didn't understand is: How is this "query execution plan" used by SQL Server to optimize the query execution? And can we do anything to optimize the query execution plans it generates?

1
  • Pedantic maybe but: a query execution plan isn't used for optimizing query execution. A query execution plan is the outcome of said optimization and it will tell you the way the server will fetch the data. You can use this information to identify if your data is being fetched efficiently.
    – Pieter B
    Jul 23, 2021 at 8:28

5 Answers 5

8

If you're a programmer, you'll notice that SQL doesn't look much like normal code.

Normal code is written in what's known as an imperative style: it tells the computer what to do. SQL, on the other hand, doesn't tell the database what to do; it tells the database what you want instead, and leaves it up to the DBMS engine to figure out what to do to retrieve it. This is known as declarative style code, and it makes your database queries much simpler. But it doesn't actually tell the database what to do.

When the database gets a SQL query, the first thing it has to do is figure out how to take a request that says "I want this data" and turn it into a way to retrieve that data in a reasonably efficient manner. So it looks through the query and the affected tables and builds itself a set of imperative commands to execute. This is the query plan. Because building the query plan can take time, most DBMSs will cache them under certain circumstances when the engine has reason to expect that you'll reuse the same query.

There are ways to review the query plan, and even force it to use certain imperative commands at certain points in the query, but the exact mechanism tends to differ quite a bit between one DBMS and another. For details, try Googling "query plan tuning" plus the name of your database engine; you'll likely find quite a bit of information.

1
  • 1
    If you're an imperative programmer that would be the normal style. Though as a functional programmer, I have to say that SQL code doesn't look normal to me either.
    – Ryan Reich
    Mar 26, 2016 at 1:06
3

Mason Wheeler's answer is very good. There are a few more points to add:

When the database engine first executes a query, it doesn't just come up with one execution plan. It comes up with a lot of them. Barring bugs, all of these execution plans are logically equivalent: that is to say, they will all generate correct results for the query.

However, what makes them different is how much time it's going to take to execute the plan. Simplifying a little, I'm going to say "how many disk accesses" instead of "how much time". That's not strictly true, but it's close. Associated with each execution plan is an estimated cost. The one with the lowest estimated cost is the one that gets used.

Now, your original question can be rephrased as is there anything a dba or a programmer can do to cause the query optimizer to find a better plan, and to pick it? The answer is yes...but.

First off, index design is critical to execution plans. Very often, the presence of the right index can result in the query optimizer finding and picking a strategy that is up to a hundred times as fast. But you have to be careful. Adding an index will slow down some other database operation, and you have to be careful not to do more harm than good.

A second thing that can be done is to analyze tables frequently. This is different for different DBMS products, but generally analyzing tables stores data about the table that helps the optimizer make better cost estimates.

A third thing that can be done is to rewrite queries to be more logical and straightforward. Sometimes, skilled programmers learn how to write SQL that reads like a bad imperative language instead of a good declarative language. This is generally a case of not trusting the optimizer to do its job. Sometimes, a human can outsmart the optimizer. More often, you get better results by just letting the optimizer optimize.

A fourth thing that can be done is to provide what used to be called "hints" in Oracle in with the query. I don't know the equivalent word with other products. I haven't seen this done for 15 years, but I suppose it's still possible. About 20 years ago, this was almost a necessity for getting things done. The world has changed a lot since then.

The long and the short of it is that the optimizer is your friend, albeit an imperfect one. Let the optimizer help you.

0

The easiest thing to do/a good place to start is the Database Engine Tuning Advisor. Typically, you'll get suggestions on indexes to apply to specific tables.

You'll still need to evaluate and test those indexes and weigh any benefits against some of the drawbacks you may encounter (e.g. Inserts, Updates and Deletes slow down due to index rebuilding overhead.).

Structuring your data properly can go a long way as well to improve performance. I would suggest doing this before tackling more advanced settings in your query. Novices may not be able to notice the unintended consequences until it is too late.

0

You can compare a query plan to a compiled program. Normally, each SQL would receive an individually calculated query plan, which, of course, causes some overhead to compile.

In a stored procedure, or in a parameterized SQL query, the compile will be done once for all later queries. This requires separation of SQL/procedure code from the actual parameter values. Reusing will normally not work if you concatenate parameter values as strings into the SQL, a bad practice anyway. SQL Server recognizes equal parameterized SQL (mostly delivered through the sp_executesql procedure) by string equality, so the @parameterPlaceholders must be the same, while the bound values can differ. Parameterized queries are, in terms of speed and resources, equivalent to stored procedures, except that they usually have no flow control.

The downside may be that the initial compile evaluates parameter values and table statistics, called Parameter Sniffing in SQL Server, and creates a query plan for them, which may be different than an optimal plan for later queries.

Imagine a query which always polls the newest entries in a table: the initial query will start with a min value and scan the whole table, so the same query will still do a full table scan later, even if only supposed to pick the last few of several million rows!

For this reason, one should look after what is happening with queries, even if the precompiled query plans are generally good and save the database a lot of time and CPU work.

-1

@Eric Hart The values that are concatenated as strings are simpler but create more headaches in the future; Cleaning up tables later from string replacement can get tedious parametized values offer a set of controls on how the data will be accessed systematically without CPU bog time overhauling the system resources as you said.

Wouldn't it be great to have the set of values queried bound to each control with programmatic naming schemes yet have similar value stacks, such as first query runs is last query to execute to clean out the table from bottom top rather than top to bottom.

In your example of new polls, create a sql select statement each row systematically, skipping rows that have string parameters and old updating parametrized values first and save the stored procedures for off peak hours on the system - data equality is creating your bound values that are static and not changed, then execute with sp_executesql proc when the data requires concatenated strings to be ingested later point in time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.