Understand SQL Server optimizer decisions

Question

I want to understand how SQL server optimizer takes decisions to optimize and process the SQL query. Is there any resource which can guide me to do so ?

Answer 1

There is not a really handy document, but to start with see Query processing architecture guide

Answer 2

Hi @Shivani T

Query optimization is a CPU-intensive operation. The process to sift through plans requires significant computing resources and to find the best plan may require more time than is available. As a result, a balance must be maintained between the resources needed to optimize the query, the resources required to execute the query, and the time we must wait for the entire process to complete. As a result, the optimizer is not built to select the best execution plan, but instead to search and find the best possible plan after a set amount of time passes. It may not be the perfect execution plan, but we accept that as a limitation of how a process with so many possibilities must operate.

Please refer to this article for more details: Query optimization techniques in SQL Server: the basics.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.