How to get execution costs of query with out looking at the graphical execution plan

Problem: How to get the execution cost and number of estimated rows results from an execution plan?

Solution: : As Execution plans are sometimes hard to monitor from an application calls or third party reporting tools. There are some dynamic management views which capture this information and provide the results like Estimated Subtree Cost, Estimated Rows and Actual Rows.

 sys.dm_exec_cached_plans

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Each query or procedure requests are sent as a hash id in the form of plan_handle, this identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the following dynamic management functions:

sys.dm_exec_sql_text - Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_exec_query_plan - Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_exec_plan_attributes - Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan.

 The above dynamic management views will be helpful for us to monitor the queries in an application perspective to optimize the query plan and find expensive queries in a split second, as hassle free instead of going through each procedure and executing it from the ssms and viewing the graphical execution plan and going through each operators to see the costs.

Example: As most of the common things we look are for the bottle necks in a query and it becomes hard for us to monitor the query when we don’t have the results in a pin point location. So, here I am trying to get specific queries where the estimated total subtree cost is higher than 3.0. For example, I am going write a bare select everything from address table and this is how its execution plan looks like and let’s see if we can reproduce the same results using our dynamic views and scripts.

--/*Checks the execution cost of each satatement and as well as the operations in the execution plan*/

;WITH plans

AS (

       SELECT q.[Text] AS StatementText

                ,CAST(eqp.[query_plan] AS XML).value('(//@EstimatedTotalSubtreeCost)[1]', 'VARCHAR(128)') AS [Estimated Total SubtreeCost]

             ,CAST(eqp.[query_plan] AS XML).value('(//@EstimateRows)[1]', 'VARCHAR(128)') AS [Estimated Number of rows]

                                                 ,CASE WHEN ecp.objtype='Proc' THEN 'Stored procedure'

                                                 WHEN ecp.objtype='Prepared' THEN  'Prepared statement'

                                                 WHEN ecp.objtype='Adhoc' THEN 'Ad hoc query'

                                                 WHEN ecp.objtype='eplProc' THEN 'Replication-filter-procedure'

                                                 WHEN ecp.objtype='Trigger' THEN 'Trigger'

                                                 WHEN ecp.objtype='View' THEN ' View'

                                                 WHEN ecp.objtype='Default' THEN 'Default'

                                                 WHEN ecp.objtype='UsrTab' THEN 'User table'

                                                 WHEN ecp.objtype='SysTab' THEN 'System table'

                                                 WHEN ecp.objtype='Check' THEN 'CHECK constraint'

                                                 WHEN ecp.objtype='Rule' THEN 'Rule' END as QueryProcessedtype

                                                 ,CAST(eqp.[query_plan] AS XML).value('(//@PhysicalOp)[1]', 'VARCHAR(128)') AS [type of operation]

                                                 ,ecp.usecounts as  [Execution Counts]

             ,DB_NAME(eqp.[dbid]) [Executed Databse]

             ,Object_Schema_name(eqp.objectid)+Object_name(eqp.objectid) NameoftheObject

             ,row_number() OVER (PARTITION BY eqp.objectid ORDER BY ecp.usecounts) Execution_rowpartition

       FROM sys.dm_exec_cached_plans AS ecp WITH (NOLOCK)

       CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp

       CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q

       WHERE  eqp.[dbid] = db_id()-- remove this clause if you want to see across all the databases.

       )

SELECT DISTINCT *FROM plans

WHERE CAST([Estimated Total SubtreeCost] as FLOAT)>3

--AND Execution_rowpartition = 1 --uncomment this if you want to see only row per the object

OPTION ( RECOMPILE ,MAXDOP 1);

GO

As we can see these both results are same it would be handy for us to run this query to check all the queries whose estimated costs are higher and try to optimize the queries.