Possível otimizar a consulta SELECT envolvendo funções XQuery selecionando elementos com número arbitrário de elementos filhos

Aqui está uma reescrita do que você já tem.

1. Nenhuma fragmentação necessária no nó raiz.
2. Especifique o text()nó para o nome do parâmetro e o valor do parâmetro.

Em meus testes limitados, isso o tornou significativamente mais rápido. Estou ansioso para ver se algum ganho de desempenho você vê do seu lado.

select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       null as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'
union all
select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       GGC.X.value('@name', 'varchar(max)') as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('greatgrandchild') as GGC(X)
  cross apply GGC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'

Atualizar:

Tomei a liberdade de executar o test rig fornecido pelo wBob no SQL Server 2014 com nível de compatibilidade 110 (SQL Server 2012) e 120 (SQL Server 2014)

Resultado:

Compatibility level  OP's query  My query  wBob query using XML indexes
-------------------  ----------  --------  ----------------------------
110                  64 sec      37 sec    1 sec
120                  8 sec       4 sec     5 sec

A diferença que você vê no tempo de execução existe porque o SQL Server 2014 usa um novo estimador de cardinalidade. Quando o nível de compatibilidade for 110, o SQL Server usará o estimador antigo. Ativar ou desativar o novo estimador de cardinalidade usando sinalizadores de rastreamento produz exatamente o mesmo resultado.

A diferença de tempos para as consultas que não utilizam os índices XML é porque no nível de Compatibilidade 120 os planos são paralelos com DOP 16 no meu caso.

Também é muito interessante notar que o tempo de execução para usar índices XML é cinco vezes mais lento quando se usa o novo estimador de cardinalidade. A razão para isso é a mesma acima, apenas o contrário. Há um plano paralelo apenas ao usar o antigo estimador de cardinalidade.

Obtenho um bom resultado com uma coleção de esquema XML e um índice XML primário. Não acho que o XSC esteja aumentando o desempenho em particular, mas é uma boa restrição de se ter.

Meu equipamento de teste completo:

USE tempdb
GO

SET NOCOUNT ON

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.testTable') IS NOT NULL DROP TABLE dbo.testTable
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
IF OBJECT_ID('tempdb..#tmp3') IS NOT NULL DROP TABLE #tmp3
GO

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = N'xsc_test' )
    DROP XML SCHEMA COLLECTION xsc_test
GO

CREATE XML SCHEMA COLLECTION xsc_test
AS N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="child" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="grandchild" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element maxOccurs="unbounded" name="parameter">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element name="name" type="xs:string" />
                                                    <xs:element name="value" type="xs:string" />
                                                </xs:sequence>
                                            </xs:complexType>
                                        </xs:element>
                                        <xs:element name="greatgrandchild" maxOccurs="unbounded">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element maxOccurs="unbounded" name="parameter">
                                                        <xs:complexType>
                                                            <xs:sequence>
                                                                <xs:element name="name" type="xs:string" />
                                                                <xs:element name="value" type="xs:string" />
                                                            </xs:sequence>
                                                        </xs:complexType>
                                                    </xs:element>
                                                </xs:sequence>
                                                <xs:attribute name="name" type="xs:string" use="required" />
                                            </xs:complexType>
                                        </xs:element>
                                    </xs:sequence>
                                    <xs:attribute name="name" type="xs:string" use="required" />
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute name="name" type="xs:string" use="required" />
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

CREATE TABLE dbo.testTable ( testId INT, testData XML,
    CONSTRAINT PK_testTable  PRIMARY KEY ( testId ) )
GO


INSERT INTO dbo.TestTable (TestId, TestData)
VALUES (1, '<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>');
GO

;WITH cte AS 
(
SELECT 2 x
UNION ALL
SELECT x + 1
FROM cte
WHERE x < 400
)
INSERT INTO dbo.testTable ( testId, testData )
SELECT x, 
    (
    SELECT 
        'root' + CAST( x AS VARCHAR(5) ) AS "@name",
        (
        SELECT x AS "@name",
            (
            SELECT x * 10 AS "@name",
                (
                SELECT
                    'param' + CAST( x AS VARCHAR(5) ) AS "name",
                    CHAR(x + 64) + 'BC' AS value
                FROM ( SELECT TOP 5 * FROM cte ) [parameters] -- grandchild parameters
                FOR XML PATH('parameter'), TYPE
                ),
                (
                SELECT x * 100 AS "@name",
                    (
                    SELECT
                        'param' + CAST( x AS VARCHAR(5) ) AS "name",
                        CHAR(x + 64) + 'BC' AS value
                    FROM ( SELECT TOP 3 * FROM cte ) [parameters]   -- greatgrandchild parameters
                    FOR XML PATH('parameter'), TYPE
                    )
                FROM ( SELECT TOP 2 * FROM cte ) greatgrandchildren -- make 2 greatgrandchildren
                FOR XML PATH('greatgrandchild'), TYPE
                )
                FROM ( SELECT TOP 3 * FROM cte ) grandchildren -- make 3 grandchildren
                FOR XML PATH('grandchild'), TYPE
            )
        FROM ( SELECT TOP 5 * FROM cte ) children -- make 5 children
        FOR XML PATH('child'), TYPE
        )
    FOR XML PATH('root'), TYPE
    ) yourXML
FROM cte
OPTION ( MAXRECURSION 400 )
GO

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Original Query START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
GO

DECLARE @startDate DATETIME = GETDATE()

-- Original query
SELECT
    TestId,
    [root],
    child,
    grandchild,
    NULL AS greatgrandchild,
    parameter
INTO #tmp1
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            parameter.d.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.d.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
                CROSS APPLY [root].a.nodes('child') AS child(b)
                    CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
                        CROSS APPLY grandchild.c.nodes('parameter') AS parameter(d)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'
UNION ALL
SELECT
    TestId,
    [root],
    child,
    grandchild,
    greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            greatgrandchild.d.value('@name', 'varchar(max)') AS greatgrandchild,
            parameter.e.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.e.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('greatgrandchild') AS greatgrandchild(d)
            CROSS APPLY greatgrandchild.d.nodes('parameter') AS parameter(e)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff1
GO

-- Original Query END
------------------------------------------------------------------------------------------------




------------------------------------------------------------------------------------------------
-- Rewrite 1 START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

DECLARE @startDate DATETIME = GETDATE()

select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       null as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
INTO #tmp2
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'
union all
select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       GGC.X.value('@name', 'varchar(max)') as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('greatgrandchild') as GGC(X)
  cross apply GGC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff2
GO

-- Rewrite 1 END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- XSC with XML Index START
------------------------------------------------------------------------------------------------

-- Size Before
EXEC sp_spaceused 'testTable', 'true'
GO
ALTER TABLE dbo.testTable ALTER COLUMN testData XML(xsc_test)
CREATE PRIMARY XML INDEX xidx_testTable ON dbo.testTable(testData)
GO

-- Size After
EXEC sp_spaceused 'testTable', 'true'
GO

DECLARE @startDate DATETIME = GETDATE()

SELECT
    t.testId,
    r.c.value('@name', 'VARCHAR(20)') [root],
    c.c.value('@name', 'VARCHAR(20)') child,
    gc.c.value('@name', 'VARCHAR(20)') grandchild,
    NULL AS greatgrandchild,
    p.c.value('(name)[1]', 'VARCHAR(20)') parameter,
    p.c.value('(value)[1]', 'VARCHAR(20)') value

INTO #tmp

FROM dbo.testTable t
    CROSS APPLY t.testData.nodes('root') AS r(c)
        CROSS APPLY r.c.nodes('child') AS c(c)
            CROSS APPLY c.c.nodes('grandchild') AS gc(c)
                CROSS APPLY gc.c.nodes('parameter') AS p(c)

UNION ALL

SELECT
    t.testId,
    r.c.value('@name', 'VARCHAR(20)') [root],
    c.c.value('@name', 'VARCHAR(20)') child,
    gc.c.value('@name', 'VARCHAR(20)') grandchild,
    ggc.c.value('@name', 'VARCHAR(20)') grandchild,
    p.c.value('(name)[1]', 'VARCHAR(20)') parameter,
    p.c.value('(value)[1]', 'VARCHAR(20)') value

FROM dbo.testTable t
    CROSS APPLY t.testData.nodes('root') AS r(c)
        CROSS APPLY r.c.nodes('child') AS c(c)
            CROSS APPLY c.c.nodes('grandchild') AS gc(c)
                CROSS APPLY gc.c.nodes('greatgrandchild') AS ggc(c)
                    CROSS APPLY ggc.c.nodes('parameter') AS p(c)

SELECT *
INTO #tmp3
FROM #tmp
WHERE value Like '[^A]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff3
GO

-- XSC with XML Index END
------------------------------------------------------------------------------------------------

Todas as consultas funcionam bem com o índice xml primário. Obviamente há uma penalidade de espaço em disco com o índice XML, no meu rig a tabela passou de ~3MB para ~24MB!

I can't see secondary xml indexes or selective xml indexes adding a lot to this (as you are basically traversing the whole document) but might give them a try if I get time.