Introduction:
In this blog, we will explore what Show Plan permission is and how it can help DBAs and developers gain insights into the Estimated and Actual Execution Plans.
Show Plan is a permission that can be granted by a SQL Server administrator to a developer. Note: Developers with Show Plan permission can access this feature.
Estimated Execution Plan:
- It shows how SQL Server thinksit will execute a query without actually running it.
Actual Execution Plan:
- It shows the actual steps SQL Server took to execute the query, including runtime metrics like row counts and execution times.
SQL Server provides several showplan options, allowing you to view different types of execution plans:
- SET SHOWPLAN_XML ON;: Shows the estimated execution plan in XML format without executing the query.
- SET SHOWPLAN_TEXT ON;: Provides the estimated execution plan in a text format.
- SET STATISTICS PROFILE ON;: Displays the actual execution plan with runtime statistics after the query has been executed.
- SET STATISTICS IO ON;and SET STATISTICS TIME ON;: Provides detailed I/O and timing information about the query execution.
Steps to assign Show Plan Permission to a user:
SQL DBA can provide this permission to a user by using below commands:
USE [DB_NAME]
GO
GRANT SHOWPLAN ON DATABASE::[DB_NAME] to [USERNAME]
GO
One example of Estimated Execution Plan:
SET SHOWPLAN_XML ON Method
It shows the estimated execution plan in XML format without executing the query.
With the given output we can get to know about the estimated plan.
One example of Actual Execution Plan:
SET STATISTICS PROFILE ON
It displays the actual execution plan with runtime statistics after the query has been executed.
With the given output we can get to know about the actual plan.
Conclusion:
Show Plan permission is a valuable tool for developers, allowing them to analyze both Estimated and Actual Execution Plans. By understanding these plans, developers can identify inefficiencies in their queries, such as expensive operations or missing indexes, and make informed decisions to optimize query performance. This ultimately leads to more efficient database operations and better application performance.
