Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Фрагментация индексов в Ms SQL  [new]
deadka
Member

Откуда:
Сообщений: 29
Доброго времени суток, Коллеги!

Подскажите кто знает плиз по след. пунктам:

1) Нужно ли (и если нужно, то как) производить при насыщенной вставками работе с таблицами ту операцию, которая в Postrge
именуется vacuum full (которая включает в себя reindex)?

2) Если кто может дать пруфы на статьи/книги по Ms SQL, в которых хорошо или хотя бы средне описано в деталях про фрагментацию индексов в MS SQL - что это, с чем едят и как с ней грамотно работать - поделитесь плиз.
28 май 17, 23:07    [20518654]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
если в гугле набрать "t-sql фрагментация индексов" то хватит не на один день чтива.
28 май 17, 23:14    [20518662]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
deadka
Доброго времени суток, Коллеги!

Подскажите кто знает плиз по след. пунктам:

1) Нужно ли (и если нужно, то как) производить при насыщенной вставками работе с таблицами ту операцию, которая в Postrge
именуется vacuum full (которая включает в себя reindex)?

2) Если кто может дать пруфы на статьи/книги по Ms SQL, в которых хорошо или хотя бы средне описано в деталях про фрагментацию индексов в MS SQL - что это, с чем едят и как с ней грамотно работать - поделитесь плиз.

Отвечаю коротко:
1. Нет, не нужно. Фрагментация индексов практически не влияет на производительность. В т.ч. фрагментация кластерного индекса.
В принципе, при неоптимальном FillFactor'е таблички и индексы могут "распухать" (иногда даже довольно сурово, раза эдак в 2 в сравнение с объемом который могли бы занимать), но влияние этого на производительность находится на -надцатом месте.
2. Если база изолированная (т.е. не входит в схему с репликацией, логшиппингом и т.д.) - индексы можно перестроить, но особого смысла в типичном случае в этом нет.
Это не постгресс. Здесь всё и так работает.
29 май 17, 08:03    [20518856]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleksrov
Member

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

Одна и таже тема каждую недели. Несколькими темами ниже уже об этом говорили.
Ваш второй вопрос: SQL Server Internals, 2 издание стр. с 31 - 55 и 141- 178 (но лучше уж прочитайте книгу целиком).
Также:
https://blogs.msdn.microsoft.com/timchapman/2012/09/28/index-rebuild-vs-reorganize-the-transaction-log-edition/
https://www.sqlskills.com/blogs/jonathan/does-index-fragmentation-matter-with-ssds/
29 май 17, 08:30    [20518895]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleks2
Guest
uaggster
deadka
Доброго времени суток, Коллеги!

Подскажите кто знает плиз по след. пунктам:

1) Нужно ли (и если нужно, то как) производить при насыщенной вставками работе с таблицами ту операцию, которая в Postrge
именуется vacuum full (которая включает в себя reindex)?

2) Если кто может дать пруфы на статьи/книги по Ms SQL, в которых хорошо или хотя бы средне описано в деталях про фрагментацию индексов в MS SQL - что это, с чем едят и как с ней грамотно работать - поделитесь плиз.

Отвечаю коротко:
1. Нет, не нужно. Фрагментация индексов практически не влияет на производительность. В т.ч. фрагментация кластерного индекса.
В принципе, при неоптимальном FillFactor'е таблички и индексы могут "распухать" (иногда даже довольно сурово, раза эдак в 2 в сравнение с объемом который могли бы занимать), но влияние этого на производительность находится на -надцатом месте.
2. Если база изолированная (т.е. не входит в схему с репликацией, логшиппингом и т.д.) - индексы можно перестроить, но особого смысла в типичном случае в этом нет.
Это не постгресс. Здесь всё и так работает.


Ну... поздравляю вас соврамши.

Влияние фрагментации везде одинаковое.
В "два раза распухший индекс" с диска будет подниматься в два раза дольше и занимать места в памяти в два раза больше.
29 май 17, 08:36    [20518906]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
aleks2
В "два раза распухший индекс" с диска будет подниматься в два раза дольше и занимать места в памяти в два раза больше.

Мда?
В 2 раза больший размер файла != в 2 раза дольше подниматься (если только таблица не будет подниматься целиком), и уж тем более != в 2 раза больше места в памяти (от слова совершенно).
И, кстати, в 2 раза распухший против минимального размера файл можно получить только сделав что-то типа кластерного индекса по guid. Во всех других случаях - это еще умудриться надо.
29 май 17, 09:22    [20518992]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleks2
Guest
uaggster
aleks2
В "два раза распухший индекс" с диска будет подниматься в два раза дольше и занимать места в памяти в два раза больше.

Мда?
В 2 раза больший размер файла != в 2 раза дольше подниматься (если только таблица не будет подниматься целиком), и уж тем более != в 2 раза больше места в памяти (от слова совершенно).
И, кстати, в 2 раза распухший против минимального размера файл можно получить только сделав что-то типа кластерного индекса по guid. Во всех других случаях - это еще умудриться надо.


Не, я тоже хачу этой травы!
29 май 17, 09:26    [20519001]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleksrov
Member

Откуда:
Сообщений: 948
uaggster
aleks2
В "два раза распухший индекс" с диска будет подниматься в два раза дольше и занимать места в памяти в два раза больше.

Мда?
В 2 раза больший размер файла != в 2 раза дольше подниматься (если только таблица не будет подниматься целиком), и уж тем более != в 2 раза больше места в памяти (от слова совершенно).
И, кстати, в 2 раза распухший против минимального размера файл можно получить только сделав что-то типа кластерного индекса по guid. Во всех других случаях - это еще умудриться надо.


Да... А SQL сразу данные с диска читает, напрямую, да?
Когда вы делаете скан, ему надо будет прочитать ВСЕ, и если индекс занимает в раза больще нужного столько в кеш и ляжет.
И не фига, не только GUID, а еще after тригеры, optimistic isolation level или поле которое обновилось и строка стала гораздо больше
29 май 17, 09:28    [20519008]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Фрагментация индексов не настолько может влиять, насколько устаревшие статистики, например. Тем не менее, существуют практические рекомендации так называемых "лучших практик".
29 май 17, 10:55    [20519290]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
aleksrov
uaggster
пропущено...

Мда?
В 2 раза больший размер файла != в 2 раза дольше подниматься (если только таблица не будет подниматься целиком), и уж тем более != в 2 раза больше места в памяти (от слова совершенно).
И, кстати, в 2 раза распухший против минимального размера файл можно получить только сделав что-то типа кластерного индекса по guid. Во всех других случаях - это еще умудриться надо.


Да... А SQL сразу данные с диска читает, напрямую, да?

Неа. Он сначала в BufferPool ищет.
Так что даже если скан сделать - не факт, что он хоть что-то с диска поднимет.
aleksrov
Когда вы делаете скан, ему надо будет прочитать ВСЕ, и если индекс занимает в раза больще нужного столько в кеш и ляжет.
И не фига, не только GUID, а еще after тригеры, optimistic isolation level или поле которое обновилось и строка стала гораздо больше

А вот не стоит путать: "Занимает на диске в 2 раза больше места, чем может" (из-за pagesplit'ов, например), и "размер индекса больше в 2 раза, чем положено".
В памяти и основательно фрагментированный и "чистый" индексы будут занимать совершенно одинаковое место.

И потом, я ж не говорю, что фрагментация не влияет на производительность. Влияет.
Только это влияние оно ближе к влиянию фазы луны.
И не сравниться с влиянием тухлой статистики, неоптимальной структуры индексов или криво закешировавшегося плана (из- за хреного обыгранного параметер сниффинга).
Короче это то, чем заниматься надо как нибудь потом.
29 май 17, 11:08    [20519349]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
sql_user2
Member

Откуда:
Сообщений: 382
От типа диска многое зависит.
Однако на память влияет "лишнии" считанные данные.

+


Да будет срач

Картинка с другого сайта.

29 май 17, 11:17    [20519388]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
o-o
Guest
[quot uaggster
А вот не стоит путать: "Занимает на диске в 2 раза больше места, чем может" (из-за pagesplit'ов, например), и "размер индекса больше в 2 раза, чем положено".
В памяти и основательно фрагментированный и "чистый" индексы будут занимать совершенно одинаковое место.
[/quot]
в памяти лежат ровно те же страницы, что и на диске.
так что если что-то на диске занимает 1000 полупустых страниц,
читается полностью в память,
то и в памяти будут все те же 1000 полупустых страниц.
ровно с теми же номерами.
маппинг 1 в 1
29 май 17, 11:56    [20519551]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleks2
Guest
uaggster
В памяти и основательно фрагментированный и "чистый" индексы будут занимать совершенно одинаковое место.


Сынок, это фантастика.
В памяти точно те же страницы, что и на диске.
Ибо никаких преобразований страниц сервер не выполняет.
29 май 17, 14:10    [20520041]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
aleks2
uaggster
В памяти и основательно фрагментированный и "чистый" индексы будут занимать совершенно одинаковое место.


Сынок, это фантастика.
В памяти точно те же страницы, что и на диске.
Ибо никаких преобразований страниц сервер не выполняет.

Мдя? И если страница объявлена сжатой?
И если на странице есть вычисляемые значения, не объявленные как persisted?
Ну, и потом - чтобы такие вещи "выправить", нужно производить реорганизацию файла данных. С высвобождением освободившегося места.
Много желающих это делать?
А как часто делаете?
))

И потом - причем тут индексы то?
Если индекс стал неоптимальным, то в структуре добавляется лишь еще один указатель (уровень указателей). Количество страниц с данными индекса останется [примерно] прежним. Разве нет?
:)
29 май 17, 15:50    [20520503]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleks2
Guest
uaggster
aleks2
пропущено...


Сынок, это фантастика.
В памяти точно те же страницы, что и на диске.
Ибо никаких преобразований страниц сервер не выполняет.

Мдя? И если страница объявлена сжатой?
И если на странице есть вычисляемые значения, не объявленные как persisted?
Ну, и потом - чтобы такие вещи "выправить", нужно производить реорганизацию файла данных. С высвобождением освободившегося места.
Много желающих это делать?
А как часто делаете?
))

И потом - причем тут индексы то?
Если индекс стал неоптимальным, то в структуре добавляется лишь еще один указатель (уровень указателей). Количество страниц с данными индекса останется [примерно] прежним. Разве нет?
:)


Вашей больной фантазии нельзя позавидовать.

Вам станет легше, если вы рассмотрите обычный сферический индекс в вакууме.
И не будете забивать отсутствующий мозг всякой ерундой.
29 май 17, 16:16    [20520637]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
uaggster,

uaggster
Мдя? И если страница объявлена сжатой?
И если на странице есть вычисляемые значения, не объявленные как persisted?

8 Кб страница занимает 8 Кб буферного пула как вы ее не объявляйте и не обзывайте. Страниц другого размера в MSSQL нет.

uaggster
Если индекс стал неоптимальным, то в структуре добавляется лишь еще один указатель (уровень указателей). Количество страниц с данными индекса останется [примерно] прежним. Разве нет?
Это вы сейчас про какую СУБД рассказываете?
29 май 17, 16:40    [20520731]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
o-o
Guest
иногда болд проканывает.
ибо даже если хором одно и то же говорят, пока что толку 0
o-o
uaggster
А вот не стоит путать: "Занимает на диске в 2 раза больше места, чем может" (из-за pagesplit'ов, например), и "размер индекса больше в 2 раза, чем положено".
В памяти и основательно фрагментированный и "чистый" индексы будут занимать совершенно одинаковое место.

в памяти лежат ровно те же страницы, что и на диске.
так что если что-то на диске занимает 1000 полупустых страниц,
читается полностью в память,
то и в памяти будут все те же 1000 полупустых страниц.
ровно с теми же номерами.
маппинг 1 в 1
29 май 17, 17:29    [20520920]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
msLex
Member

Откуда:
Сообщений: 7734
o-o
иногда болд проканывает.
ибо даже если хором одно и то же говорят, пока что толку 0
o-o
пропущено...

в памяти лежат ровно те же страницы, что и на диске.
так что если что-то на диске занимает 1000 полупустых страниц,
читается полностью в память,
то и в памяти будут все те же 1000 полупустых страниц.
ровно с теми же номерами.
маппинг 1 в 1



После вот этого
uaggster
Мдя? И если страница объявлена сжатой?
И если на странице есть вычисляемые значения, не объявленные как persisted?
Ну, и потом - чтобы такие вещи "выправить", нужно производить реорганизацию файла данных. С высвобождением освободившегося места.
Много желающих это делать?
А как часто делаете?
))

И потом - причем тут индексы то?
Если индекс стал неоптимальным, то в структуре добавляется лишь еще один указатель (уровень указателей). Количество страниц с данными индекса останется [примерно] прежним. Разве нет?
:)


есть подозрения, что гражданин вообще не понимает как устроена работа с данными в MSSQL и болд, к сожалению, не поможет.
29 май 17, 17:41    [20520977]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
aleksrov
Member

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

Не, он просто очень упорный, ему уже трое написали, но нет, он считает (насколько я понял его фантазии), что страница если сжата, то когда сервер ее кидает в кеш она будет не сжатая и занимать больше места.
Из книги вырезка:
+

Data pages in the buffer pool store data in the same format as data is stored on disk. Compression is
implemented transparently to other SQL Server features; that is, SQL Server components that access data do
not know if compression is used or not.
Even though data compression allows you to put more rows into data pages, it does not increase the
amount of data that a row can store. The 8,060 bytes maximum row size limitation still applies, regardless of
the compression settings. SQL Server guarantees that the disabling of data compression will always succeed
and, therefore, an uncompressed row must always fit on a data page.
Data compression can significantly reduce table storage space at the cost of extra CPU load, especially
when data is modified. However, compressed data uses less space in the buffer pool and requires fewer I/O
operations, which can improve the performance of the system. Row compression could be a good choice even
with volatile data on non-heavily-CPU-bound systems. Page compression is a good choice for static data.

Если не persisted, то SQL будет считать их на лету, это никак не скажется на хранении, а лишь на операциях чтения, которые станут медленее.
uaggster
Ну, и потом - чтобы такие вещи "выправить", нужно производить реорганизацию файла данных. С высвобождением освободившегося места.
Много желающих это делать?
А как часто делаете?
))

И потом - причем тут индексы то?
Если индекс стал неоптимальным, то в структуре добавляется лишь еще один указатель (уровень указателей). Количество страниц с данными индекса останется [примерно] прежним. Разве нет?
:)

Ну это я вообще не могу понять о чем он.
30 май 17, 05:17    [20521769]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
aleksrov
msLex,

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

Ладно, ладно, убедили.
Хотя мне казалось, что описанное поведение еще в 2008 аннонсировали.
aleksrov
uaggster
Ну, и потом - чтобы такие вещи "выправить", нужно производить реорганизацию файла данных. С высвобождением освободившегося места.
Много желающих это делать?
А как часто делаете?
))

...

Ну это я вообще не могу понять о чем он.

О вопросе автора топика.
Т.е. ладно.
Возможно, файлы данных могут "распухать" из - за pagesplit'ов. Возможно - из-за того, что происходит вставка в, скажем так, неоптимальный кластерный индекс (ну, например, создали вы кластерный индекс на основе guid, задали ему fillfactor=100, и вуаля, файл распух в 2 раза спустя какое то время).
Дальше то что?
Как вы предлагаете с этим бороться?
Производить реорганизацию файлов данных со сжатием, как спросил ТС?
И как часто вы это делаете, мне интересно? :-))
30 май 17, 10:57    [20522437]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
uaggster
aleksrov
msLex,

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

Ладно, ладно, убедили.
Хотя мне казалось, что описанное поведение еще в 2008 аннонсировали.
aleksrov
пропущено...

Ну это я вообще не могу понять о чем он.

О вопросе автора топика.
Т.е. ладно.
Возможно, файлы данных могут "распухать" из - за pagesplit'ов. Возможно - из-за того, что происходит вставка в, скажем так, неоптимальный кластерный индекс (ну, например, создали вы кластерный индекс на основе guid, задали ему fillfactor=100, и вуаля, файл распух в 2 раза спустя какое то время).
Дальше то что?
Как вы предлагаете с этим бороться?
Производить реорганизацию файлов данных со сжатием, как спросил ТС?
И как часто вы это делаете, мне интересно? :-))

вроде и слова из темы, а предложения ну такой бред...
30 май 17, 11:01    [20522451]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
uaggster
Member

Откуда:
Сообщений: 769
TaPaK
вроде и слова из темы, а предложения ну такой бред...

А... ну то есть по делу сказать нечего.
Жаль...
Так сколько раз в день файлы данных сжимать то?
:-)))
31 май 17, 16:17    [20528053]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
o-o
Guest
uaggster
Так сколько раз в день файлы данных сжимать то?
:-)))

где интересно вот тут написано о сжатии файлов?
deadka
Доброго времени суток, Коллеги!

1) Нужно ли (и если нужно, то как) производить при насыщенной вставками работе с таблицами ту операцию, которая в Postrge
именуется vacuum full (которая включает в себя reindex)?

2) Если кто может дать пруфы на статьи/книги по Ms SQL, в которых хорошо или хотя бы средне описано в деталях про фрагментацию индексов в MS SQL - что это, с чем едят и как с ней грамотно работать - поделитесь плиз.

в первом пункте он про ребилд спрашивает, и что, его никто не делает что ли?
во втором литературой интересуется, и правильно делает:
всяко лучше прочесть о том, как все устроено, чем придумать самому и орать о своих фантазиях в рупор с трибуны форума
31 май 17, 16:26    [20528103]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
o-o
Guest
deadka
вот хорошее видео, все разъясняющее:
+ Paul Randal - Index Fragmentation Internals, Analysis, and Solutions



вот, где можно почитать о фрагментации:подборка статей Рэндала с тэгом index fragmentation
лучше начать с видео, а потом уже статьи почитать
31 май 17, 16:36    [20528146]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индексов в Ms SQL  [new]
Добрый Э - Эх
Guest
[quot o-o]где интересно вот тут написано о сжатии файлов?
deadka
Доброго времени суток, Коллеги!

1) Нужно ли (и если нужно, то как) производить при насыщенной вставками работе с таблицами ту операцию, которая в Postrge
именуется vacuum full (которая включает в себя reindex)?
Давай я тебе отвечу за uaggster-а... Судя по всему ты с Postrge не работал, отсюда у тебя с ним взаимное недопонимание... Дело в том, что vacuum full в Postgre - это не просто удаление мусора, сбор статистики и ребилд индекса. Это, в том числе, и перенос табличных данных в новый датафайл, с последующим высвобождением ранее занятого, но теперь освободившегося, пространства обратно в файловую систему операционной системы. То есть - это то самое "сжатие файлов", про которое вещает uaggster. После vacuum full датафалы базы реально ужимаются, если в них есть достаточно много замусоренного пространства. Если бы речь шла за просой вакуум, без фула - то да - он лишь подчищает мусор внутри датафайла, а высвободившееся место возвращает таблицам (но не файловой системе операционнки) для повторного использования и в этом случае датафайлы (с точки зрения файловой системы) свой размер не изменяют и свободного места в файловой системе сервера не добавляют...
1 июн 17, 06:33    [20529468]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить