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

Есть некоторый индекс. Хотелось бы из кэша при помощи SQL-запроса получить список запросов, в плане которых используется этого индекса.
В каком направлении копать?
26 янв 18, 17:50    [21142502]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Начинучка,

ну например

SELECT TOP 10
	databases.name,
	dm_exec_sql_text.text AS TSQL_Text,
	dm_exec_query_stats.creation_time, 
	dm_exec_query_stats.execution_count,
	dm_exec_query_stats.total_worker_time AS total_cpu_time,
	dm_exec_query_stats.total_elapsed_time, 
	dm_exec_query_stats.total_logical_reads, 
	dm_exec_query_stats.total_physical_reads, 
	dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE 
	dm_exec_sql_text.text LIKE '%TABLENAME%'
	AND CAST(dm_exec_query_plan.query_plan AS NVARCHAR(MAX))LIKE '%INDEXNAME%'

хотя знатоки xml могу предложить и более оптимальную штуку
26 янв 18, 18:01    [21142515]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
Начинучка
Guest
TaPaK,

как-то можно уточнить запрос, чтобы использование было для поиска данных (Seek, Scan)?
иначе выпадает очень много "мусора", где происходит вставка данных в таблицу или удаление из неё...
26 янв 18, 18:26    [21142552]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Начинучка
как-то можно уточнить запрос, чтобы использование было для поиска данных (Seek, Scan)?
declare @database sysname = N'MyDB', @schema sysname = N'MySchema', @table sysname = N'MyTable', @index sysname = N'MyIndex';

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
 qp.x, i.LogicalOp, object_schema_name(qt.objectid, qt.dbid), object_name(qt.objectid, qt.dbid), st.statement_text
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp cross apply
 sys.dm_exec_sql_text(qs.sql_handle) qt cross apply
 (select quotename(@database), quotename(@schema), quotename(@table), quotename(@index)) o([database], [schema], [table], [index]) cross apply
 (select substring(qt.text, (qs.statement_start_offset / 2) + 1, ((case qs.statement_end_offset when -1 then datalength(qt.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2) + 1)) st(statement_text) cross apply
 (select cast(case when st.statement_text is not null then tqp.query_plan end as xml) where charindex(o.[index], tqp.query_plan) > 0) qp(x) cross apply
 (
  select
   t.n.value('../../@LogicalOp', 'nvarchar(100)')
  from
   qp.x.nodes('//RelOp/IndexScan/Object[@Database = sql:column("o.database") and @Schema = sql:column("o.schema") and @Table = sql:column("o.table") and @Index = sql:column("o.index")]') t(n)
  where
   qp.x is not null
 ) i(LogicalOp);
27 янв 18, 00:12    [21143153]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
У меня вопрос похожий, поэтому задам его здесь, чтобы не создавать новый топик...

Используется MS SQL Server 2012 SE + SP1

Есть задача - пересмотреть схему индексирования базы, определить и (возможно) удалить неиспользуемые индексы, достроить недостающие.

На просторах интернета был найден запрос, показывающий характер использования существующих индексов
(в моём случае - по отдельно взятой таблице 'MyTableName'):

+ Статистика чтений/записей индексов определенной таблицы
select *, cast(reads as real)/ cast(isnull(nullif(writes,0),1) as real) ratio
from   (
         SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName],
                i.name AS [IndexName] , i.index_id , 
                user_seeks + user_scans + user_lookups AS [Reads] , 
                user_updates AS [Writes] , 
                i.type_desc AS [IndexType] , 
                i.fill_factor AS [FillFactor]
         FROM   sys.dm_db_index_usage_stats AS s 
         JOIN   sys.indexes AS i ON s.[object_id] = i.[object_id]
         WHERE  OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 
           AND  i.index_id = s.index_id 
           AND  s.database_id = DB_ID()
           AND  OBJECT_NAME(s.[object_id]) = 'MyTableName'
       ) v
ORDER BY ratio;


В таблице есть 4 индекса, у двух из которых - количество чтений по нулям, количество записей постоянно растет
У двух других индексов растут и количества чтений, и количество записей.

Есть другой запрос (взятый из предыдущего поста этого топика ;) ), показывающий запросы, в плане выполнения которых используется конкретный индекс:
(в моём случае - это таблица 'MyTableName' и индекс по ней 'MyIndexName'):

+ Запросы, в планах которых используется указанный индекс
declare @database sysname = N'MyDB', 
        @schema   sysname = N'MySchema', 
        @table    sysname = N'MyTableName', 
        @index    sysname = N'MyIndexName';
-- 
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select qp.x, i.LogicalOp, object_schema_name(qt.objectid, qt.dbid), object_name(qt.objectid, qt.dbid), st.statement_text
from   sys.dm_exec_query_stats qs cross apply
       sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp 
cross  apply sys.dm_exec_sql_text(qs.sql_handle) qt 
cross  apply (select quotename(@database), quotename(@schema), quotename(@table), quotename(@index)) o([database], [schema], [table], [index]) 
cross  apply (select substring(qt.text, 
                               (qs.statement_start_offset / 2) + 1, 
                               ((case qs.statement_end_offset when -1 then datalength(qt.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2) + 1)) st(statement_text) 
cross  apply (select cast(case when st.statement_text is not null then tqp.query_plan end as xml) where charindex(o.[index], tqp.query_plan) > 0) qp(x) 
cross  apply (
               select t.n.value('../../@LogicalOp', 'nvarchar(100)')
               from   qp.x.nodes('//RelOp/IndexScan/Object[@Database = sql:column("o.database") and @Schema = sql:column("o.schema") and @Table = sql:column("o.table") and @Index = sql:column("o.index")]') t(n)
               where  qp.x is not null
             ) i(LogicalOp);


И этот запрос, по одному из "читаемых" индексов не возвращает результат.

Подскажитие, пожалуйста, как такое может быть - что кол-во чтений индекса постоянно растет, а запросов, использующих этот индекс, в базе нет?
Возможно, используемые запросы неверно понимаются мною или содержат логические ошибки? Может можно как-то по другому поискать источник чтения этого индекса?

Заранее спасибо за конструктивные ответы!
6 апр 18, 06:53    [21317228]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Щукина Анна
И этот запрос, по одному из "читаемых" индексов не возвращает результат.
Значит в кеше нету планов таких запросов.
6 апр 18, 10:04    [21317691]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
invm,

тогда возвращаемся к первой части вопроса - что увеличивает счетчик чтений индекса, если запрос его использующих - нет?
AdHoc-оптимизация отключена. В кэш попадает полная версия плана, а не заглушка. Среднее время жизни плана в кэше для уникального "одноразового" запроса (в нашей конфигурации) - 10 минут. Выполняю несколько раз подряд запрос по индексной статистике - вижу, что значение счетчика чтений меняется. Выполняю поиск плана - пустой результат...
6 апр 18, 11:37    [21318065]     Ответить | Цитировать Сообщить модератору
 Re: По планам запросов из библиотечного кэша вычислить использование определенного индекса.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Щукина Анна
invm,
тогда возвращаемся к первой части вопроса - что увеличивает счетчик чтений индекса, если запрос его использующих - нет?

вам ответили не что запросов нет,
а что планов таких запросов нет.
разницу чувствуете?
а почему их в кэше нет, другой вопрос.
допустим, я всюду использую option(recompile),
ибо мне надо, чтобы всегда под конкретные параметры план строился.
ну и все: recompile говорит, план в кэше не хранить.
---
итого.
запущу мой запрос 1000 раз, получу 1000 планов, но ни один из них в кэше вы не найдете
6 апр 18, 11:56    [21318161]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить