“Why is this query running so slowly?”It’s one of the phrases most commonly heard by DBAs and database developers when dealing with OLTP systems.Luckily, SQL Server provides a range of native options for determining exactly what’s occurring under the hood. Using execution plans, it’s possible to see the exact roadmap the SQL engine is following to retrieve data. This article will review the basics of reading and interpreting execution plans, then dig deeper into the internal processes and mechanics used by the SQL Server optimizer.
Execution plans can become a very useful tool for every database developer. They provide a deeper understanding of the mechanisms applied in the processing of queries. This article will take a closer look at how execution plans are retrieved and how to read them.What Are Execution Plans?SQL is, to a great extent, a declarative language. The user defineswhatshould be done but does not specifyhowthe queries should be executed. There are many ways in which certain parts of an SQL statement can be processed. For example, predicates can be computed in any sequence and subqueries can be turned into joins if necessary.