Оптимизировать SQL-запрос, TSQL. Ms sql оптимизация sql запросов

Отсутствующие индексы в MS-SQL или оптимизация «по-быстрому»

При выполнении запроса, как мы знаем, оптимизатор SQL Server исходя из существующих индексов и имеющейся свежей статистики пытается за разумное время найти лучший план запроса, конечно если этот план уже не «сидит» в кэше сервера, и запрос выполняется по этому плану и план сохраняется в кэш сервера. Если план уже построен для этого запроса ранее, то запрос выполняется по существующему плану.

Нам в этой теме интересен следующий момент: Во время компиляции плана запроса, при переборе возможных индексов, если лучшего индекса не нашлось (по мнению сервера), то в плане запроса помечается этот не найденный индекс, и сервер ведет статистику по таким индексам – сколько раз сервер бы воспользовался этим индексом и сколько стоил этот запрос. Эти отсутствующие индексы – missing indexes мы сейчас и разберем, что с ними делать и как с ними работать.

Предлагаю на примере разобраться с отсутствующими индексами. Создадим пару таблиц в нашей, БД на локальном или тестовом сервере:

if object_id ('orders_detail') is not null drop table orders_detail;

if object_id('orders') is not null drop table orders;

create table orders

(

id int identity primary key,

dt datetime,

seller nvarchar(50)

)

create table orders_detail

(

id int identity primary key,

order_id int foreign key references orders(id),

product nvarchar(30),

qty int,

price money,

cost as qty * price

)

with cte as

(

select 1 id union all

select id+1 from cte where id < 20000

)

insert orders

select

dt,

seller

from

(

select

dateadd(day,abs(convert(int,convert(binary(4),newid()))%365),'2016-01-01') dt,

abs(convert(int,convert(binary(4),newid()))%5)+1 seller_id

from cte

) c

left join

(

values

(1,'Vasa'),

(2,'Peta'),

(3,'Anna'),

(4,'Ira'),

(5,'Igor')

) t (id,seller) on t.id = c.seller_id

option(maxrecursion 0)

insert orders_detail

select

order_id,

product,

qty,

price

from

(

select

o.id as order_id,

abs(convert(int,convert(binary(4),newid()))%5)+1 product_id,

abs(convert(int,convert(binary(4),newid()))%20)+1 qty

from orders o cross join

(

select top(abs(convert(int,convert(binary(4),newid()))%5)+1) *

from

(

values (1),(2),(3),(4),(5),(6),(7),(8)

) n(num)

) n

) c

left join

(

values

(1,'Сахар', 50),

(2,'Молоко', 80),

(3,'Хлеб', 20),

(4,'Макароны', 40),

(5,'Пиво', 100)

) t (id,product, price) on t.id = c.product_id

Структура простая из 2х табличек: продажи где поля идентификатор, дата продажи и продавец и другая таблица – детализация этих продаж, где какие-то товары в этой продаже указаны с ценой и количеством.

Предлагаю посмотреть простой запрос и его план:

select count(*) from orders o join orders_detail d on o.id = d.order_id

where d.cost > 1800

На графическом отображении плана запроса видна подсказка зеленым цветом об отсутствующем индексе, если кликнуть по ней правой кнопкой мыши и выделить «Missing Index Details..» то получим текст предлагаемого индекса, в тексте только лишь убрать комментарии и дать какое-нибудь имя индексу и скрипт готов к выполнению.

Мы не будем строить этот индекс, который дала подсказка в SSMS, а посмотрим будет ли рекомендован индекс этот динамическими представлениями, связанными с отсутствующими индексами. Эти представления:

select * from sys.dm_db_missing_index_group_stats

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

Мы из этого видим, что в 1м представлении у нас есть статистика по отсутствующих индексах, а именно:

Сколько бы раз произвелся поиск если бы предложенный индекс существовал?
Сколько раз использовалось бы сканирование если бы предложенный индекс существовал.
Дата время последней потребности в этом индексе
Текущая реальная стоимость плана запроса без предлагаемого индекса.

2е представление это уже тело индекса по сути:

База данных
Объект/таблица
Сортированные колонки
Колонки включенные для увеличения покрытия индекса

3е представление - это связь 1го и 2х представлений.

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

with igs as

(

select *

from sys.dm_db_missing_index_group_stats

)

, igd as

(

select *,

isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col

from sys.dm_db_missing_index_details

)

select --top(10)

'use ['+db_name(igd.database_id)+'];

create index ['+'ix_'+replace(convert(varchar(10),getdate(),120),'-','')+'_'+convert(varchar,igs.group_handle)+'] on '+

igd.[statement]+'('+

case

when left(ix_col,1)=',' then stuff(ix_col,1,1,'')

when right(ix_col,1)=',' then reverse(stuff(reverse(ix_col),1,1,''))

else ix_col

end

+') '+isnull('include('+igd.included_columns+')','')+' with(online=on, maxdop=0)

' command

,igs.user_seeks

,igs.user_scans

,igs.avg_total_user_cost

from igs

join sys.dm_db_missing_index_groups link on link.index_group_handle = igs.group_handle

join igd on link.index_handle = igd.index_handle

where igd.database_id = db_id()

order by igs.avg_total_user_cost * igs.user_seeks desc

В порядке эффективности индексов выведены отсутствующие индексы. Идеально когда этот резалтсет ничего не возвращает, на нашем примере этот резалтсет возвратит минимум один индекс:

Когда совсем лень или некогда разбираться с тормозами у пары заказчиков я выполнял этот запрос, копировал первую колонку и выполнял на сервере. После этого тормоза уходили 😊.

Я рекомендую осознано подходить к полученной информации с этими индексами. Например, если рекомендовать ситема будет следующие индексы:

create index ix_01 on tbl1 (a,b) include (c)

create index ix_02 on tbl1 (a,b) include (d)

create index ix_03 on tbl1 (a)

И эти индексы используются для поиска/seek, то вполне очевидно, что логичнее заменить эти индексы на один который покроет все 3 предложенных:

create index ix_1 on tbl1 (a,b) include (c,d)

Т.е. как минимум ревью предлагаемых индексов перед тем как их накатить на боевой сервер. Хотя…. Повторюсь, например на сервер TFS я накатывал потерянные индексы и общая производительность выростала, а время на такую оптимизацию затрачено минимум. Хотя, впоследствии с ТФС 2015 на ТФС 2017 я столкнулся с тем что обновление не проходило из-за новых индексов. Но их легко можно найти были по маске

select * from sys.indexes where name like 'ix[_]2017%'

kurenkov.pro

Оптимизировать SQL-запрос, TSQL MS SQL Server

Я разработчик программного обеспечения, и я недавно обратился к DBA с просьбой оптимизировать запрос, который использует мое приложение. DBA сообщила, что запрос занимает около 50% от CPU и высоких операций ввода-вывода при его запуске. Запрос довольно прямолинейный, и я не уверен, как его оптимизировать.

Вопрос 1: Как я могу оптимизировать этот запрос?

Вопрос 2: это даже моя работа сделать это, не должно ли DBA быть более осведомленным в этом? Имейте в виду, что у нас нет разработчиков БД, просто администраторов баз данных и разработчиков программного обеспечения.

DB имеет приблизительно 30-50 миллионов записей, она постоянно поддерживается / контролируется администратором баз данных, но я не уверен, как это сделать. Сервер находится на выделенном компьютере и является Microsoft SQL Server 2005 - 9.00.5057.00 (X64)

PS: Пожалуйста, не предоставляйте способы улучшения БД структурными изменениями, я знаю, что плохой дизайн имеет валюту, хранящуюся как varchar, но это то, что есть, мы не можем изменять структуру БД, а только запросы, обращающиеся к ней.

Спасибо за понимание.

Похоже, у вас неправильное представление об индексах. Индексы не объединяются друг с другом, поэтому не обязательно иметь столбец «индексированный» или «не проиндексированный». Нехорошо иметь отдельный индекс для отдельных столбцов. Речь идет о индексах с несколькими столбцами, которые значительно отличаются от индивидуальных запросов. Индекс в столбце не поможет запросу, если он еще эффективнее для базы данных сначала выбрать другой столбец.

Я немного устарел, но для этого запроса я бы рекомендовал индекс, который выглядит примерно так:

CREATE NONCLUSTERED INDEX [ix_History_XXXXX] ON [History] ( [INNO] ASC, [Locked] ASC, [PDate] ASC, [PMode] ASC ) INCLUDE ( PStatus, PAmount, Fee)

Вы можете захотеть поменять местами PDate, PMode и PStatus, в зависимости от их избирательности .

При создании индекса сначала нужно указать наиболее конкретные элементы. Общая идея заключается в том, что индекс хранит каждый следующий элемент по порядку. С помощью этого индекса строки для всех значений XYZ для INNO будут сгруппированы вместе, и поэтому механизм запросов может искать право на этот раздел индекса. Следующий наиболее специфический столбец Locked . Несмотря на то, что это bit значение, потому что оно ограничено точно одним значением, мы все еще можем искать непосредственно одну конкретную часть индекса, которая будет иметь значение для всего запроса. Опять же: мне не приходилось делать подобные вещи какое-то время, так что вы могли бы также PMode здесь; Я просто не помню, является ли оптимизатор запросов Sql Server достаточно умным, чтобы эффективно обрабатывать два значения.

Здесь лучший вариант для индекса зависит от того, насколько каждый из значений запроса ограничивает результаты. Поскольку мы больше не можем получить все результаты в одном пространстве, нам нужно будет отсканировать соответствующие части индекса. Мой инстинкт здесь – использовать значение « Date . Это позволит сканировать индекс, начиная с первой даты, которая соответствует вашему результату, и поможет ему получить записи в правильном порядке, но опять же: это всего лишь мой инстинкт. Возможно, вы сможете сделать лучше, указав PMode или PStatus.

Наконец, добавление в предложение INCLUDES позволит вам полностью завершить этот запрос из индекса, не возвращаясь в полную таблицу. Вы используете предложение INCLUDES, а не просто добавляете значения в запрос, чтобы избежать того, чтобы Sql Server перестраивал индекс для обновлений этих столбцов. Вот почему, например, PStatus, вероятно, не должен быть частью основного индекса, если статус – это что-то, что может измениться, и почему вам может быть лучше, а также оставить Locked из индекса. Тем не менее, это вещи, которые вы хотите измерить и проверить сами.

Я бы просто создал индекс в следующей таблице:

CREATE NONCLUSTERED INDEX idx_History_Locked_PMode_INNO_PStatus_PDate_iPAmount_iFee ON dbo.History (Locked, PMode, INNO, PStatus, PDate) INCLUDE (PAmount, Fee) WHERE Locked = 1; -- This is optional, can reduce index size.

Это должно улучшить ваш текущий запрос. Здесь должны быть соблюдены все условия.

Я посмотрю, получаю ли я лучшие результаты с ISNULL вместо COALESCE.

Другое дело, глядя на индексы. Вы указали поля, индексированные. Если эти поля охвачены несколькими индексами, я предлагаю сделать один хороший индекс покрытия для этого запроса.

Индекс покрытия – это тот, где все данные, необходимые для запроса, содержатся в индексе. Если индекс, используемый в запросе, не покрывает, то есть дополнительная поездка (или поездки) в таблицу, чтобы получить остальные поля. Это более эффективно, если все данные находятся прямо в запросе.

Ознакомьтесь с этими статьями:

Что такое Covering Indexes и Covered Queries в SQL Server?

https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

Для данных, которые не являются частью соединения или в предложении where, вы можете использовать ключевое слово include. Включенные поля не подлежат поиску частями индекса, но сохранят поездку в базу данных.

Попробуйте индекс ниже. Все поля в предложении where являются частью поисковой части индекса, и все возвращаемые поля, которые не являются частью предложения where, включены. Возможно, вам придется сыграть с заказом, посмотрев план исполнения, но я подумал.

Create Nonclustered Index Ix_Ncl_History_CoveringBigSelect on History(PDate, PMode, INNO, PStatus, Locked) Include (PAmount, Fee)

Вот статья о включенных столбцах.

Вы правы, запрос выглядит нормально. Это прямой запрос, только с предложением «И» и отсутствием «NOT NULL» ограничения или объединения или подзапроса. Условия в основном равны (только дата реляционная). Если значения в условиях (например, «C», «P», «1», «XYZ», «CONSERVED» и т. Д.) Являются достаточно избирательными, чем вы (или администратор базы данных) должны определять некоторые индексы, и оптимизатор может использовать его , Попросите администратор базы данных создать соответствующий индекс для таблицы.

Сколько строк результата вы ожидаете получить? Если их много (например, >> 10 000), предложение ORDER BY может стоить много.

Как вы сказали, я полагаю, вы ничего не можете сделать для db, включите индексирование и структурные изменения. Итак, что же касается клиентской среды приложения, достаточно ли она достаточно для вычисления клиентской стороны?

Если да, я предлагаю переместить расчет на клиентскую сторону:

Не применяйте тип данных в запросе, отбрасывайте varchar до десятичного разряда, потребляя ресурсы ЦП. Получите результат напрямую и выполните преобразование в своем приложении.
Для проблемы ввода-вывода попытайтесь удалить условие IN, потому что IN по существу является условием «ИЛИ». Поэтому разделите свой запрос на мелкие кусочки, используя условие «=» и отправьте в свое приложение, используйте свое клиентское приложение для их объединения.

sqlserver.bilee.com

Оптимизация SQL-запросов путем удаления оператора сортировки в плане выполнения

Во-первых, вы должны убедиться, что сортировка на самом деле является узким местом производительности. Продолжительность сортировки будет зависеть от количества отсортированных элементов, а количество магазинов для определенного родительского хранилища, вероятно, будет небольшим. (Предполагается, что оператор сортировки применяется после применения предложения where).

Я слышал, что оператор Sort указывает на плохой дизайн в запросе, поскольку сортировка может быть сделана преждевременно через индекс

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

В вашем случае вы, кажется, извлекаете весь результирующий набор, поэтому сортировка вряд ли сделает вещи намного хуже (если набор результатов не огромен). Кроме того, в вашем случае не может быть тривиально создавать полезный отсортированный индекс, потому что предложение where содержит a или.

Теперь, если вы все еще хотите избавиться от этого типа-оператора, вы можете попробовать:

SELECT [Phone] FROM [dbo].[Store] WHERE [ParentStoreId] = 10 AND [Type] in (0, 1) ORDER BY [Phone]

Кроме того, вы можете попробовать следующий индекс:

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Phone], [Type])

чтобы попытаться заставить оптимизатор запросов выполнять сканирование диапазона индекса только на ParentStoreId , затем сканировать все соответствующие строки в индексе, выводя их, если Type совпадает. Однако это, скорее всего, вызовет больше дискового ввода-вывода и, следовательно, замедлит ваш запрос, а не ускорит его.

Изменить : в крайнем случае вы можете использовать

SELECT [Phone] FROM [dbo].[Store] WHERE [ParentStoreId] = 10 AND [Type] = 0 ORDER BY [Phone] UNION ALL SELECT [Phone] FROM [dbo].[Store] WHERE [ParentStoreId] = 10 AND [Type] = 1 ORDER BY [Phone]

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])

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

sqlserver.bilee.com

Запрос оптимизации SQL MS SQL Server

Следующий запрос MSSQL2005 выполняется очень медленно. Я чувствую, что они должны быть способ ускорить его, но я не знаю, как это сделать. Обратите внимание, что я редактировал внутреннее соединение для использования операторов select, чтобы сделать его более очевидным (для людей, читающих этот вопрос), что происходит, хотя это не влияет на скорость (вероятно, план Execution одинаковый в любом случае). Интересно, что я никогда не использовал ключевое словоvaluegroups для чего-либо большего, чем счетчик, но я не уверен, есть ли способ извлечь выгоду из этого.

Edit: По-видимому, люди продолжают жаловаться на мое использование подзапросов. На самом деле это не имеет значения. Я добавил их прямо перед публикацией этого вопроса, чтобы было легче увидеть, что происходит. Но они только делали вещи более запутанными, поэтому я изменил это, чтобы не использовать их.

Я бы удостоверился, что у вас есть следующие индексы.

Идентификатор в группах ключевых слов.

Имя в группах ключевых слов.

ID в ClientDefinitionEntry с INCLUDE для processPath.

CREATE INDEX [IX_ClientDefinitionEntry_Id_ProcessPath] ON [dbo].[ClientDefinitionEntry] ( [keywordGroupId] ASC ) INCLUDE ( [processPath]) ON [PRIMARY] CREATE INDEX [IX_KeywordValueGroups_Id] ON [dbo].[KeywordValueGroups] ( [keywordValueGrpId] ASC ) CREATE INDEX [IX_KeywordValueGroups_Name] ON [dbo].[KeywordValueGroups] ( [name] ASC )

Я также изменил бы запрос на следующее.

select top 1 cde.processPath as 'keywordValue', count(*) as 'total' from dbo.ClientDefinitionEntry AS cde INNER JOIN dbo.KeywordValueGroups AS kvg ON cde.keywordGroupId = kvg.keywordValueGrpId where kvg.[name] = @definitionName group by processPath order by total desc

Как выглядит план выполнения? Посмотрев на это, вы узнаете, какая часть запроса занимает больше всего времени / ресурсов.

У вас есть индексы на столбцах, на которых вы фильтруете? У вас есть индексы столбцов, которые вы используете для присоединения? У вас есть индексы столбцов, которые вы используете для сортировки?

как только вы взглянете на это, и запрос все еще медленный, вы можете посмотреть, как ваша база данных / таблица фрагментирована (dbcc showcontig), и посмотреть, нужно ли перестраивать индексы. Может оказаться полезным иметь план обслуживания, который регулярно обновляет ваши индексы.

Запустите запрос с помощью этой опции:

SET SHOWPLAN_TEXT ON

И добавьте результат к вопросу.

Также проверьте, обновлены ли ваши статистические данные:

SELECT object_name = Object_Name(ind.object_id), IndexName = ind.name, StatisticsDate = STATS_DATE(ind.object_id, ind.index_id) FROM SYS.INDEXES ind order by STATS_DATE(ind.object_id, ind.index_id) desc

И информация об индексах, определениях таблиц и внешних ключах была бы полезна.

На самом деле информации недостаточно, чтобы точно знать. Если у вас возникают проблемы с производительностью в этом запросе, тогда таблицы должны иметь нетривиальное количество данных, и вам не нужно указывать важные индексы.

Какие индексы определенно помогут, во многом зависит от того, насколько велики таблицы и, в меньшей степени, для распределения значений в полях KeywordGroupId и KeywordValueGrpId.

Не имея никакой другой информации, я бы сказал, что вы хотите убедиться, что dbo.KeywordValueGroups.[name] индексируется, а также dbo.ClientDefinitionEntry.[keywordGroupId] .

Из-за способа написания запроса индекс в dbo.KeywordValueGroups.[keywordValueGrpId] не может помочь, но [name], [keywordValueGrpId] индекс на [name], [keywordValueGrpId] вероятно, будет. Если у вас есть этот индекс, вам не нужен выделенный индекс в [name] .

Основываясь только на чувстве кишки, я могу опасаться, что индекс в [name] является обязательным , и что cde.keywordGroupId, вероятно, важна. Будет ли [name], [keywordValueGrpId] индекс на [name], [keywordValueGrpId] помочь, это зависит от того, сколько записей существует с тем же [name].

Единственный способ узнать наверняка – добавить индексы и посмотреть, что произойдет.

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

Не знаете, сколько записей мы говорим, но это: порядок по общему описанию находится в вычисленном столбце, что означает, что каждый расчет по каждой строке должен быть выполнен до того, как будет выполнен заказ. Вероятно, это одна из вещей, замедляющих ее, но я не вижу выхода из этой конкретной проблемы. Не проблема, если у вас есть только несколько записей после присоединения, но может быть, если их много.

Сначала я сосредоточусь на индексировании. Мы часто забываем, что при создании ключей foriegn они автоматически не индексируются. Проверьте, проиндексированы ли обе части соединения.

Поскольку вы передаете значение в параметре, у вас может также возникнуть проблема с параметром sniffing. Google это для техник, чтобы исправить это.

sqlserver.bilee.com