SQL Server 2008 R2 Performance Dashboard workshop

-- Script must not be run in a transaction

-- 将218行中SQL Server2005  cpu_ticks_in_ms 改为 SQL Server 2008 之后的  ms_ticks

SET IMPLICIT_TRANSACTIONS OFF
IF @@TRANCOUNT > 0 ROLLBACK TRAN
GO

-- Options that are saved with object definition
SET QUOTED_IDENTIFIER ON        -- Required to call methods on XML type
SET ANSI_NULLS ON                -- All queries use IS NULL check
go

use msdb
go

if not exists (select * from sys.schemas where name = 'MS_PerfDashboard')
    exec('create schema MS_PerfDashboard')
go

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_WaitTypeCategory'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_WaitTypeCategory
go

create function MS_PerfDashboard.fn_WaitTypeCategory(@wait_type nvarchar(60))
returns varchar(60)
as
begin
    declare @category nvarchar(60)
    select @category =
        case
            when @wait_type like N'LCK_M_%' then N'Lock'
            when @wait_type like N'LATCH_%' then N'Latch'
            when @wait_type like N'PAGELATCH_%' then N'Buffer Latch'
            when @wait_type like N'PAGEIOLATCH_%' then N'Buffer IO'
            when @wait_type like N'RESOURCE_SEMAPHORE_%' then N'Compilation'
            when @wait_type = N'SOS_SCHEDULER_YIELD' then N'Scheduler Yield'
            when @wait_type in (N'LOGMGR', N'LOGBUFFER', N'LOGMGR_RESERVE_APPEND', N'LOGMGR_FLUSH', N'WRITELOG') then N'Logging'
            when @wait_type in (N'ASYNC_NETWORK_IO', N'NET_WAITFOR_PACKET') then N'Network IO'
            when @wait_type in (N'CXPACKET', N'EXCHANGE') then N'Parallelism'
            when @wait_type in (N'RESOURCE_SEMAPHORE', N'CMEMTHREAD', N'SOS_RESERVEDMEMBLOCKLIST') then N'Memory'
            when @wait_type like N'CLR_%' or @wait_type like N'SQLCLR%' then N'CLR'
            when @wait_type like N'DBMIRROR%' or @wait_type = N'MIRROR_SEND_MESSAGE' then N'Mirroring'
            when @wait_type like N'XACT%' or @wait_type like N'DTC_%' or @wait_type like N'TRAN_MARKLATCH_%' or @wait_type like N'MSQL_XACT_%' or @wait_type = N'TRANSACTION_MUTEX' then N'Transaction'
            when @wait_type like N'SLEEP_%' or @wait_type in(N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'WAIT_FOR_RESULTS') then N'Sleep'
            else N'Other'
        end

    return @category
end
go
GRANT EXECUTE ON MS_PerfDashboard.fn_WaitTypeCategory TO public
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1
    drop function MS_PerfDashboard.fn_QueryTextFromHandle
go

CREATE function MS_PerfDashboard.fn_QueryTextFromHandle(@handle varbinary(64), @statement_start_offset int, @statement_end_offset int)
RETURNS @query_text TABLE (database_id smallint, object_id int, encrypted bit, query_text nvarchar(max))
begin
    if @handle is not null
    begin
        declare @start int, @end int
        declare @dbid smallint, @objectid int, @encrypted bit
        declare @batch nvarchar(max), @query nvarchar(max)

        -- statement_end_offset is zero prior to beginning query execution (e.g., compilation)
        select
            @start = isnull(@statement_start_offset, 0),
            @end = case when @statement_end_offset is null or @statement_end_offset = 0 then -1
                        else @statement_end_offset
                    end

        select @dbid = t.dbid,
            @objectid = t.objectid,
            @encrypted = t.encrypted,
            @batch = t.text
        from sys.dm_exec_sql_text(@handle) as t

        select @query = case
                when @encrypted = cast(1 as bit) then N'encrypted text'
                else ltrim(substring(@batch, @start / 2 + 1, ((case when @end = -1 then datalength(@batch)
                            else @end end) - @start) / 2))
            end

        -- Found internal queries (e.g., CREATE INDEX) with end offset of original batch that is
        -- greater than the length of the internal query and thus returns nothing if we don't do this
        if datalength(@query) = 0
        begin
            select @query = @batch
        end

        insert into @query_text (database_id, object_id, encrypted, query_text)
        values (@dbid, @objectid, @encrypted, @query)
    end

    return
end
go
GRANT SELECT ON MS_PerfDashboard.fn_QueryTextFromHandle TO public
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_hexstrtovarbin'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_hexstrtovarbin
go

create function MS_PerfDashboard.fn_hexstrtovarbin(@input varchar(8000))
returns varbinary(8000)
as
begin
    declare @result varbinary(8000)

    if @input is not null
    begin
        declare @i int, @l int

        select @result = 0x, @l = len(@input) / 2, @i = 2
    
        while @i <= @l
        begin
            set @result = @result +
            cast(cast(case lower(substring(@input, @i*2-1, 1))
                when '0' then 0x00
                when '1' then 0x10
                when '2' then 0x20
                when '3' then 0x30
                when '4' then 0x40
                when '5' then 0x50
                when '6' then 0x60
                when '7' then 0x70
                when '8' then 0x80
                when '9' then 0x90
                when 'a' then 0xa0
                when 'b' then 0xb0
                when 'c' then 0xc0
                when 'd' then 0xd0
                when 'e' then 0xe0
                when 'f' then 0xf0
                end as tinyint) |
            cast(case lower(substring(@input, @i*2, 1))
                when '0' then 0x00
                when '1' then 0x01
                when '2' then 0x02
                when '3' then 0x03
                when '4' then 0x04
                when '5' then 0x05
                when '6' then 0x06
                when '7' then 0x07
                when '8' then 0x08
                when '9' then 0x09
                when 'a' then 0x0a
                when 'b' then 0x0b
                when 'c' then 0x0c
                when 'd' then 0x0d
                when 'e' then 0x0e
                when 'f' then 0x0f
                end as tinyint) as binary(1))
        set @i = @i + 1
        end
    end

    return @result
end
go
GRANT EXECUTE ON MS_PerfDashboard.fn_hexstrtovarbin TO public
go


if object_id('MS_PerfDashboard.usp_CheckDependencies', 'P') is not null
    drop procedure MS_PerfDashboard.usp_CheckDependencies
go

create procedure MS_PerfDashboard.usp_CheckDependencies
as
begin
    declare @Version nvarchar(100)
    declare @MajorVer tinyint, @MinorVer tinyint, @BuildNum smallint
    declare @dec1 int, @dec2 int, @dec3 int

    select @Version = convert(nvarchar(100), serverproperty('ProductVersion'))
    select @dec1 = charindex('.', @Version)
    select @dec2 = charindex('.', @Version, @dec1 + 1)
    select @dec3 = case when charindex('.', @Version, @dec2 + 1) = 0 then len(@Version) + 1 else charindex('.', @Version, @dec2 + 1) end

    select @MajorVer = convert(tinyint, substring(@Version, 1, @dec1 - 1)),
        @MinorVer = convert(tinyint, substring(@Version, @dec1 + 1, @dec2 - @dec1 - 1)),
        @BuildNum = convert(smallint, substring(@Version, @dec2 + 1, @dec3 - @dec2 - 1))
    
    select @MajorVer as major_version,
        @MinorVer as minor_version,
        @BuildNum as build_number,
        convert(nvarchar(128), SERVERPROPERTY('MachineName')) +
            CASE WHEN convert(nvarchar(128), SERVERPROPERTY('InstanceName')) IS NOT NULL THEN N'\' + convert(nvarchar(128), SERVERPROPERTY('InstanceName'))
            ELSE N''
            END as ServerInstance,
        @Version as ProductVersion,
        serverproperty('ProductLevel') as ProductLevel,
        serverproperty('Edition') as Edition

    if not (@MajorVer > 9 or (@MajorVer = 9 and @MinorVer > 0) or (@MajorVer = 9 and @MinorVer = 0 and @BuildNum >= 3026))
    begin
        RAISERROR('The target server being monitored via the Performance Dashboard must be running SQL Server 2005 Service Pack 2 (build 9.00.3026) or later.  This server is running version %s', 18, 1, @Version)
    end
end
go
grant execute on MS_PerfDashboard.usp_CheckDependencies to public
go


if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null
    drop procedure MS_PerfDashboard.usp_Main_GetCPUHistory
go

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory
as
begin
    declare @ts_now bigint
    select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
    
    select top 15 record_id,
        dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
        SQLProcessUtilization,
        SystemIdle,
        100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
    from (
        select
            record.value('(./Record/@id)[1]', 'int') as record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
            timestamp
        from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            and record like '%<SystemHealth>%') as x
        ) as y
    order by record_id desc
end
go
grant execute on MS_PerfDashboard.usp_Main_GetCPUHistory to public
go


if object_id('MS_PerfDashboard.usp_Main_GetMiscInfo', 'P') is not null
    drop procedure MS_PerfDashboard.usp_Main_GetMiscInfo
go

create procedure MS_PerfDashboard.usp_Main_GetMiscInfo
as
begin
    select
        (select count(*) from sys.traces) as running_traces,
        (select count(*) from sys.databases) as number_of_databases,
        (select count(*) from sys.dm_db_missing_index_group_stats) as missing_index_count,
        (select waiting_tasks_count from sys.dm_os_wait_stats where wait_type = N'SQLCLR_QUANTUM_PUNISHMENT') as clr_quantum_waits,
        (select count(*) from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like N'%<NonYieldSchedBegin>%') as non_yield_count,
        (select cpu_count from sys.dm_os_sys_info) as number_of_cpus,
        (select scheduler_count from sys.dm_os_sys_info) as number_of_schedulers
    end
go
grant execute on MS_PerfDashboard.usp_Main_GetMiscInfo to public
go


if object_id('MS_PerfDashboard.usp_Main_GetSessionInfo', 'P') is not null
    drop procedure MS_PerfDashboard.usp_Main_GetSessionInfo
go

create procedure MS_PerfDashboard.usp_Main_GetSessionInfo
as
begin
    select count(*) as num_sessions,
        sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,
        sum(convert(bigint, s.cpu_time)) as cpu_time,
        sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,
        sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
        case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))
            else NULL
            end as cache_hit_ratio
    from sys.dm_exec_sessions s
    where s.is_user_process = 0x1
end
go
grant execute on MS_PerfDashboard.usp_Main_GetSessionInfo to public
go



if object_id('MS_PerfDashboard.usp_Main_GetRequestInfo', 'P') is not null
    drop procedure MS_PerfDashboard.usp_Main_GetRequestInfo
go

create procedure MS_PerfDashboard.usp_Main_GetRequestInfo
as
begin
    select count(r.request_id) as num_requests,
        sum(convert(bigint, r.total_elapsed_time)) as total_elapsed_time,
        sum(convert(bigint, r.cpu_time)) as cpu_time,
        sum(convert(bigint, r.total_elapsed_time)) - sum(convert(bigint, r.cpu_time)) as wait_time,
        case when sum(r.logical_reads) > 0 then (sum(r.logical_reads) - isnull(sum(r.reads), 0)) / convert(float, sum(r.logical_reads))
            else NULL
            end as cache_hit_ratio
    from sys.dm_exec_requests r
        join sys.dm_exec_sessions s on r.session_id = s.session_id
    where s.is_user_process = 0x1
end
go
grant execute on MS_PerfDashboard.usp_Main_GetRequestInfo to public
go


if object_id('MS_PerfDashboard.usp_Main_GetRequestWaits', 'P') is not null
    drop procedure MS_PerfDashboard.usp_Main_GetRequestWaits
go

create procedure MS_PerfDashboard.usp_Main_GetRequestWaits
as
begin
    SELECT
        r.session_id,
        MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
        r.wait_type,
        r.wait_time
    FROM sys.dm_exec_requests AS r
        INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
    WHERE r.wait_type IS NOT NULL  
        AND s.is_user_process = 0x1
end
go
GRANT EXECUTE ON MS_PerfDashboard.usp_Main_GetRequestWaits TO public
go



if object_id('MS_PerfDashboard.usp_GetPageDetails', 'P') is not null
    drop procedure MS_PerfDashboard.usp_GetPageDetails
go

create procedure MS_PerfDashboard.usp_GetPageDetails @wait_resource varchar(100)
as
begin
    declare @database_id smallint, @file_id smallint, @page_no int
    declare @t TABLE (ParentObject varchar(256), Object varchar(256), Field varchar(256), VALUE sql_variant)

    declare @colon1 int, @colon2 int
    select @colon1 = charindex(':', @wait_resource)
    select @colon2 = charindex(':', @wait_resource, @colon1 + 1)
    select @database_id = substring(@wait_resource, 1, @colon1 - 1)
    select @file_id = substring(@wait_resource, @colon1 + 1, @colon2 - @colon1 - 1)
    select @page_no = substring(@wait_resource, @colon2 + 1, 100)
    
    BEGIN TRY
        insert into @t exec sp_executesql N'dbcc page(@database_id, @file_id, @page_no) with tableresults', N'@database_id smallint, @file_id smallint, @page_no int', @database_id, @file_id, @page_no
    END TRY
    BEGIN CATCH
        --do nothing
    END CATCH
    
    select @database_id as database_id,
        quotename(db_name(@database_id)) as database_name,
        @file_id as file_id,
        @page_no as page_no,
        convert(int, [Metadata: ObjectId]) as [object_id],
        quotename(object_schema_name(convert(int, [Metadata: ObjectId]), @database_id)) + N'.' + quotename(object_name(convert(int, [Metadata: ObjectId]), @database_id)) as [object_name],
        convert(smallint, [Metadata: IndexId]) as [index_id],
        convert(int, [m_level]) as page_level,
        case convert(int, [m_type])
            when 1 then N'Data Page'
            when 2 then N'Index Page'
            when 3 then N'Text Mix Page'
            when 4 then N'Text Tree Page'
            when 8 then N'GAM Page'
            when 9 then N'SGAM Page'
            when 10 then N'IAM Page'
            when 11 then N'PFS Page'
            else convert(nvarchar(10), [m_type])    -- other types intentionally omitted
        end as page_type
    from (select * from @t where ParentObject = 'PAGE HEADER:' and
            Field IN ('Metadata: ObjectId', 'Metadata: IndexId', 'm_objId (AllocUnitId.idObj)', 'm_level', 'm_type')) as x
        pivot (min([VALUE]) for Field in ([Metadata: ObjectId], [Metadata: IndexId], [m_level], [m_type])) as z
end
go
GRANT EXECUTE ON MS_PerfDashboard.usp_GetPageDetails TO public
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.usp_GetPlanGuideDetails'), 'IsProcedure') = 1
    drop procedure MS_PerfDashboard.usp_GetPlanGuideDetails
go

create procedure MS_PerfDashboard.usp_GetPlanGuideDetails @database_name nvarchar(128), @plan_guide_name nvarchar(128)
as
begin
    if (LEFT(@database_name, 1) = N'[' and RIGHT(@database_name, 1) = N']')
    begin
        select @database_name = substring(@database_name, 2, len(@database_name) - 2)
    end

    if (LEFT(@plan_guide_name, 1) = N'[' and RIGHT(@plan_guide_name, 1) = N']')
    begin
        select @plan_guide_name = substring(@plan_guide_name, 2, len(@plan_guide_name) - 2)
    end

    if db_id(@database_name) is not null
    begin
        declare @cmd nvarchar(4000)
        select @cmd = N'select * from [' + @database_name + N'].[sys].[plan_guides] where name = @P1'

        exec sp_executesql @cmd, N'@P1 nvarchar(128)', @plan_guide_name
    end
    else
    begin
        -- return empty result set
        select * from [sys].[plan_guides] where 0 = 1
    end
end
go

grant execute on MS_PerfDashboard.usp_GetPlanGuideDetails to public
go




if OBJECTPROPERTY(object_id('MS_PerfDashboard.usp_TransformShowplanXMLToTable'), 'IsProcedure') = 1
    drop procedure MS_PerfDashboard.usp_TransformShowplanXMLToTable
go

CREATE PROCEDURE MS_PerfDashboard.usp_TransformShowplanXMLToTable @plan_handle nvarchar(256), @stmt_start_offset int, @stmt_end_offset int, @fDebug bit = 0x0
AS
BEGIN
    SET NOCOUNT ON

    declare @plan nvarchar(max)
    declare @dbid int, @objid int
    declare @xml_plan xml
    declare @error int

    declare @output TABLE (
        node_id int,
        parent_node_id int,
        relevant_xml_text nvarchar(max),
        stmt_text nvarchar(max),
        logical_op nvarchar(128),
        physical_op nvarchar(128),
        output_list nvarchar(max),
        avg_row_size float,
        est_cpu float,
        est_io float,
        est_rows float,
        est_rewinds float,
        est_rebinds float,
        est_subtree_cost float,
        warnings nvarchar(max))

    BEGIN TRY
        -- handle may be invalid now, or XML may be too deep to convert
        select @dbid = p.dbid, @objid = p.objectid, @plan = p.query_plan from sys.dm_exec_text_query_plan(msdb.MS_PerfDashboard.fn_hexstrtovarbin(@plan_handle), @stmt_start_offset, @stmt_end_offset) as p
        select @xml_plan = convert(xml, @plan)

        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        insert into @output
        select nd.node_id,
            x.parent_node_id,
            case when @fDebug = 0x1 then
                            case
                                when x.parent_node_id is null then @plan
                                else convert(nvarchar(max), x.plan_node)
                            end
                    else NULL
                    end as relevant_xml_text,
            nd.stmt_text,
            nd.logical_op,
            nd.physical_op,
            nd.output_list,
            nd.avg_row_size,
            nd.est_cpu,
            nd.est_io,
            nd.est_rows,
            nd.est_rewinds,
            nd.est_rebinds,
            nd.est_subtree_cost,
            nd.warnings
        from (select
                splan.row.query('.') as plan_node,
                splan.row.value('../../@NodeId', 'int') as parent_node_id
            from (select @xml_plan as query_plan) as p
                cross apply p.query_plan.nodes('//sp:RelOp') as splan (row)) as x
                cross apply MS_PerfDashboard.fn_ShowplanRowDetails(plan_node) as nd
        order by isnull(parent_node_id, -1) asc

        -- Statements such as WAITFOR, etc may not have a RelOp so just show the statement type if available
        if @@rowcount = 0
        begin
            ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
            insert into @output (stmt_text) select isnull(@xml_plan.value('(//@StatementType)[1]', 'nvarchar(max)'), N'Unknown Statement')
        end
    END TRY
    BEGIN CATCH
        select @error = ERROR_NUMBER()
--         select
--             cast(NULL as int) as node_id,
--             cast(NULL as int) as parent_node_id,
--             cast(NULL as nvarchar(max)) as relevant_xml_text,
--             cast(NULL as nvarchar(max)) as stmt_text,
--             cast(NULL as nvarchar(128)) as logical_op,
--             cast(NULL as nvarchar(128)) as physical_op,
--             cast(NULL as nvarchar(max)) as output_list,
--             cast(NULL as float) as avg_row_size,
--             cast(NULL as float) as est_cpu,
--             cast(NULL as float) as est_io,
--             cast(NULL as float) as est_rows,
--             cast(NULL as float) as est_rewinds,
--             cast(NULL as float) as est_rebinds,
--             cast(NULL as float) as est_subtree_cost,
--             cast(NULL as nvarchar(max)) as warnings
--         where 0 = 1
    END CATCH

    -- This may be an empty set if there was an exception caught above
    SELECT
        node_id,
        parent_node_id,
        relevant_xml_text,
        stmt_text,
        logical_op,
        physical_op,
        output_list,
        avg_row_size,
        est_cpu,
        est_io,
        est_rows,
        est_rewinds,
        est_rebinds,
        est_subtree_cost,
        warnings
    FROM @output
END
go

grant execute on MS_PerfDashboard.usp_TransformShowplanXMLToTable to public
go




/*
 *
 *    Helper procedures for building showplan output.  These are called, indirectly, by MS_PerfDashboard.usp_TransformShowplanXMLToTable and because
 *    they belong to the same schema we do not need to grant EXECUTE permissions to users.  They are not intended to be called directly as they require
 *    proper context within the showplan XML in order to return meaningful output.
 *
 *
 */
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildColumnReference'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildColumnReference
go

create function MS_PerfDashboard.fn_ShowplanBuildColumnReference(@node_data xml, @include_alias_or_table bit)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)
    declare @table nvarchar(256), @alias nvarchar(256), @column nvarchar(256)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @alias = @node_data.value('(./sp:ColumnReference/@Alias)[1]', 'nvarchar(256)'),
        @table = @node_data.value('(./sp:ColumnReference/@Table)[1]', 'nvarchar(256)'),
        @column = @node_data.value('(./sp:ColumnReference/@Column)[1]', 'nvarchar(256)')

    select @column = case when left(@column, 1) = N'[' and right(@column, 1) = N']' then @column else quotename(@column) end

    if @include_alias_or_table = 0x1 and coalesce(@alias, @table) is not null
    begin
        select @alias = case when left(@alias, 1) = N'[' and right(@alias, 1) = N']' then @alias else quotename(@alias) end
        select @table = case when left(@table, 1) = N'[' and right(@table, 1) = N']' then @table else quotename(@table) end

        select @output = case
                    when @alias is not null then @alias
                    else @table
                end + N'.' + @column
    end
    else
    begin
        select @output = @column
    end

    return @output
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList
go

create function MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList (@node_data xml, @include_alias_or_table bit)
returns nvarchar(max)

as
begin
    declare @output nvarchar(max)

    declare @count int, @ctr int

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'', @ctr = 1, @count = @node_data.value('count(./sp:ColumnReference)', 'int')

    -- iterate over each element in the list
    while @ctr <= @count
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + case when @ctr > 1 then N', ' else N'' end + MS_PerfDashboard.fn_ShowplanBuildColumnReference(@node_data.query('./sp:ColumnReference[position() = sql:variable("@ctr")]'), @include_alias_or_table)

        select @ctr = @ctr + 1
    end

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList
go

create function MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = convert(nvarchar(max), @node_data.query('for $val in /sp:DefinedValue
                return concat(($val/sp:ColumnReference/@Column)[1], "=", ($val/sp:ScalarOperator/@ScalarString)[1], ",")'))

    declare @len int
    select @len = len(@output)
    if (@len > 0)
    begin
        select @output = left(@output, @len - 1)
    end

    return @output
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildOrderBy'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildOrderBy
go

create function MS_PerfDashboard.fn_ShowplanBuildOrderBy (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = convert(nvarchar(max), @node_data.query('for $col in /sp:OrderByColumn
                    return concat(if (($col/sp:ColumnReference/@Alias)[1] > "") then concat(($col/sp:ColumnReference/@Alias)[1], ".") else if (($col/sp:ColumnReference/@Table)[1] > "") then concat(($col/sp:ColumnReference/@Table)[1], ".") else "", string(($col/sp:ColumnReference/@Column)[1]), if ($col/@Ascending = 1) then " ASC" else " DESC", ",")'))
    declare @len int
    select @len = len(@output)
    if (@len > 0)
    begin
        select @output = left(@output, @len - 1)
    end

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildRowset'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildRowset
go

create function MS_PerfDashboard.fn_ShowplanBuildRowset (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:Object'))

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScalarExpression'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildScalarExpression
go

create function MS_PerfDashboard.fn_ShowplanBuildScalarExpression (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    select @output = N''

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @node_data.value('(./sp:ScalarOperator/@ScalarString)[1]', 'nvarchar(max)')

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList
go

create function MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = convert(nvarchar(max), @node_data.query('for $op in ./sp:ScalarOperator
                    return concat(string($op/@ScalarString), ",")'))

    declare @len int
    select @len = len(@output)
    if (@len > 0)
    begin
        select @output = left(@output, @len - 1)
    end

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScanRange'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildScanRange
go

create function MS_PerfDashboard.fn_ShowplanBuildScanRange (@node_data xml, @scan_type nvarchar(30))
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)
    set @output = N''

    declare @count int, @ctr int

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @ctr = 1, @count = @node_data.value('count(./sp:RangeColumns/sp:ColumnReference)', 'int')

    while @ctr <= @count
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output +
                case when @ctr > 1 then N' AND ' else '' end + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:RangeColumns/sp:ColumnReference[position() = sql:variable("@ctr")]'), 0x1)
                + N' ' +
            case UPPER(@scan_type)
                when 'BINARY IS' then N'IS'
                when 'EQ' then N'='
                when 'GE' then N'>='
                when 'GT' then N'>'
                when 'IS' then N'IS'
                when 'IS NOT' then N'IS NOT'
                when 'IS NOT NULL' then N'IS NOT NULL'
                when 'IS NULL' then N'IS NULL'
                when 'LE' then N'<='
                when 'LT' then N'<'
                when 'NE' then N'<>'
            end
             + N' '
            + MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList(@node_data.query('./sp:RangeExpressions/sp:ScalarOperator[position() = sql:variable("@ctr")]'))

        select @ctr = @ctr + 1
    end

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildSeekPredicates'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildSeekPredicates
go

create function MS_PerfDashboard.fn_ShowplanBuildSeekPredicates (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)
    declare @count int, @ctr int

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'', @ctr = 1, @count = @node_data.value('count(./sp:SeekPredicates/sp:SeekPredicate)', 'int')

    -- iterate over each element in the list
    while @ctr <= @count
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + case when @ctr > 1 then N' AND ' else N'' end + MS_PerfDashboard.fn_ShowplanBuildSeekPredicate(@node_data.query('./sp:SeekPredicates/sp:SeekPredicate[position() = sql:variable("@ctr")]/*'))

        select @ctr = @ctr + 1
    end

    return @output
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildSeekPredicate'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildSeekPredicate
go

create function MS_PerfDashboard.fn_ShowplanBuildSeekPredicate (@node_data xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)
    set @output = N''

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Prefix') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + MS_PerfDashboard.fn_ShowplanBuildScanRange(@node_data.query('./sp:Prefix/*'), @node_data.value('(./sp:Prefix/@ScanType)[1]', 'nvarchar(100)'))
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:StartRange') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + case when datalength(@output) > 0 then N' AND ' else '' end + MS_PerfDashboard.fn_ShowplanBuildScanRange(@node_data.query('./sp:StartRange/*'), @node_data.value('(./sp:StartRange/@ScanType)[1]', 'nvarchar(100)'))
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:EndRange') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + case when datalength(@output) > 0 then N' AND ' else '' end + MS_PerfDashboard.fn_ShowplanBuildScanRange(@node_data.query('./sp:EndRange/*'), @node_data.value('(./sp:EndRange/@ScanType)[1]', 'nvarchar(100)'))
    end

    return @output
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildObject'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildObject
go

create function MS_PerfDashboard.fn_ShowplanBuildObject (@node_data xml)
returns nvarchar(max)
as
begin
    declare @object nvarchar(max)
    set @object = N''

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Server') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + @node_data.value('(./sp:Object/@Server)[1]', 'nvarchar(128)') + N'.'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Database') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + @node_data.value('(./sp:Object/@Database)[1]', 'nvarchar(128)') + N'.'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Schema') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + @node_data.value('(./sp:Object/@Schema)[1]', 'nvarchar(128)') + N'.'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Table') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + @node_data.value('(./sp:Object/@Table)[1]', 'nvarchar(128)')
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Index') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + N'.' + @node_data.value('(./sp:Object/@Index)[1]', 'nvarchar(128)')
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Object/@Alias') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @object = @object + N' AS ' + @node_data.value('(./sp:Object/@Alias)[1]', 'nvarchar(128)')
    end

    return @object
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildWarnings'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanBuildWarnings
go

create function MS_PerfDashboard.fn_ShowplanBuildWarnings(@relop_node xml)
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    if (@relop_node.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RelOp/sp:Warnings') = 1)
    begin
        if (@relop_node.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RelOp/sp:Warnings[@NoJoinPredicate = 1]') = 1)
        begin
            select @output = N'NO JOIN PREDICATE'
        end
        
        if (@relop_node.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RelOp/sp:Warnings/sp:ColumnsWithNoStatistics') = 1)
        begin
            ;with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sp)
            select @output = case when @output is null then N'' else @output + N', ' end + N'NO STATS: ' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@relop_node.query('./sp:RelOp/sp:Warnings/sp:ColumnsWithNoStatistics/*'), 0x1)
        end
    end

    return @output
end
go




if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatAssert'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatAssert
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatAssert(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'Assert(' + @node_data.value('(./sp:Assert/sp:Predicate/sp:ScalarOperator/@ScalarString)[1]', 'nvarchar(max)') + N'))'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatBitmap'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatBitmap
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatBitmap(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'Bitmap(Hash Keys:(' + MS_PerfDashboard.fn_BuildColumnReferenceList(@node_data.query('./sp:HashKeys/sp:ColumnReference'), 0x1) + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatCollapse'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatCollapse
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatCollapse(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'Bitmap(GROUP BY:(' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:GroupBy/sp:ColumnReference'), 0x1) + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatComputeScalar'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatComputeScalar
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatComputeScalar(@node_data xml, @physical_op nvarchar(128))
returns nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @physical_op + N'(DEFINE: (' + MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList(@node_data.query('./sp:DefinedValues/*')) + N'))';

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatConcat'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatConcat
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatConcat(@node_data xml)
RETURNS nvarchar(max)
as
begin
    return N'Concatenation'
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatIndexScan'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatIndexScan
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatIndexScan(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)


    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @physical_op + N'(OBJECT: (' + MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:IndexScan/sp:Object')) + N')'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:IndexScan/sp:SeekPredicates/sp:SeekPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', SEEK: (' + MS_PerfDashboard.fn_ShowplanBuildSeekPredicates(@node_data.query('./sp:IndexScan/sp:SeekPredicates')) + N')'
    end


    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:IndexScan/sp:Predicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', WHERE: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:IndexScan/sp:Predicate/*')) + N')'
    end

    select @output = @output + N')'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:IndexScan[@Lookup = 1]') = 1)
    begin
        select @output = @output + N' LOOKUP'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:IndexScan[@Ordered = 1]') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N' ORDERED ' + @node_data.value('(./sp:IndexScan/@ScanDirection)[1]', 'nvarchar(128)')
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:IndexScan[@ForcedIndex = 1]') = 1)
    begin
        select @output = @output + N' FORCEDINDEX'
    end

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatConstantScan'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatConstantScan
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatConstantScan(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Constant Scan'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:ConstantScan/sp:Values') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'(VALUES: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList(@node_data.query('./sp:ConstantScan/sp:Values/sp:Row/*')) + N'))'
    end

    return @output
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan
go

-- Passed the Rowset element of XML showplan and extracts the Object details
CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @physical_op + N'(' + MS_PerfDashboard.fn_ShowplanBuildRowset(@node_data) + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatFilter'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatFilter
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatFilter(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    declare @fStartup tinyint

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @fStartup = case when (@node_data.exist('./sp:Filter[@StartupExpression = 1]') = 1) then 1 else 0 end

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'Filter(WHERE: (' +
        case when @fStartup = 1 then N'STARTUP EXPRESSION(' else N'' end +
        MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Filter/sp:Predicate/*')) +
        case when @fStartup = 1 then N')' else N'' end +
        N'))'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatHashMatch'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatHashMatch
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatHashMatch(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Hash Match(' + @logical_op

    if (@logical_op = N'Aggregate')
    begin
        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:HashKeysBuild') = 1)
        begin
            ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
            select @output = @output + N', HASH:(' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Hash/sp:HashKeysBuild/sp:ColumnReference'), 0x1) + N')'
        end
    
        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:BuildResidual') = 1)
        begin
            ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
            select @output = @output + N', RESIDUAL:(' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Hash/sp:BuildResidual/*')) + N')'
        end

        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', DEFINE: (' + MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList(@node_data.query('./sp:Hash/sp:DefinedValues/*')) + N')';
    end
    else
    begin
        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:HashKeysBuild') = 1)
        begin
            ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
            select @output = @output + N', HASH:(' +
                MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Hash/sp:HashKeysBuild/sp:ColumnReference'), 0x1) +
                N')=(' +
                MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Hash/sp:HashKeysProbe/sp:ColumnReference'), 0x1) + N')'
        end
    
        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:BuildResidual') = 1) or
            (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:ProbeResidual') = 1)
        begin
            declare @build_residual bit
    
            select @build_residual = 0x0, @output = @output + N', RESIDUAL:('
    
            if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:BuildResidual') = 1)
            begin
                ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
                select @output = @output + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Hash/sp:BuildResidual/*'))
                select @build_residual = 0x1
            end
    
            if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Hash/sp:ProbeResidual') = 1)
            begin
                ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
                select @output = @output + case when @build_residual = 0x1 then N' AND ' else '' end + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Hash/sp:ProbeResidual/*'))
            end

            select @output = @output + N')'
        end
    end

    select @output = @output + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatMerge'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatMerge
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatMerge(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'Merge Join(' + @logical_op + case when @node_data.exist('./sp:Merge[@ManyToMany = 1]') = 1 then N', MANY-TO-MANY'
            else N'' end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Merge/sp:InnerSideJoinColumns') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', MERGE: (' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Merge/sp:InnerSideJoinColumns/sp:ColumnReference'), 0x1) + N')=(' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Merge/sp:OuterSideJoinColumns/sp:ColumnReference'), 0x1) + N'))'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Merge/sp:Residual') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', RESIDUAL: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Merge/sp:Residual/*')) + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Merge/sp:PassThru') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', PASSTHRU: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Merge/sp:PassThru/*')) + N')'
    end

    return @output;
end
go




if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatNestedLoops'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatNestedLoops
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatNestedLoops(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Nested Loops(' + @logical_op

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops/sp:OuterReferences') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', OUTER REFERENCES:' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:NestedLoops/sp:OuterReferences/sp:ColumnReference'), 0x1)
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops/sp:Predicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', WHERE: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:NestedLoops/sp:Predicate/*')) + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops/sp:PassThru') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', PASSTHRU:(' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:NestedLoops/sp:PassThru/*')) + N')'
    end

    select @output = @output + N')'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops[@Optimized = 1]') = 1)
    begin
        select @output = @output + N' OPTIMIZED'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops[@WithOrderedPrefetch = 1]') = 1)
    begin
        select @output = @output + N' WITH ORDERED PREFETCH'
    end
    else if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:NestedLoops[@WithUnorderedPrefetch = 1]') = 1)
    begin
        select @output = @output + N' WITH UNORDERED PREFETCH'
    end

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatParallelism'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatParallelism
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatParallelism(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    select @output = N'Parallelism(' + @logical_op + N')'
    --TODO: Extend to show partitioning information, order by information    

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSimpleUpdate'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatSimpleUpdate
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatSimpleUpdate(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @physical_op + N'(' + MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:Object'))

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:SetPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', SET: ' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:SetPredicate/*'))
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:SeekPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', WHERE: (' + MS_PerfDashboard.fn_ShowplanBuildSeekPredicate(@node_data.query('./sp:SeekPredicate/*')) + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteQuery'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatRemoteQuery
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatRemoteQuery(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Remote Scan('

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteQuery/@RemoteSource') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'SOURCE: (' + @node_data.value('(./sp:RemoteQuery/@RemoteSource)[1]', 'nvarchar(256)') + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteQuery/@RemoteObject') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'OBJECT: (' + @node_data.value('(./sp:RemoteQuery/@RemoteObject)[1]', 'nvarchar(256)') + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteQuery/@RemoteQuery') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', QUERY: (' + @node_data.value('(./sp:RemoteQuery/@RemoteQuery)[1]', 'nvarchar(max)') + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteScan'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatRemoteScan
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatRemoteScan(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Remote Scan('

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteScan/@RemoteSource') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'SOURCE: (' + @node_data.value('(./sp:RemoteScan/@RemoteSource)[1]', 'nvarchar(256)') + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteScan/@RemoteObject') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'OBJECT: (' + @node_data.value('(./sp:RemoteScan/@RemoteObject)[1]', 'nvarchar(256)') + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteModify'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatRemoteModify
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatRemoteModify(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = @logical_op + N'('

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteModify/@RemoteSource') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'SOURCE: (' + @node_data.value('(./sp:RemoteModify/@RemoteSource)[1]', 'nvarchar(256)') + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteModify/@RemoteObject') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'OBJECT: (' + @node_data.value('(./sp:RemoteModify/@RemoteObject)[1]', 'nvarchar(256)') + N')'
    end


    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:RemoteModify/sp:SetPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'WHERE: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:RemoteModify/sp:SetPredicate/*')) + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSort'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatSort
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatSort(@node_data xml, @logical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Sort('

    if @logical_op = N'Sort'
    begin
        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Sort[@Distinct = 1]') = 1)
        begin
            select @output = @output + N'DISTINCT '
        end

        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'ORDER BY: (' + MS_PerfDashboard.fn_ShowplanBuildOrderBy(@node_data.query('./sp:Sort/sp:OrderBy/sp:OrderByColumn')) + N')'
    end
    else if @logical_op = N'TopN Sort'
    begin
        select @output = @output + N'TOP ' + @node_data.value('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (./sp:TopSort/@Rows)[1]', 'nvarchar(50)') + N', '

        if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TopSort[@Distinct = 1]') = 1)
        begin
            select @output = @output + N'DISTINCT '
        end

        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'ORDER BY: (' + MS_PerfDashboard.fn_ShowplanBuildOrderBy(@node_data.query('./sp:TopSort/sp:OrderBy/sp:OrderByColumn')) + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSplit'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatSplit
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatSplit(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Split'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Split/sp:ActionColumn') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'(' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Split/sp:ActionColumn/sp:ColumnReference'), 0x1) + N')'
    end

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatStreamAggregate'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatStreamAggregate
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatStreamAggregate(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    declare @need_comma bit

    select @output = N'Stream Aggregate('

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:StreamAggregate/sp:GroupBy') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'GROUP BY: (' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:StreamAggregate/sp:GroupBy/sp:ColumnReference'), 0x1) + N')'
        select @need_comma = 0x1
    end

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @output +
            case when @need_comma = 0x1 then N', ' else N'' end
        + N'DEFINE: (' + MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList(@node_data.query('./sp:StreamAggregate/sp:DefinedValues/sp:DefinedValue')) + N')'

    select @output = @output + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSegment'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatSegment
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatSegment(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Segment'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Segment/sp:GroupBy/sp:ColumnReference') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'(GROUP BY: ' + MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@node_data.query('./sp:Segment/sp:GroupBy/sp:ColumnReference'), 0x1) + N')'
    end

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSpool'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatSpool
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatSpool(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = @physical_op

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Spool/sp:SeekPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'(' + MS_PerfDashboard.fn_ShowplanBuildSeekPredicate(@node_data.query('./sp:Spool/sp:SeekPredicate/*')) + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Spool[@Stack = 1]') = 1)
    begin
        select @output = @output + N' WITH STACK'
    end

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTableScan'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatTableScan
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatTableScan(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Table Scan('

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @output + MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:TableScan/sp:Object'))

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TableScan/sp:Predicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', WHERE: (' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:TableScan/sp:Predicate/*')) + N')'
    end
    
    select @output = @output + N')'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TableScan[@Ordered = 1]') = 1)
    begin
        select @output = @output + N' ORDERED'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TableScan[@ForcedIndex = 1]') = 1)
    begin
        select @output = @output + N' FORCEDINDEX'
    end


    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTop'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatTop
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatTop(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Top'

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Top/sp:TopExpression') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'(TOP EXPRESSION: ' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:Top/sp:TopExpression/*')) + N')'
    end

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTVF'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatTVF
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatTVF(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)
    select @output = N'Table-valued Function('

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TableValuedFunction/sp:Object') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'OBJECT: (' + MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:TableValuedFunction/sp:Object')) + N')'
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:TableValuedFunction/sp:Predicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N', WHERE: ( ' + MS_PerfDashboard.fn_ShowplanBuildPredicate(@node_data.query('./sp:TableValuedFunction/sp:Predicate')) + N')'
    end

    select @output = @output + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatUDX'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatUDX
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatUDX(@node_data xml)
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = N'UDX(' + @node_data.value('(./sp:Extension/@UDXName)[1]', 'nvarchar(128)') + N')'

    return @output;
end
go


if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatUpdate'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatUpdate
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatUpdate(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @output = @physical_op + N'(' + MS_PerfDashboard.fn_ShowplanBuildObject(@node_data.query('./sp:Object/*'))

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:SetPredicate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = @output + N'SET: ' + MS_PerfDashboard.fn_ShowplanBuildScalarExpression(@node_data.query('./sp:SetPredicate/*'))
    end

    select @output = @output + N')'

    return @output;
end
go



if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatGenericUpdate'), 'IsScalarFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanFormatGenericUpdate
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanFormatGenericUpdate(@node_data xml, @physical_op nvarchar(128))
RETURNS nvarchar(max)
as
begin
    declare @output nvarchar(max)

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:SimpleUpdate') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = MS_PerfDashboard.fn_ShowplanFormatSimpleUpdate(@node_data.query('./sp:SimpleUpdate/*'), @physical_op)
    end

    if (@node_data.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; ./sp:Update') = 1)
    begin
        ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
        select @output = MS_PerfDashboard.fn_ShowplanFormatUpdate(@node_data.query('./sp:Update/*'), @physical_op)
    end

    return @output;
end
go


--
-- Created last since it depends on all the above functions for building/formatting the showplan
--
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanRowDetails'), 'IsTableFunction') = 1
    drop function MS_PerfDashboard.fn_ShowplanRowDetails
go

CREATE FUNCTION MS_PerfDashboard.fn_ShowplanRowDetails(@relop_node xml)
returns @node TABLE (node_id int, stmt_text nvarchar(max), logical_op nvarchar(128), physical_op nvarchar(128), output_list nvarchar(max), avg_row_size float, est_cpu float, est_io float, est_rows float, est_rewinds float, est_rebinds float, est_subtree_cost float, warnings nvarchar(max))
AS
begin
    declare @node_id int
    declare @output_list nvarchar(max)
    declare @stmt_text nvarchar(max)
    declare @logical_op nvarchar(128), @physical_op nvarchar(128)
    declare @avg_row_size float, @est_cpu float, @est_io float, @est_rows float, @est_rewinds float, @est_rebinds float, @est_subtree_cost float
    declare @relop_children xml

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @logical_op = @relop_node.value('(./sp:RelOp/@LogicalOp)[1]', 'nvarchar(128)'),
        @physical_op = @relop_node.value('(./sp:RelOp/@PhysicalOp)[1]', 'nvarchar(128)'),
        @relop_children = @relop_node.query('./sp:RelOp/*')

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    select @stmt_text =
        case
            when @physical_op = N'Assert' then MS_PerfDashboard.fn_ShowplanFormatAssert(@relop_children)
            when @physical_op = N'Bitmap' then MS_PerfDashboard.fn_ShowplanFormatBitmap(@relop_children)
            when @physical_op in (N'Clustered Index Delete', N'Clustered Index Insert', N'Clustered Index Update',
                        N'Index Delete', N'Index Insert', N'Index Update',
                        N'Table Delete', N'Table Insert', N'Table Update') then MS_PerfDashboard.fn_ShowplanFormatGenericUpdate(@relop_children, @physical_op)
            when @physical_op in (N'Clustered Index Scan', N'Clustered Index Seek',
                        N'Index Scan', N'Index Seek') then MS_PerfDashboard.fn_ShowplanFormatIndexScan(@relop_children, @physical_op)
--            when @physical_op = N'Clustered Update' then
            when @physical_op = N'Compute Scalar' then MS_PerfDashboard.fn_ShowplanFormatComputeScalar(@relop_children.query('./sp:ComputeScalar/*'), @physical_op)
            when @physical_op = N'Concatenation' then MS_PerfDashboard.fn_ShowplanFormatConcat(@relop_children)
            when @physical_op = N'Constant Scan' then MS_PerfDashboard.fn_ShowplanFormatConstantScan(@relop_children)
            when @physical_op = N'Deleted Scan' then MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan(@relop_children.query('./sp:DeletedScan/*'), @physical_op)
            when @physical_op = N'Filter' then MS_PerfDashboard.fn_ShowplanFormatFilter(@relop_children)
--            when @physical_op = N'Generic' then
            when @physical_op = N'Hash Match' then MS_PerfDashboard.fn_ShowplanFormatHashMatch(@relop_children, @logical_op)
            when @physical_op = N'Index Spool' then MS_PerfDashboard.fn_ShowplanFormatSpool(@relop_children, @physical_op)
            when @physical_op = N'Inserted Scan' then MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan(@relop_children.query('./sp:InsertedScan/*'), @physical_op)
            when @physical_op = N'Log Row Scan' then N'Log Row Scan'
            when @physical_op = N'Merge Interval' then N'Merge Interval'
            when @physical_op = N'Merge Join' then MS_PerfDashboard.fn_ShowplanFormatMerge(@relop_children, @logical_op)
            when @physical_op = N'Nested Loops' then MS_PerfDashboard.fn_ShowplanFormatNestedLoops(@relop_children, @logical_op)
            when @physical_op = N'Online Index Insert' then N'Online Index Insert'
            when @physical_op = N'Parallelism' then MS_PerfDashboard.fn_ShowplanFormatParallelism(@relop_children, @logical_op)
            when @physical_op = N'Parameter Table Scan' then N'Parameter Table Scan'
            when @physical_op = N'Print' then N'Print'
            when @physical_op in (N'Remote Delete', N'Remote Insert', N'Remote Update') then MS_PerfDashboard.fn_ShowplanFormatRemoteModify(@relop_children, @logical_op)
            when @physical_op = N'Remote Scan' then MS_PerfDashboard.fn_ShowplanFormatRemoteScan(@relop_children)
            when @physical_op = N'Remote Query' then MS_PerfDashboard.fn_ShowplanFormatRemoteQuery(@relop_children)
            when @physical_op = N'RID Lookup' then N'RID Lookup'
            when @physical_op = N'Row Count Spool' then MS_PerfDashboard.fn_ShowplanFormatSpool(@relop_children, @physical_op)
            when @physical_op = N'Segment' then MS_PerfDashboard.fn_ShowplanFormatSegment(@relop_children)
            when @physical_op = N'Sequence' then N'Sequence'
            when @physical_op = N'Sequence Project' then MS_PerfDashboard.fn_ShowplanFormatComputeScalar(@relop_children.query('./sp:SequenceProject/*'), @physical_op)
            when @physical_op = N'Sort' then MS_PerfDashboard.fn_ShowplanFormatSort(@relop_children, @logical_op)
            when @physical_op = N'Split' then MS_PerfDashboard.fn_ShowplanFormatSplit(@relop_children)
            when @physical_op = N'Stream Aggregate' then MS_PerfDashboard.fn_ShowplanFormatStreamAggregate(@relop_children)
            when @physical_op = N'Switch' then N'Switch'
            when @physical_op = N'Table-valued function' then MS_PerfDashboard.fn_ShowplanFormatTVF(@relop_children)
            when @physical_op = N'Table Scan' then MS_PerfDashboard.fn_ShowplanFormatTableScan(@relop_children)
            when @physical_op = N'Table Spool' then MS_PerfDashboard.fn_ShowplanFormatSpool(@relop_children, @physical_op)
            when @physical_op = N'Top' then MS_PerfDashboard.fn_ShowplanFormatTop(@relop_children)
            when @physical_op = N'UDX' then MS_PerfDashboard.fn_ShowplanFormatUDX(@relop_children)
            else @physical_op + N'(' + @logical_op + N')'
        end    

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
    insert @node (
        node_id,
        stmt_text,
        logical_op,
        physical_op,
        output_list,
        avg_row_size,
        est_cpu,
        est_io,
        est_rows,
        est_rewinds,
        est_rebinds,
        est_subtree_cost,
        warnings)
    values (
        @relop_node.value('(./sp:RelOp/@NodeId)[1]', 'int'),
        @stmt_text,
        @logical_op,
        @physical_op,
        MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList(@relop_node.query('./sp:RelOp/sp:OutputList/sp:ColumnReference'), 0x1),
        @relop_node.value('(./sp:RelOp/@AvgRowSize)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimateCPU)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimateIO)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimateRows)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimateRewinds)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimateRebinds)[1]', 'float'),
        @relop_node.value('(./sp:RelOp/@EstimatedTotalSubtreeCost)[1]', 'float'),
        MS_PerfDashboard.fn_ShowplanBuildWarnings(@relop_node)
        );

    return;
end

go