Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Новый индекс - производительность select vs insert  [new]
hey112
Guest
Как известно, при создании нового индекса требуется взвесить производительность чтений vs обновлений. Но что-то я нигде не встречал конкретных подходов собственно как это делать.
Поэтому для примера взял одну таблицу и типичный селект с джойном и поддерживающим этот селект индексом.
Что делает этот индекс - он заменяет скан таблицы на seek.

Таблица небольшая, 150 записей. При наличии индекса, будет следующая статистика:

select  ->>>  Table 'MyTable'. Scan count 2, logical reads 15, physical reads 0...
insert  ->>>  Table 'MyTable'. Scan count 0, logical reads 4, physical reads 0...


Без индекса:

select  ->>>  Table 'MyTable'. Scan count 2, logical reads 18, physical reads 0...
insert  ->>>  Table 'MyTable'. Scan count 0, logical reads 2, physical reads 0...


Т.е. имеем такую ситуацию, что селект потреебляет на 3 логических чтения меньше, а инсерт - на 2 больше. Т.е. выйгрыш от индекса в 1 логическое чтение в целом.
Два вопроса:
- Я правильно понимаю, что когда таблица станет большой, сотни тысяч записей - то разница (из-за скана в селекте) станет куда бОльшей, ведь скан будет всей таблицы, а логические чтения индекса при инсерте возрастут незначительно? С поправкой на количество селектов vs инсертов
- Так-ли надо вообще оценивать полезность индексов?
11 окт 17, 05:59    [20859530]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Добрый Э - Эх
Guest
hey112,

без соотношения чтение/запись делать оценку - бессмысленно. Если у тебя данные один раз вставляются, а затем охулиард раз читаются - индекс очень даже облегчет жизнь сервера. А если наоборот - в таблицу идет дохулион инсертов, но селектов из неё бывает раз в году (к примеру - таблица логов работы приложения, в которую всегда пишут, но читают её только при наличии каких-то проблем в работе приложения), то индекс нафик не нужен.
11 окт 17, 06:08    [20859531]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
hey112
Guest
без соотношения чтение/запись делать оценку - бессмысленно. Если у тебя данные один раз вставляются, а затем охулиард раз читаются - индекс очень даже облегчет жизнь сервера. А если наоборот - в таблицу идет дохулион инсертов, но селектов из неё бывает раз в году (к примеру - таблица логов работы приложения, в которую всегда пишут, но читают её только при наличии каких-то проблем в работе приложения), то индекс нафик не нужен.

я знаю, но предположим рассматривается "типичная" ситуация, где имеем скажем 60-80% селектов, и остальное инсерты, когда юзеры просматривают данные, и иногда их вставляют/обновляют
11 окт 17, 06:15    [20859533]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Добрый Э - Эх
Guest
hey112,

сделай тест на табличке с чуть большим числом строк. хотя бы на порядок-два. индексные чтения имеют оверхед в виде чтение структуры индекса. на малых таблицах он вносит существенный вклад в общее число логических чтений. на больших - этот вклад сильно меньше и в некоторых случаях его можно списать на погрешность измерения...
11 окт 17, 06:27    [20859537]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
hey112
Guest
Добрый Э - Эх
сделай тест на табличке с чуть большим числом строк. хотя бы на порядок-два. индексные чтения имеют оверхед в виде чтение структуры индекса. на малых таблицах он вносит существенный вклад в общее число логических чтений. на больших - этот вклад сильно меньше и в некоторых случаях его можно списать на погрешность измерения...


у меня нет сейчас под рукой таблицы с тысячами записей, меня в принципе интересует насколько больше логических чтений вставка в индекс такой таблицы будет потреблять, я сильно подозреваю что очень незначительно, а в сравнении со сканом таблицы тут выйгрыш будет очень большой.
Мне просто все индексы которые я создаю для поддержки своих запросов надо обосновывать, и желательно с цифрами на руках, а то менеджеры услышали где-то краем уха что индексы снижают производительность инсертов, и теперь пихают эти аргументы где можно и нельзя.
Как в других организациях подходят к этому вопросу, может есть стандартная техника как вычислить полезность индекса для конкретной системы?
11 окт 17, 06:47    [20859541]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Добрый Э - Эх
Guest
hey112,

чтобы цифры были более-менее реалистичными - нужно, как минимум, знать состав и распределение данных, структуру таблицы и примерную логику работы приложения с ней. В зависимости от этого результат [не] использования индекса может быть от крайне положительного до весьма негативного. без конкретики от тебя - любые данные здесь советы будут чистой воды "тыкать пальцем в небо"
11 окт 17, 07:04    [20859544]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
hey112
Guest
Добрый Э - Эх
чтобы цифры были более-менее реалистичными - нужно, как минимум, знать состав и распределение данных, структуру таблицы и примерную логику работы приложения с ней. В зависимости от этого результат [не] использования индекса может быть от крайне положительного до весьма негативного. без конкретики от тебя - любые данные здесь советы будут чистой воды "тыкать пальцем в небо"


я-ж написал, что предположим имеем 80% селектов, остальное инсерты. Также написал, что есть запрос, у которого этот индекс убирает скан. Инсерт для текущего размера таблицы с индексом занимает на 2 логических чтения больше, на бОльшей таблице это займет скажем на 10 чтений больше. Какая еще информация требуется и как она повлияет на решение?
11 окт 17, 08:33    [20859589]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
aleks222
Guest
hey112
Добрый Э - Эх
чтобы цифры были более-менее реалистичными - нужно, как минимум, знать состав и распределение данных, структуру таблицы и примерную логику работы приложения с ней. В зависимости от этого результат [не] использования индекса может быть от крайне положительного до весьма негативного. без конкретики от тебя - любые данные здесь советы будут чистой воды "тыкать пальцем в небо"


я-ж написал, что предположим имеем 80% селектов, остальное инсерты. Также написал, что есть запрос, у которого этот индекс убирает скан. Инсерт для текущего размера таблицы с индексом занимает на 2 логических чтения больше, на бОльшей таблице это займет скажем на 10 чтений больше. Какая еще информация требуется и как она повлияет на решение?


Не майтесь херней.
Теоретические изыскания, конечно, увлекательный процесс... НО!

Высший критерий истины - практика.

Если скорость исполнения запросов удовлетворяет - можно забить на все.

Засада только одна - проверять удовлетворительность быстродействия надо на максимальных размерах данных.
11 окт 17, 10:01    [20859718]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
архивариус
Member

Откуда:
Сообщений: 149
-- Possible Bad NC Indexes (writes > reads)  (Query 57) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
------

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!
11 окт 17, 12:53    [20860502]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
150 записей вообще не требуют индекса за исключением ключа.
11 окт 17, 15:32    [20861301]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
hey112
Guest
архивариус
-- Possible Bad NC Indexes (writes > reads)  (Query 57) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
------

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!

thanks, полезный скрипт
12 окт 17, 00:55    [20862562]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
hey112
Guest
aleks222
Не майтесь херней.
Теоретические изыскания, конечно, увлекательный процесс... НО!

Высший критерий истины - практика.

Если скорость исполнения запросов удовлетворяет - можно забить на все.

Засада только одна - проверять удовлетворительность быстродействия надо на максимальных размерах данных.

почему-же херней-то, ну устраивает скажем производительность даже со фул сканом, что-ж его теперь так и оставлять-ть что-ли? А потом клиент нарастит число пользователей и сервак начнет не справляться.
Вообще странно, я думал хоть где-то есть стандартизированные процедуры определения какие индексы включать в системе, а похоже народ везде делает это умозрительно
12 окт 17, 00:59    [20862563]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
hey112
Вообще странно, я думал хоть где-то есть стандартизированные процедуры определения какие индексы включать в системе, а похоже народ везде делает это умозрительно
Вы про стандарты iso-9000 по выбору индекса? Таких еще не придумали, приходится нанимать разработчиков или dba. Почти каменный век, да.

З.Ы. В целом, задача индексирования сводится к поиску оптимума, как не превышая максимально возможную утилизацию существующего железа по ресурсам получить максимальную выгоду (в любых попугаях) при запуске запросов на выборку или модификацию. Модель получится не самая простая, а затраты на ее составление, вполне возможно, превысят зарплату опытного человека, который "по наитию" сделает почти не хуже.

Сообщение было отредактировано: 12 окт 17, 01:20
12 окт 17, 01:08    [20862569]     Ответить | Цитировать Сообщить модератору
 Re: Новый индекс - производительность select vs insert  [new]
Massa52
Member

Откуда:
Сообщений: 373
http://www.sqlservercentral.com/blogs/nebraska-sql-from-dba_andy/2016/02/10/how-bad-are-your-indexes/
12 окт 17, 03:53    [20862591]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить