Cached Plans & decimal representation

Background

Looking for a particular pattern in my cached plans.

But, getting back an error reading “Error converting data type nvarchar to numeric“.

 

Code

Original

Here is a versely reduced query:

Code


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT


    [EstimateRows]
        = RelOp.op.value
            (
                  N'@EstimateRows'
                , N'varchar(50)'
            )

    , [EstimateIO]
        = RelOp.op.value
            (
                  N'@EstimateIO'
                , N'varchar(50)'
            )

    -- converting to decimal(18,2)
    , [EstimateCPU]
        = RelOp.op.value
            (
                  N'@EstimateCPU'
                , N'decimal(18, 2)'
            )

    , [AvgRowSize]
        = RelOp.op.value
            (
                  N'@AvgRowSize'
                , N'varchar(50)'
            )			

    , [EstimatedTotalSubtreeCost]
        = RelOp.op.value
            (
                  N'@EstimatedTotalSubtreeCost'
                , N'varchar(50)'
            )			

    , QueryPlan
        = qp.query_plan

    , [queryPlanNode]
        =  RelOp.op.query('.')


    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)


Output

Image

Textual


Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

 

Revised

Objective

Convert each RelOp.op.value to cast varchar([n])

Code


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT


    [EstimateRows]
        = RelOp.op.value
            (
                  N'@EstimateRows'
                , N'varchar(50)'
            )

    , [EstimateIO]
        = RelOp.op.value
            (
                  N'@EstimateIO'
                , N'varchar(50)'
            )

    -- converting to varchar(50)
    , [EstimateCPU]
        = RelOp.op.value
            (
                  N'@EstimateCPU'
                , N'varchar(50)'
            )

    , [AvgRowSize]
        = RelOp.op.value
            (
                  N'@AvgRowSize'
                , N'varchar(50)'
            )			

    , [EstimatedTotalSubtreeCost]
        = RelOp.op.value
            (
                  N'@EstimatedTotalSubtreeCost'
                , N'varchar(50)'
            )			

    , QueryPlan
        = qp.query_plan

    , [queryPlanNode]
        =  RelOp.op.query('.')


    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)


Output

Image

Explanation

  1. Noticed that EstimateCPU came back as 8.4e-005
    • That means it is using scientific notation
  2. Thought back that converting to decimal can be problematic

 

Revised

Objective

Convert each appropriate column to cast RelOp.op.value as float:

Code


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>
SELECT


    [EstimateRows]
        = RelOp.op.value
            (
                  N'@EstimateRows'
                , N'varchar(50)'
            )

    , [EstimateIO]
        = RelOp.op.value
            (
                  N'@EstimateIO'
                , N'varchar(50)'
            )

    -- converting to float
    , [EstimateCPU]
        = RelOp.op.value
            (
                  N'@EstimateCPU'
                , N'float'
            )

    , [AvgRowSize]
        = RelOp.op.value
            (
                  N'@AvgRowSize'
                , N'varchar(50)'
            )			

    , [EstimatedTotalSubtreeCost]
        = RelOp.op.value
            (
                  N'@EstimatedTotalSubtreeCost'
                , N'varchar(50)'
            )			

    , QueryPlan
        = qp.query_plan

    , [queryPlanNode]
        =  RelOp.op.query('.')


    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)


Output

Image

Explanation

  1. On Row 23, Column Estimate CPU
    • 8.4E-05 comes back as scientific notated
    • And, it is still good

 

Summary

Not a big deal, it is just one of those things we forget.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s