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?
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.
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.
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.
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.
