Graphical execution plan  - Introduction

               

When it comes Query optimization we don’t know from where to start. This series of articles will take you through or give you general idea about What is query optimization? I have tried to keep language as possible as simple. Without wasting your time more let’s check it out.

Let me give you general outline about Graphical execution plan

Graphical execution plan consists of total 79 operators, which are divided into 4  main categories.

·         Logical and physical operators

(query execution or (DML) statements.)

·         Cursor physical operators

(Transact-SQL cursor operations)

·         Parallelism physical operator

(Parallelism operations.)

·         Language element

(Assign, Declare, If, Select (Result), While)

The graphical operators are divided into 3 categories as shown above based on color code. In this article we will discuss only important operators used frequently

Before going further let’s understand What happens when we submit query to SQL SERVER ?

                The query submitted buy user gets processed in following steps

1)       Query parsing

2)      Generation of execution plan by query optimizer

3)      Query forwarded to storage engine which process actual query

Now we will see each step in details

1)                Query parsing

When you submit a query to SQL Server, it  gets checked for any syntax error ,if any present, sends error to user. When query is parsed , a parsed tree is generated which is nothing but logical steps to perform query submitted . Now this generatedParsed tree or Query tree is submitted to Query optimizer.

2)              Query optimizer

Using the query processor tree and the information about data present (like indexes, what types of joins can be used, statistics about data) it works out to find out What will be the optimal way to execute the query?  Finds it and generates an execution plan. It also considers CPU and IO cost.

                               

When Query is submitted to optimizer , it generates many Query plans and decide which query plan will be beneficial to use in terms of cost (CPU ,IO, EXECUTION TIME)

                               

3)      Query forwarded to storage engine which process actual query

Query optimizer then sends optimized Query plan generated by Query optimizer to storage engine to retrieve data from database or perform operation specified.

 

You can use following query to find out cached query plans in plan cache

SELECT      [CAP].[REFCOUNTS]

            ,[CAP].[USECOUNTS]

            ,[CAP].[OBJTYPE]

            ,[SQT].[DBID]

            ,[SQT].[OBJECTID]

            ,[SQT].[TEXT]

            ,[QP].[QUERY_PLAN]

FROM SYS.DM_EXEC_CACHED_PLANS CAP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CAP.PLAN_HANDLE) SQT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(CAP.PLAN_HANDLE) QP

ORDER BY [USECOUNTS]

Before going further lets discuss what is seek and scan

                Scan : Scan operation looks through all data pages or index pages to for data required by query

                Seek : Seek operation traveses through only subset of rows to  provide data required by query

Seek operation is more effiecent than Scan

Let’s learn each important common operator