Reading and understanding an execution plan graphical presentation

Problem: Reading and understanding an execution plan graphical presentation

Solution:

An execution plan is generally a logical algorithm of query processor chooses to execute the query.

Execution plan is portrayed graphical from a SSMS output window, when you select the include estimated or actual execution plan.

Today we are going to see what all operations are important to look for and how to read the execution plan and understand it step by step.

It is a norm to read the execution plan from right to left and from top to bottom to understand what operations are done insider the query optimizer. When you have multiple batch queries in the same procedure, each batch gets individual execution plan and cost relative to batch.

Execution plans are shown as nodes and each node is represented as an icon and the relations are shown by parent or child based on the split of a query either a trigger or a nested statement or a cursor. 

When a graphical interpretation of execution plan is shown, it shows in a symbol and each symbol represents a logical operation. And each logical operator symbol connects to an arrow to represent the flow of data. An execution plan comprises of icons and arrows to show data flow and tool tip for each operator. There are around 70 icons, just like road signs these also represent each icon for a purpose of logical operation.

For example: 

Individual colors have been associated with each of the three icon types: iterator (logical and physical operators) icons are blue, cursors icons are yellow, and language elements are green.

On each operator if you hover over or put the cursor on the icon, the tool tip shows the below information:

For example :

Physical Operation	The physical operator used, such as Hash Join or Nested Loops. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates. This can cause the query optimizer to choose a less-efficient query plan than otherwise expected. For more information about column statistics, see Using Statistics to Improve Query Performance.
Logical Operation	The logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip.
Estimated Row Size	The estimated size of the row produced by the operator (bytes).
Estimated I/O Cost	The estimated cost of all I/O activity for the operation. This value should be as low as possible.
Estimated CPU Cost	The estimated cost of all CPU activity for the operation.
Estimated Operator Cost	The cost to the query optimizer for executing this operation. The cost of this operation as a percentage of the total cost of the query is displayed in parentheses. Because the query engine selects the most efficient operation to perform the query or execute the statement, this value should be as low as possible.
Estimated Subtree Cost	The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.
Estimated Number of Rows 1	The number of rows produced by the operator.

Read More

What is an execution and how to get execution plan of a query?

Problem: What is an execution and how to get execution plan of a query?

Solution: Query is generally a block of declarative code executed against a database. When the execution happens SQL Server processes the query by the database engine. The engine while processing the query uses query optimizer (query processor) translates the request to logical algorithm of the data flow called as execution plan.

The Execution Plan options graphically display the data retrieval methods chosen by the SQL Server query optimizer. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server rather than the tabular representation produced by the Transact-SQL SET statement options SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT, or the XML for representation produced by SET SHOWPLAN_XML. The graphical display is very useful for understanding the performance characteristics of a query. SQL Server Management Studio shows which statistics are missing, thereby forcing the query optimizer to make estimates about predicate selectivity, and then permits those missing statistics to be easily created.

There are two types of execution plans one is estimated and the other is actual.

• Estimated execution plan is based of query processor analysis by parsing the script and this can be generated without execution the script.

You can see the below screen shot to select the feature in SSMS while running a query press(CTR+L)

• Actual execution plan is based on the execution of the script and the processed algorithm to fetch the query work.

You can see the below screen shot to select the feature in SSMS while running a query press(CTR+M)

These following set options can enable the execution plan to show.

SET SHOWPLAN_ALL (Transact-SQL) -Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

SET SHOWPLAN_TEXT (Transact-SQL) -Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.

SET SHOWPLAN_XML (Transact-SQL) -Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document

Syntax:

SET SHOWPLAN_ALL { ON | OFF }

SET SHOWPLAN_TEXT { ON | OFF }

SET SHOWPLAN_XML { ON | OFF }

Example:

In this example, I would like to show we can see an execution plan of a query. I would like to write a SQL statement and query employees table, let’s see how the query processor will translate this request into a logical flow.

USE DB;

GO

SET SHOWPLAN_XML ON;

GO

-- Sample query.

SELECT  [EmployeeID]

      ,[Name]

      ,[PhoneNumber]

  FROM [dbo].[Employees]

  WHERE EmployeeID =100136;

GO

SET SHOWPLAN_XML OFF;

OUTPUT:

After clicking at the xml the plan will be shown as below.

Read More

What are different ways to get total number of rows in a table.?

Problem: What are different ways to get total number of rows in a table.?

Solution:

A Table stores data in the format of columns and rows. Every Table stores the total number of rows in different aspect. There are few different ways to retrieve the number of rows in a table.

When you query the table directly for the total number of rows there will be Some I/O cost on the table as there will be an index scan or table scan operation performance operation in the execution, to avoid that there are few alternatives. Let’s deep dive into that.

·         Space utilization System Stored Procedure - sp_spaceused

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

·         Partitions - sys.partitions

Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether they are explicitly partitioned.     

·         Indexes - sys.indexes

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

·         SysIndexes - sys.sysindexes

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead.

·         Partition Stats - sys.dm_db_partition_stats

Returns page and row-count information for every partition in the current database.

Some of this information might not be accurate because these are based on the updates that SQL server writes to these views.

Example:

In this example, I would like to show all possible ways to get the total number of rows in a table.

--1. Querying directly the table

select Count(1) as Rows from dbo.Employees

OUTPUT:

Rows

2826203

--2. System Stored Procedure

EXEC sp_spaceused 'dbo.employees'

OUTPUT:

name	rows	reserved	data	index_size	unused
Employees	2826203	92632 KB	92296 KB	208 KB	128 KB

--3. Partitions

select SUM(rows) as total_count from sys.partitions p

INNER JOIN sys.tables t ON t.object_id=p.object_id

where t.is_ms_shipped=0

and  object_name(t.object_id)= 'Employees'

and schema_name(t.schema_id)='dbo'

and p.index_id=1

OUTPUT:

total_count

2826203

--4. Partitions + Tables+ Indexes

SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows

FROM sys.tables TBL

INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id

INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id

AND PART.index_id = IDX.index_id

WHERE TBL.name = 'Employees'

AND IDX.index_id < 2

GROUP BY TBL.object_id, TBL.name;

OUTPUT:

object_id	name	rows
542624976	Employees	2826203

--5.Indexes( This particular view is not supported in future version starting 2016)

select rows as total from sysindexes

where id = OBJECT_ID('[dbo].[Employees]') and indid < 2

OUTPUT:

total

2826203

--6.Partition Statistics

SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows

FROM sys.dm_db_partition_stats

WHERE object_id = OBJECT_ID('[dbo].[Employees]')

AND index_id < 2

GROUP BY OBJECT_NAME(object_id);

OUTPUT:

table_name	rows
Employees	2826203

Now as we can see almost all the queries above have the same results. So, let me compare the Execution costs from all these batch queries at a single grid to see how much of a variance it is having. I used xml of the execution plan to compare the batch query costs and statements total cost, please see the below screenshot for comparison results.

 OUTPUT:

Read More

How to enable, disable or drop all or specific plan guides?

Problem: How to enable, disable or drop all or specific plan guides?

Solution: sp_control_plan_guide

This procedure controls or allows us to do few things

·         Drop one or all plan guides.

·         Disable one or all plan guides.

·         Enable one or all plan guides.

We can right click on the plan guides from a SSMS to enable or disable but it does not have a drop option. You can see the below screenshot on where to find the enable or disable option.

What is a Plan Guide:

For every query, there will be an execution plan which is actual and estimated by the SQL Server Optimizer. Plan guides lets you change or optimize a query execution plan performance without changing the query of a procedure. A plan guide has a major influence on optimization of specific query text with the help of query hints attached to the plan guides.

If you want to learn in detail on how to create a plan guide, please go through this article. How to create a plan guide.

Syntax:

sp_control_plan_guide [ @operation = ] N'<control_option>' 

  [ , [ @name = ] N'plan_guide_name' ] 

<control_option>::= 

{  

    DROP  

  | DROP ALL 

  | DISABLE 

  | DISABLE ALL 

  | ENABLE  

  | ENABLE ALL 

} 

Example:

In this example, I would like to show we can create an explicit plan guides to reduce the degree of parallelism using MAXDOP OPTION Hint and then disable, enable and drop the plan guides once after creation.

-- creates a plan guide  based on a SQL statement 

EXEC sp_create_plan_guide  

    @name = N'maxdop plan guide on Address table',  --name of the plan guide

    @stmt = N'select * from [dbo].[Address]

order by AddressID DESC',  --statement to improve the query optimization

    @type = N'SQL',  -- object or SQL or Template this is the scope of the Plan

    @module_or_batch = NULL,   --Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears.

    @params = NULL,  --Specifies the definitions of all parameters that are embedded in statement_text.

    @hints = N'OPTION (MAXDOP 1)';  --Specifies an OPTION clause to attach to a query that matches

--Disable the plan guide. 

EXEC sp_control_plan_guide N'DISABLE', N'[maxdop plan guide on Address table]'; 

GO 

--Enable the plan guide. 

EXEC sp_control_plan_guide N'ENABLE', N'[maxdop plan guide on Address table]'; 

GO 

--Drop the plan guide. 

EXEC sp_control_plan_guide N'DROP', N'[maxdop plan guide on Address table]';

Example 2: In this example, I would like to stop all custom plan guides, if in case the database has changed a lot we might want to drop the plan guides or disable the plan guides. As they longer remain to perform the same. Below are few snippets to drop, disable and enable all plan guides.

--Disable all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'DISABLE ALL'; 

--Enable all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'ENABLE ALL'; 

--DROP all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'DROP ALL'; 

Read More

What are plan guides and how to use a custom plan guide?

Problem: What are plan guides and how to use a custom plan guide?

Solution: sp_create_plan_guide

Plan Guides:

For every query, there will be an execution plan which is actual and estimated by the SQL Server Optimizer. Plan guides lets you change or optimize a query execution plan performance without changing the query of a procedure. A plan guide has a major influence on optimization of specific query text with the help of query hints attached to the plan guides.

You can see the below screenshot to see where a plan guide resides in a SQL Server Management studio.

Benefits of plan guides:

·         Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

·         You can disable or enable the plan guides when you think that this plan guide no longer remains implied for the queries.

How to Create a Plan guide:

In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. When the query executes, SQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

you can use gui from SSMS to create a plan guide as well, by right clicking on the plan guide folder.

Important Notes:

The total number of plan guides you can create is limited only by available system resources.

Downsides of Plan guides

Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Plan guides should not be used to influence most of the query load of a deployed application.

Syntax:

sp_create_plan_guide [ @name = ] N'plan_guide_name'  

    , [ @stmt = ] N'statement_text'  

    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  

    , [ @module_or_batch = ]  

      {   

                    N'[ schema_name. ] object_name'  

        | N'batch_text'  

        | NULL  

      }  

    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   

    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   

                 | N'XML_showplan'  

                 | NULL }  

Example:

In this example, I would like to show we can create an explicit plan guides to reduce the degree of parallelism using MAXDOP OPTION Hint.

To explain what I am trying to achieve let me show how a query optimizer chose to return the results before I have a specific plan guide on the SQL Statement and then create a plan guide for that query and observe the difference.

Before plan guides

This is how the queries execution plan looks before I have a specific execution plan. As you can see it reached to a degree of parallelism to 8. Which means it uses 8 parallel operators to get the task done which will chew the CPU for a while.

Creating a plan guide to use attached option of MAXDOP to the query:

-- creates a plan guide  based on a SQL statement 

EXEC sp_create_plan_guide  

    @name = N'maxdop plan guide on Address table',  --name of the plan guide

    @stmt = N'select * from [dbo].[Address]

order by AddressID DESC',  --statement to improve the query optimization

    @type = N'SQL',  -- object or SQL or Template this is the scope of the Plan

    @module_or_batch = NULL,   --Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears.

    @params = NULL,  --Specifies the definitions of all parameters that are embedded in statement_text.

    @hints = N'OPTION (MAXDOP 1)';  --Specifies an OPTION clause to attach to a query that matches

After the plan guide

This is a forced execution plan as our query statement is specified on the plan guide it can choose the best optimized plan guide by reducing the parallelism.

Read More