In this blog we are going to see some basic things on how to interpret a execution plan for a query . The first first step on enhancing a query for optimal performance is to understand the query execution on cost basis , which part of the of the query is costly and which part is not . To do so, SQL Servers query execution plan is the best choice, by which we can point out which part of our query is more costlier.
Learning a query execution plan is like learning a new language, which is iconic language. Just explaining each and every icon of this language will be wage for you. More things on execution plan can be garbed while practicing it .
Before dive deeper into the subject , we should know the limitations of the execution plan. Execution plan doesn’t tell you below things
- Locks acquired
- Wait statistics
- Data is in the cache or not
- Not a replacement of SET STATISTICS IO
- Not a replacement of SET STATISTICS TIME
Below are some easy usage ways to get full benefit of the execution plan
“We have to read the execution plan from right to left and from down to top”.
I am using AdventureWorks2008 database on my examples. In this blog we will see only some basic things and spice up things on the coming series
select * from DatabaseLog |
Just run the above query,will give you plan like below. In which we are seeing a table scan, this are some hazardous symbols. Need to be avoided anywhere from the plan
One of the best way to optimize the query is to run the original one and the optimized one on the same query window, as shown in the diagram. By this way you can compare cost between them. Which will tell are you travelling in the right direction towards optimization or not.
The above diagram is just showing you, which one of the query is more costly.
Query 1. clustered index scan operation
We can see that a clustered index scan operation is performed to retrieve the required data. If you place the mouse pointer over the Clustered Index Scan icon, to bring up the ToolTip window, you will see that the clustered index used was PK_Contact_ContactID and that the estimated number of rows involved in the operation.
We can see that a clustered index scan operation is performed to retrieve the required data. If you place the mouse pointer over the Clustered Index Scan icon, to bring up the ToolTip window, you will see that the clustered index used was PK_Contact_ContactID and that the estimated number of rows involved in the operation.
Indexes in SQL Server are stored in a B-tree (a series of nodes that point to a parent). A clustered index not only stores the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there can be only one clustered index per table. As such, a clustered index scan is almost the same in concept as a table
scan. The entire index, or a large percentage of it, is being traversed, row-by-row, in order to identify the data needed by the query.
scan. The entire index, or a large percentage of it, is being traversed, row-by-row, in order to identify the data needed by the query.
Query 2. clustered index seek operation
Index seeks are completely different from scans, where the engine walks through the rows to find what it needs. An index seek, clustered or not, occurs when the optimizer is able to locate an index that it can use to retrieve the required records. Therefore, it tells the storage engine to look up the values based on the keys of the given index. Indexes in SQL Server are stored in a B-tree (a series of nodes that point to a parent). A clustered index stores not just the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there
can be only one clustered index per table.
Index seeks are completely different from scans, where the engine walks through the rows to find what it needs. An index seek, clustered or not, occurs when the optimizer is able to locate an index that it can use to retrieve the required records. Therefore, it tells the storage engine to look up the values based on the keys of the given index. Indexes in SQL Server are stored in a B-tree (a series of nodes that point to a parent). A clustered index stores not just the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there
can be only one clustered index per table.
When an index is used in a seek operation, the key values are used to quickly identify the row, or rows, of data needed. This is similar to looking up a word in the index of a book to get the correct page number. The added value of the clustered index seek is that, not only is the index seek an inexpensive operation as compared to an index scan,
but no extra steps are required to get the data because it is stored in the index.
but no extra steps are required to get the data because it is stored in the index.
In the above example, we have a Clustered Index Seek operation carried out against the Person.Address table, specifically on the PK_Address_AddressId, which is happens to be both the primary key and the clustered index for this table.
No comments:
Post a Comment