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

Откуда: Киев
Сообщений: 98
Привет всем!

Есть вопрос — прошу подсказать, или моя теория верна. У меня есть таблица в которой все поля типа char, int, bit — т.е., поля фиксированного размера.

Однако, есть текстовое поле типа varchar(1000) — т.е., размер (длина) поля этого не фиксированная. Итого: при удалении записи из таблицы, скорее всего, будет возникать «дырка» в странице (page) индекса; при изменении значения поля varchar(1000) может так случится, что запись физически будет перемещена в другую страницу (page) индекса, что опять вызовет «дырку» в индексе. В конечном итоге, это всё порождает фрагментацию индекса.

Тут я вспомнил, что поля типа text/ntext в SQL Server хранятся в отдельном файле. Итого, если заменить поле varchar(1000) на text, то получится, что в таблице у меня останутся только поля фиксированного размера и, при удалении/обновлении данных, любая «дырка» будет занята новыми данными — таким образом, фрагментации можно избежать.

Правильно ли я мыслю и что вы думаете об этом?

Для примера, вот вам некоторая абстрактная табличка:

create table SomeTable(
    id int primary key,
    name char(25),
    blablaId int,
    biography varchar(1000) /* Это поле вызовет фрагментацию при его изменении, т.к., данные физически находятся в таблице. */ 
);

create table SomeTable(
    id int primary key,
    name char(25),
    blablaId int,
    biography text /* А это поле фрагментации не вызовет, т.к., физически данных находятся ВНЕ таблицы, в отдельном файле. */
);
21 фев 14, 15:55    [15606204]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
Тут я вспомнил, что поля типа text/ntext в SQL Server хранятся в отдельном файле.

В отдельной файловой группе. Если вы указали это при создании таблицы.
А хранятся такие поля все равно страницами.
А при изменении данных вы получите теже самые "дырки". Особенно если сначала заносите большой объем информации в поле, а потом уменьшаете его
21 фев 14, 16:00    [15606237]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Спасибо за ответ!

Glory
В отдельной файловой группе. Если вы указали это при создании таблицы.


Подскажите, пожалуйста комманду для указания «хранить в отдельной файловой группе» — я думал это по умолчанию.

Glory
А хранятся такие поля все равно страницами. А при изменении данных вы получите теже самые "дырки". Особенно если сначала заносите большой объем информации в поле, а потом уменьшаете его


А вот это действительно плохо. И как-же тогда быть? Сделать поле типа char(1000), но это увеличит объём таблицы в разы.
21 фев 14, 16:08    [15606316]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Кстати, как вариант, я думаю, можно попытаться вынести поле varchar(1000) в отдельную таблицу №2. Таким образом будет фрагментироваться только таблица №2. Что думаете?
21 фев 14, 16:10    [15606328]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
Подскажите, пожалуйста комманду для указания «хранить в отдельной файловой группе» — я думал это по умолчанию.

CREATE TABLE

roman_lenko
И как-же тогда быть? Сделать поле типа char(1000), но это увеличит объём таблицы в разы.

Для чего вы делаете индекс по этому полю ?
21 фев 14, 16:12    [15606347]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
NickAlex66
Member

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

Указанные типы (text/image) хранятся не в отдельном файле, а скажем так в отдельной (от простых типов) области файла данных. При желании их можно вынести в отдельную ФГ.
Понятие фрагментации применяется на к столбцам, как таковым, а к индексам или куче.
Ну и при удалении/добавлении/изменении записи в таблице фрагментация, как правило, увеличивается в общем то не зависимо от типа данных. Но данные с переменным типом вносят больший вклад. Как то так.
21 фев 14, 16:12    [15606348]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Для чего вы делаете индекс по этому полю ?


Ну, я по этому полю индекс не делаю. По сути, у меня есть один кластеризированный индекс — id = PRIMARY KEY в который, как я понимаю, включены по умолчанию все поля таблицы. Когда я выполняю операцию UPDATE/DELETE для индекса id, изменяя значение того самого текстового поля varchar, то SQL Server даст мне «дырки». Я вижу это так.
21 фев 14, 16:18    [15606397]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
изменяя значение того самого текстового поля varchar, то SQL Server даст мне «дырки». Я вижу это так.

И в чем будет выигрыш то ? хранить пробелы от char(1000) или хранить "пробелы" от измененения данных varchar(1000) ?
21 фев 14, 16:21    [15606418]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Спасибо!

NickAlex66
roman_lenko,

Указанные типы (text/image) хранятся не в отдельном файле, а скажем так в отдельной (от простых типов) области файла данных. При желании их можно вынести в отдельную ФГ.


Каким образом? — не могу понять.

NickAlex66
Понятие фрагментации применяется на к столбцам, как таковым, а к индексам или куче.
Ну и при удалении/добавлении/изменении записи в таблице фрагментация, как правило, увеличивается в общем то не зависимо от типа данных. Но данные с переменным типом вносят больший вклад. Как то так.


Странно. Если у меня есть, скажем, всё поля типа INT, каждое по 4 байта и я выполнил команду UPDATE, то размер-то поля не поменялся — оно как было 4 байта, так и осталось — ничего не вылезло, ничего не было перемещено в другую страницу. Если я выполняю DELETE, то здесь — да, «дырка» образовалась, но со временем эта «дырка» будет замещена другими данными — т.к., SQL Server умеет «находить» такие дырки и вставлять под них данных подходящего размера.
21 фев 14, 16:22    [15606427]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
И в чем будет выигрыш то? хранить пробелы от char(1000) или хранить "пробелы" от измененения данных varchar(1000)?


Ну, char(1000) всегда хранит, скажем, ~1000 байт информации. В то время как varchar(1000) изначально хранит именно столько байт, сколько я туда вставил, например — 300 и, исходя из текущего размера (300 байт) СУБД уже выстаивает страницу индекса соответствующим образом. Если в будущем я запишу в это же поле не 300 байт, а, скажем, 800, то СУБД, скорее всего, целиком переместит мою строку в другую часть страницы индекса (или вообще в другую страницу), т.к., её размер увеличился а данные пихать уже некуда.
21 фев 14, 16:27    [15606463]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
Каким образом? — не могу понять.

Так же как и вообще все данные в MSSQL
В таблице хранится ссылка на первую страницу поля. В конце страницы есть ссылка на следующую страницу, в ней на следующую и тд

roman_lenko
Странно. Если у меня есть, скажем, всё поля типа INT, каждое по 4 байта и я выполнил команду UPDATE, то размер-то поля не поменялся — оно как было 4 байта, так и осталось — ничего не вылезло, ничего не было перемещено в другую страницу. Если я выполняю DELETE, то здесь — да, «дырка» образовалась, но со временем эта «дырка» будет замещена другими данными — т.к., SQL Server умеет «находить» такие дырки и вставлять под них данных подходящего размера.

Что поменяется с text полем ? Что дырки будут не в страницах со всеми полями, а только в страницах с text полем ?
Будет выигрыш, если вы редко обращаетесь к этому полю. А если оно у вас в кажом запросе, так еще и накладнее будет
21 фев 14, 16:28    [15606467]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
Ну, char(1000) всегда хранит, скажем, ~1000 байт информации. В то время как varchar(1000) изначально хранит именно столько байт, сколько я туда вставил, например — 300 и, исходя из текущего размера (300 байт) СУБД уже выстаивает страницу индекса соответствующим образом. Если в будущем я запишу в это же поле не 300 байт, а, скажем, 800, то СУБД, скорее всего, целиком переместит мою строку в другую часть страницы индекса (или вообще в другую страницу), т.к., её размер увеличился а данные пихать уже некуда.

Да хоть миллион байт.
А у вас что все эти 100 байт будут заполнены именно информацией, а не пробелами ?
21 фев 14, 16:29    [15606481]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
roman_lenko,

http://technet.microsoft.com/ru-ru/library/ms186981(v=sql.105).aspx
21 фев 14, 16:32    [15606503]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Что поменяется с text полем ? Что дырки будут не в страницах со всеми полями, а только в страницах с text полем ?
Будет выигрыш, если вы редко обращаетесь к этому полю. А если оно у вас в кажом запросе, так еще и накладнее будет


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

Glory
Будет выигрыш, если вы редко обращаетесь к этому полю. А если оно у вас в кажом запросе, так еще и накладнее будет


Ну, это поле достаточно частое в обращении. С ним работают команды SELECT, DELETE и INSERT. Реже — UPDATE.

Glory
А у вас что все эти 1000 байт будут заполнены именно информацией, а не пробелами ?


Нет — что люди напишут — то и будет. Кто — 300 байт, кто — 500, кто всю 1000.
21 фев 14, 16:35    [15606529]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
roman_lenko
Ну, вот я надеялся на то, что, если это поле будет физически где-то расположено вне таблицы в отдельном файле, типа на него будет просто какой-то указатель фиксированного размера храниться (типа, как pointer в C++), поэтому файлу пофигу — изменяй его как хочешь — он являет собой статическую обособленную единицу.

И в чем выигрыш при обращении к этим данным ? Будет читаться меньше страниц что ли ?

roman_lenko
Нет — что люди напишут — то и будет. Кто — 300 байт, кто — 500, кто всю 1000.

Объясняю на пальцах
create table t1 (f1 char(8000))
create table t2 (f1 varchar(8000))
если я добавлю t1 100 записей 'A', то я получу 100 страниц данных
если я добавлю t2 100 записей 'A', то я получу 1 страницу данных

Как бы дальше я не модицировал данные t1, там всегда будет 100 страниц.
Для того, чтобы t2 достигла размера в 100 страниц, мне нужно будет целенаправленно в каждую запись занести 8000 байт.
Как вы думаете, чтение какой таблицы с диска будет дешевле ?
21 фев 14, 16:42    [15606583]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Glory
Объясняю на пальцах
create table t1 (f1 char(8000))
create table t2 (f1 varchar(8000))
если я добавлю t1 100 записей 'A', то я получу 100 страниц данных
если я добавлю t2 100 записей 'A', то я получу 1 страницу данных

Как бы дальше я не модицировал данные t1, там всегда будет 100 страниц.
Для того, чтобы t2 достигла размера в 100 страниц, мне нужно будет целенаправленно в каждую запись занести 8000 байт.
Как вы думаете, чтение какой таблицы с диска будет дешевле ?


Браво! — теперь я уловил мысль — спасибо большое!
21 фев 14, 16:50    [15606643]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Exproment
Member

Откуда:
Сообщений: 416
Я вижу смысл от такого действия в случаях большой нагрузки на таблицу по полям кроме text. Например, для сущности в которой есть description(типа text) большого размера. По остальным полям часто выполняются различные агрегации, машинные вычисления и т.д. А Задача description - только показать описание пользователю по запросу.

Тогда выполняя большинство операций вы просто будете меньше страниц читать с диска, что предоставит выигрыш.
21 фев 14, 16:57    [15606694]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Exproment
Я вижу смысл от такого действия в случаях большой нагрузки на таблицу по полям кроме text. Например, для сущности в которой есть description(типа text) большого размера. По остальным полям часто выполняются различные агрегации, машинные вычисления и т.д. А Задача description - только показать описание пользователю по запросу.

Тогда выполняя большинство операций вы просто будете меньше страниц читать с диска, что предоставит выигрыш.
Ну, уберите вы эти текстовые поля в другую таблицу, раз нет возможности нормально проиндексировать таблицу, чтобы большинство операций не сканило кластерный индекс.
21 фев 14, 17:00    [15606713]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
roman_lenko
Member

Откуда: Киев
Сообщений: 98
Exproment
Я вижу смысл от такого действия в случаях большой нагрузки на таблицу по полям кроме text. Например, для сущности в которой есть description(типа text) большого размера. По остальным полям часто выполняются различные агрегации, машинные вычисления и т.д. А Задача description - только показать описание пользователю по запросу.

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


Сильно сложно :) Боюсь, что это уже не из моей оперы.

Гавриленко Сергей Алексеевич
Ну, уберите вы эти текстовые поля в другую таблицу, раз нет возможности нормально проиндексировать таблицу, чтобы большинство операций не сканило кластерный индекс.


Видимо, так и буду делать.

Хотя Glory сделал для меня «разрыв шаблона» по поводу того как организуются строковые данные в таблице — буду думать.
21 фев 14, 17:14    [15606800]     Ответить | Цитировать Сообщить модератору
 Re: Использование типа text/ntext для уменьшения фрагментации индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
roman_lenko
Если я выполняю DELETE, то здесь — да, «дырка» образовалась, но со временем эта «дырка» будет замещена другими данными — т.к., SQL Server умеет «находить» такие дырки и вставлять под них данных подходящего размера.
Т.е. вы хотите сказать, что сервер запихает значение 1234 в страницу кластерного индекса где уже хранятся, скажем id с 25 по 87, только потому что там дырка подходящего размера есть?
22 фев 14, 03:29    [15609679]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить