ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск в иерархичном XML
9 сообщений из 9, страница 1 из 1
Поиск в иерархичном XML
    #39167995
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Существует абстрактный план запроса:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
DECLARE @xml XML = '
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.6020.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="&#xD;&#xA;SELECT DISTINCT d.SalesOrderID, d.UnitPrice, h.OrderDate&#xD;&#xA;FROM Sales.SalesOrderHeader h&#xD;&#xA;JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID&#xD;&#xA;,Sales.Currency c, Sales.CountryRegionCurrency r&#xD;&#xA;WHERE h.DueDate &gt; h.ShipDate" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="45.2634" StatementEstRows="3.14624e+006" StatementOptmLevel="FULL" QueryHash="0x3ECB68B1AA9F7DCD" QueryPlanHash="0x9E109FEC6A3EA1D7">
          <QueryPlan CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="952">
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="5760" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="38400" EstimatedPagesCached="9600" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="3.14624e+006" EstimateIO="0" EstimateCPU="3.54802" AvgRowSize="27" EstimatedTotalSubtreeCost="45.2634" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <StreamAggregate>
                <GroupBy>
                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[d]" Column="UnitPrice" />
                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                </GroupBy>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3.94981e+006" EstimateIO="0" EstimateCPU="16.5102" AvgRowSize="31" EstimatedTotalSubtreeCost="41.7154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <NestedLoops Optimized="0">
                    <RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                      <OutputList />
                      <Top RowCount="0" IsPercent="0" WithTies="0">
                        <TopExpression>
                          <ScalarOperator ScalarString="(1)">
                            <Const ConstValue="(1)" />
                          </ScalarOperator>
                        </TopExpression>
                        <RelOp NodeId="4" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0002769" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="109" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                          <OutputList />
                          <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                            <DefinedValues />
                            <Warnings NoJoinPredicate="1" />
                            <Object Database="[AdventureWorks2012]" Schema="[Sales]" Table="[CountryRegionCurrency]" Index="[IX_CountryRegionCurrency_CurrencyCode]" Alias="[r]" TableReferenceId="-1" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                      </Top>
                    </RelOp>
                    <RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3.94981e+006" EstimateIO="0" EstimateCPU="16.5102" AvgRowSize="31" EstimatedTotalSubtreeCost="25.2019" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                      <Warnings NoJoinPredicate="1" />
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </StreamAggregate>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'


На неизвестном уровне вложенности существует нод Warnings (возможно не один).

Нужно найти первый попавшийся. Пробовал вот таким запросом:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
;WITH cte AS 
(
	SELECT
          [type] = t.c.value('local-name(.)', 'VARCHAR(100)')
        , x1 = CAST(NULL AS XML)
        , x = t.c.query('./*')
    FROM @XML.nodes('*') t(c)

    UNION ALL

    SELECT
          t.c.value('local-name(.)', 'VARCHAR(100)')
        , t.c.query('.')
        , t.c.query('./*')
    FROM cte
    CROSS APPLY x.nodes('*') t(c)
)
SELECT TOP(1) [type], x1
FROM cte
WHERE [type] = 'Warnings'


Но что-то мне подсказывает интуиция, что можно сделать это явно проще и элегантнее (без CTE).

Кто может подсказать или направить в правильную сторону?
...
Рейтинг: 0 / 0
10.02.2016, 19:25
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39168016
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top (1)
 t.n.query('.')
from
 @xml.nodes('//Warnings') t(n);
...
Рейтинг: 0 / 0
10.02.2016, 20:11
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39168023
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Такой вариант подошел:

Код: sql
1.
2.
SELECT TOP(1) t.c.query('.')
FROM @xml.nodes('//*:Warnings') t(c)


То такие крякозябры на мутную голову начал писать :)

invm, большое спасибо за помощь!
...
Рейтинг: 0 / 0
10.02.2016, 20:17
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Поиск в иерархичном XML
    #39836495
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
В известных диагностических скриптах есть тяжёлый запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Find missing index warnings for cached plans in the current database  (Query 53) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

Like по полному тексту xml работает медленно и выдаёт в профилере много сообщений:
Код: plaintext
Экземпляр типа данных XML имеет слишком много уровней вложенных узлов. Максимально допустимая глубина составляет 128 уровней.

Код: sql
1.
query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0

Обсуждаемое условие на малом количестве записей быстрее Like, но на большом количестве записей работает медленно и с большим количеством таких сообщений в профилере.

Код: sql
1.
2.
(query_plan.value ( 'not(/*/*/*/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0
or query_plan.value ( 'not(/*/*/*/*/*/*/*[local-name()="MissingIndexes"])','bit' )=0)

Условие с ограничением области поиска начинает работать быстрее на большом количестве записей и выдаёт существенно меньше сообщений в профилере. Но приходится явно указывать варианты.
...
Рейтинг: 0 / 0
11.07.2019, 17:16
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39836528
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
tunknown,

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, default, default) AS qpt
WHERE qpt.query_plan LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

?
...
Рейтинг: 0 / 0
11.07.2019, 18:16
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39836543
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
tunknown,

фантазия у авторов скрипта разыгралась от недостатка образования. Информацию о недостающих индексах можно получить из динамических представлений sys.dm_db_missing_index...
...
Рейтинг: 0 / 0
11.07.2019, 18:33
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39836665
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
invm
Код: sql
1.
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, default, default) AS qpt

?Это за 56 секунд.

Как это ни странно, вариант с xml+OR быстрее- всего 36 секунд, хотя и с сообщениями в профилере. Могу предположить, что текстовый план всё равно внутри берётся из бинарного xml, преобразуемого в текст, но почему-то без сообщений в профилере.

Но вопрос не в этих скриптах, а в произвольном поиске в xml.

Владислав КолосовИнформацию о недостающих индексах можно получить из динамических представлений sys.dm_db_missing_index...Возможно, sqlserver не всё может сложить в единое место и они пытаются покрыть другой случай, т.к. у них упоминаются в соседних скриптах и sys.dm_db_missing_index_group_stats и sys.dm_db_missing_index_groups.
...
Рейтинг: 0 / 0
12.07.2019, 09:36
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39836690
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
tunknown,

Код: sql
1.
WHERE query_plan.exist(N'//*:MissingIndexes') = 1

?
...
Рейтинг: 0 / 0
12.07.2019, 10:17
| Ответить | Цитировать | Написать  
Поиск в иерархичном XML
    #39836786
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
invm
Код: sql
1.
WHERE query_plan.exist(N'//*:MissingIndexes') = 1

?То же, что и ранее проверенное
Код: sql
1.
query_plan.value ( 'not(//*:MissingIndexes)','bit' )=0

хотя могло бы и побыстрее быть, т.к. внутренняя функция не используется.

Вот, если бы материализовать часть бинарного xml в текст, например, зная, что всё, что нужно встретится в первых 2000 символов. Но в явном виде это не работает.
...
Рейтинг: 0 / 0
12.07.2019, 12:40
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск в иерархичном XML
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]