Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как по sys.dm_db_missing_index_details найти текст запроса?  [new]
AlexVin
Member

Откуда: E-burg
Сообщений: 1074
сервер 2008 r2
как по рекомендации в sys.dm_db_missing_index_details найти текст запроса, который просигнализировал о необходимости этого индекса?
14 июл 15, 11:54    [17889368]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
человек_ниоткуда
Guest
Ну раз все молчат...
Моё IMHO: рыться в планах запросов и искать где про этот индекс сказано. Я, так, собственно, и делаю обычно.
sys.dm_db_missing_index_details - это сводка, так сказать, она по серверу целиком, она не связана а с конкретными планами запросов.
14 июл 15, 13:09    [17889775]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
AlexVin
Member

Откуда: E-burg
Сообщений: 1074
а можно как-то отобрать запросы (и планы) где есть отсутствующие индексы?
не поможете написать select like из sys.dm_exec_query_plan(qs.plan_handle)?
14 июл 15, 13:24    [17889866]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
хе-хе )
Guest
Как вариант, для каждого столбца, правда отдельно и есть ньансы
 SELECT * FROM sys.sql_modules
 WHERE definition Like ('%where % equality_columns [=|in] %')
or definition Like ('%where % inequality_columns [<>|not in] %')
14 июл 15, 13:43    [17890007]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
человек_ниоткуда
Guest
Вот разбор плана на составляющие таблицы и индексы, вместо "0x000000000000000000000000000000000000000000000000" подставляй plan_handle.
WITH XMLNAMESPACES
	(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
	query_plan
,	[EstimatedTotalSubtree%] = i.value('(@EstimatedTotalSubtreeCost)[1]', 'FLOAT') / (SUM(i.value('(@EstimatedTotalSubtreeCost)[1]', 'FLOAT')) OVER (PARTITION BY 0)) * 100
,	PhysicalOp = i.value('(@PhysicalOp)[1]', 'VARCHAR(128)')
,	EstimateRows = i.value('(@EstimateRows)[1]', 'VARCHAR(128)')
,	DatabaseName = i.value('(./*/Object/@Database)[1]', 'VARCHAR(128)')
,	TableName = i.value('(./*/Object/@Schema)[1]', 'VARCHAR(128)') + '.' + i.value('(./*/Object/@Table)[1]', 'VARCHAR(128)')
,	IndexName = i.value('(./*/Object/@Index)[1]', 'VARCHAR(128)')
,	EstimatedTotalSubtreeCost = i.value('(@EstimatedTotalSubtreeCost)[1]', 'FLOAT')
,	EstimateRows = i.value('(@EstimateRows)[1]', 'FLOAT')
,	EstimateIO = i.value('(@EstimateIO)[1]', 'FLOAT')
,	EstimateCPU = i.value('(@EstimateCPU)[1]', 'FLOAT')
,	output_columns = STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./OutputList/ColumnReference') AS t(cg) FOR  XML PATH('')),1,2,'')
,	seek_columns = STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./*/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg) FOR  XML PATH('')),1,2,'')
,	Predicate = i.value('(./*/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') 
FROM   
		(      SELECT query_plan
			FROM   sys.dm_exec_query_plan(0x000000000000000000000000000000000000000000000000) _qp
		) r
CROSS APPLY
		r.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY 
		n.nodes('.//RelOp[*/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);

Можешь отсуда плясать.
14 июл 15, 13:46    [17890029]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Вот как-то так
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), p as
(
 select
  quotename(object_schema_name(t.objectid, t.dbid)) + N'.' + quotename(object_name(t.objectid, t.dbid)) as [object_name],
  p.query_plan,
  a.sql_text,
  n.value('@Impact', 'numeric(18,4)') as [query_impact, %],
  n.value('MissingIndex[1]/@Database', 'sysname') + N'.' +
   n.value('MissingIndex[1]/@Schema', 'sysname') + N'.' +
   n.value('MissingIndex[1]/@Table', 'sysname') as table_name,
  n.query('for $c in MissingIndex/ColumnGroup[@Usage="EQUALITY"]/Column order by xs:integer($c/@ColumnId) return concat($c/@Name, ",")').value('.', 'nvarchar(4000)') as equality_columns,
  n.query('for $c in MissingIndex/ColumnGroup[@Usage="INEQUALITY"]/Column order by xs:integer($c/@ColumnId) return concat($c/@Name, ",")').value('.', 'nvarchar(4000)') as inequality_columns,
  n.query('for $c in MissingIndex/ColumnGroup[@Usage="INCLUDE"]/Column order by xs:integer($c/@ColumnId) return concat($c/@Name, ",")').value('.', 'nvarchar(4000)') as included_columns
 from
  sys.dm_exec_query_stats qs cross apply
  sys.dm_exec_query_plan(qs.plan_handle) p cross apply
  p.query_plan.nodes('//MissingIndexes/MissingIndexGroup') px(n) cross apply
  sys.dm_exec_sql_text(qs.plan_handle) t cross apply
  (select substring(t.text, (qs.statement_start_offset / 2) + 1, (case qs.statement_end_offset when -1 then datalength(t.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2 + 1)) a(sql_text)
 where
  convert(binary(8), n.value('../../..[1]/@QueryHash', 'nvarchar(30)'), 1) = qs.query_hash
)
select
 mid.[statement], mid.equality_columns, mid.inequality_columns, mid.included_columns,
 p.[query_impact, %], p.object_name, p.sql_text, p.query_plan
from
 sys.dm_db_missing_index_details mid join
 p on p.table_name = mid.statement and
      p.equality_columns = isnull(mid.equality_columns + ',', '') and
      p.inequality_columns = isnull(mid.inequality_columns + ',', '') and
      p.included_columns = isnull(mid.included_columns + ',', '');

ЗЫ: Работать может очень долго...
15 июл 15, 00:10    [17893168]     Ответить | Цитировать Сообщить модератору
 Re: как по sys.dm_db_missing_index_details найти текст запроса?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
AlexVin,

Вариант без полного парсинга всего кэша планов. Работает очень быстро, при условия конечно что план еще в кэше. Есть правда ограничения. если есть несколько запросов, которым нужен абсолютно одинаковый индекс, то этот скрипт вернет только один из них. Также если искомый запрос выполняется очень часто, то иногда этот скрипт может ничего не вернуть.
Просто поменяйте значение @index_handle на ваш index_handle из sys.dm_db_missing_index_details.

+
DECLARE @index_handle INT
SET @index_handle = 12345

/* Generated in SQL Explorer v.1.6.2.25881 */

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

DECLARE @missing_index_search_string nvarchar(512)
DECLARE @search_start_date DATETIME
DECLARE @search_end_date DATETIME

SELECT 
  @missing_index_search_string = '%<MissingIndex ' + 
    + 'Database="\' + QUOTENAME(DB_NAME(database_id)) + '" ' +
    + 'Schema="\' + QUOTENAME(object_schema_name(object_id, database_id)) + '" ' +
    + 'Table="\' + QUOTENAME(object_name(object_id, database_id)) + '">'
    + ISNULL('%<ColumnGroup Usage=\"EQUALITY\">%' + REPLACE(REPLACE(equality_columns, ', ', '%'), '[', '<Column Name="\['), '')
    + ISNULL('%<ColumnGroup Usage=\"INEQUALITY\">%' + REPLACE(REPLACE(inequality_columns, ', ', '%'), '[', '<Column Name="\['), '')
    + ISNULL('%<ColumnGroup Usage=\"INCLUDE\">%' + REPLACE(REPLACE(included_columns, ', ', '%'), '[', '<Column Name="\['), '')
    + '%</MissingIndex>%'
FROM sys.dm_db_missing_index_details 
WHERE index_handle = @index_handle

SELECT TOP 1 
  @search_start_date = migs.last_user_seek,
  @search_end_date = DATEADD(SECOND, 1, migs.last_user_seek)
FROM sys.dm_db_missing_index_groups AS mig 
  INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
WHERE mig.index_handle = @index_handle

SELECT 
  DatabaseName = DB_NAME(epa.dbid),
  ObjectName = DB_NAME(qt.[dbid]) + '.' + OBJECT_SCHEMA_NAME(qt.objectid, qt.[dbid]) + '.' + OBJECT_NAME(qt.objectid, qt.[dbid]),
  sql_text = substring(qt.text, (statement_start_offset/2)+1 , case when statement_end_offset IN(0,-1) then 2147483647 else ((statement_end_offset - statement_start_offset)/2) + 1 end),
  qs.last_execution_time,
  exec_count = qs.execution_count,
  qs.plan_handle,
  qp.query_plan,
  CAST(ISNULL(1.0*execution_count/NULLIF(DATEDIFF(second, creation_time, GETDATE())/60., 0), 0) AS DECIMAL(20, 2)) AS [exec/min],
  last_time_ms = CAST(qs.last_elapsed_time/1000. AS DECIMAL(20,2)),
  last_cpu_ms = CAST(qs.last_worker_time/1000. AS DECIMAL(20,2)),
  qs.last_logical_reads,
  qs.last_physical_reads,
  start_offset = qs.statement_start_offset,
  end_offset = qs.statement_end_offset
FROM (SELECT 1 AS PH) PredicatePushDown
  INNER JOIN sys.dm_exec_query_stats qs 
    ON (qs.last_execution_time BETWEEN @search_start_date AND @search_end_date)
    OR (qs.last_execution_time >= DATEADD(SECOND, -3, GETDATE()))
  OUTER APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp
  OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
  OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  OUTER APPLY (SELECT TOP 1 CONVERT(smallint, value) AS dbid
               FROM sys.dm_exec_plan_attributes(qs.plan_handle)
               WHERE attribute IN ('dbid', 'dbid_execute') and value <> 32767
               ORDER BY attribute) AS epa
WHERE tqp.query_plan LIKE @missing_index_search_string ESCAPE '\'
15 июл 15, 03:21    [17893274]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить