asked
HOW DOES SQL SERVER ENGINE FIGURE OUT ?
When a batch or remote procedure call (RPC) is submitted to SQL Server, the server checks for the validity and correctness of the query plan before it begins executing. If one of these checks fails, the batch may have to be compiled again to produce a different query plan. Such compilations are known as recompilations. These recompilations are generally necessary to ensure correctness and are often performed when the server determines that there could be a more optimal query plan due to changes in underlying data. this is a part of microsoft whitepaper. i just wanna know how does the sql data engine figures out that there could be a better plan that the stored one.
In general SQL Server will use the same execution plan forever unless there are changes. Changes to the query structure itself will lead to a new execution plan. Changes to the underlying structure, the addition of new indexes or changes to old ones will require a new execution plan. Changes to the statistics of the data, usually caused by data changes, but also manually initiated, can cause the creation of a new execution plan. If the plan is removed from cache due to age and pressure on the cache or because of manual extraction, a new execution plan will be created. If temporary tables are used in the query, then a new execution plan has to occur every time in order to allow for the creation of the temporary object. How and when SQL Server determines that it needs a new execution plan are well known. How it creates that execution plan.... that's magic.
it means that if anything changes,it will create a new execution plan,compare it with the stored plan and then choose accordingly
No, if anything changes, it creates a new plan. It may create a worse plan. This happens a lot due to parameter sniffing (where parameter values are tested against statistics and used as part of the information to create the execution plan. Normally a good thing, but can occasionally lead to be results). Compiles & recompiles do not compare one plan to another. They do, internally, try various plans, but, again, the algorithm there is unknown outside Microsoft.
There are a couple of smart answers, such as If you knew that you'd be working on SQL Server or Its magic At the end of the day, that's what you pay for when you get buy the license. At SQLBits VI Conor Cunnigham explained how he was involved. It was a great talk and I would recmmend watching it from here: [
http://www.sqlbits.com/Speakers/Conor_Cunningham/Default.aspx][1] [1]:
http://www.sqlbits.com/Speakers/Conor_Cunningham/Default.aspx
The optimizer is the most complex part of SQL Server and it figures out how to better execute a query and come up with a better plan(after considering many plans) than the stored one using a set of rules. Optimization has three steps: transaction processing(TP), quick plans and Full Optimization. You can read about them briefly in the book [Professional SQL Server 2008 Internals and Troubleshooting][1] or [Microsoft SQL Server 2008 R2 Unleashed:][2] And as FatherJack pointed out we are ultimately paying licence for the optimizer doing this. [1]:
http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284/ref=sr_1_1?ie=UTF8&s=books&qid=1277748380&sr=1-1 [2]:
http://www.amazon.com/Microsoft-SQL-Server-2008-Unleashed/dp/0672330563
