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

Откуда:
Сообщений: 103
Есть таблица такой структуры:

CREATE TABLE Data
(
	DataID uniqueidentifier NOT NULL,
	TVID uniqueidentifier NOT NULL,
	TypeDataID uniqueidentifier NOT NULL,
	dtMeasure datetime NOT NULL,
	Value1 float NULL,
	Value2 float NULL,
	Value3 flaot NULL,
	Value4 float NULL,
	Value5 float NULL,
	..................
	Value495 float NULL,
	Value496 float NULL,
	Value497 float NULL,
	Value498 float NULL,
	Value499 float NULL,
	Value500 float NULL,
	DeleteStateCode int NOT NULL,
	
	CONSTRAINT PK_Data PRIMARY KEY CLUSTERED
	(
		DataID ASC
	)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX TVIndex ON Data
(
	TVID ASC,
	TypeDataID ASC,
	dtMeasure ASC
)
WHERE (DeleteStateCode = 0)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


В основном по данной таблице происходит такая выборка, меняются лишь наборы Value1...Value500:
SELECT dtMeasure, Value1, Value2, Value10, Value50
FROM Data
WHERE TVID = '00000000-0000-0000-0000-000000000000' AND
      TypeDataID = '00000000-0000-0000-0000-000000000000' AND
      dtMeasure BETWEEN '01.10.2015' AND '31.10.2015' AND
      DeleteStateCode = 0


Данная таблица содержит около 100 миллионов записей и имеет размер 250 гигабайт.
Просмотр данных в основном происходит за последний квартал.
Вставка новых данных в среднем за час 2500 записей (бывают пики, каждые 4 часа, записывается по 10-15 тысяч записей в час)
Обновления около 1000 записей в час.

Хочу секционировать данную таблицу поквартально в различные файловые группы и на разные диски.
И тут у меня возникает вопрос, как лучше это организовать?
Как это сделать оптимально быстро?

Есть 2 варианта:
1-й вариант:
- Создаем функцию и схему секционирования (по столбцу dtMeasure)
- Удаляем кластерный индекс
- Секционируем таблицу Data
- Создаем индексы
2-й вариант:
- Создаем функцию и схему секционирования (по столбцу dtMeasure)
- Создаем аналогичную таблицу DataTemp
- Секционируем таблицу DataTemp
- Перекачиваем данные из Data в DataTemp
- Создаем индексы
- Переименовывем таблицы

Предположительно, что второй вариант будет быстрее.

Так же хочу спросить про индексы.
В секционированной таблице нет возможности создать кластерный индекс, который был до этого, т.к. там нет ключа секционирования (dtMeasure)
Сейчас рассматриваю два варианта создания индексов:
1-й вариант:
- Создать уникальный индекс по столбцу DataID для ограничения PK
- Создать кластерный индекс по столбцам (DataID, TVID, TypeDataID, dtMeasure, DeleteStateCode)
2-й вариант:
- Создать уникальный индекс по столбцу DataID для ограничения PK
- Создать некластерный индекс по столбцам (TVID, TypeDataID, dtMeasure, DeleteStateCode)

Плюсы+/Минусы- первого варианта:
+ Ограничения PK
+ Имеется кластерный индекс (все данные отсортированы, быстрый поиск данных)
- Кластерный индек занимаемт много места (3 uniqueidentifier, 1 datetime, 1 int)
- Из-за кластерного индекса будет медленнее происходит вставка новых данных

Плюсы+/Минусы- второго варианта:
+ Ограничения PK
+ Быстрая вставка данных (из-за отсутствия кластерного индекса)
- Данная таблица будет ввиде "кучи" (heap)

Хотелось бы услашать мнения экспертов на мои вопросы.
Как организовать секционирование?
Какие индексы будут оптимальны?
10 ноя 15, 17:32    [18396185]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Владислав Колосов
Member

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

секционирование таблицы заключается в переливке данных. Как ни крути - это будет переливка. Секционирование некластерных индексов переливки не требует. Однако, секционирование индексов не достаточно для переключения секций. Так что сами решайте, что вам нужно.
Разносить по дискам необходимо только при равномерной нагрузке запросами на секции, иначе нет смысла.
10 ноя 15, 18:36    [18396572]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9161
Кластерный по uniqueidentifier, да еще и по трем полям - очень плохой выбор хоть с секциями, хоть без них.
10 ноя 15, 18:38    [18396588]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Владислав Колосов
Разносить по дискам необходимо только при равномерной нагрузке запросами на секции, иначе нет смысла.
Эээ, зачем разносить, какой это добавляет новый смысл секционированию?
11 ноя 15, 00:43    [18397731]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Max_Tpop
Как организовать секционирование?
Какие индексы будут оптимальны?
Что вы собственно хотите получить от секционирования?

Вот это самоцель:
Max_Tpop
Хочу секционировать данную таблицу поквартально в различные файловые группы и на разные диски.
?
Это же уменьшит производительность, если у вас обращения к данным по кварталам не строго равномерное.
11 ноя 15, 00:45    [18397736]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
alexeyvg
Владислав Колосов
Разносить по дискам необходимо только при равномерной нагрузке запросами на секции, иначе нет смысла.
Эээ, зачем разносить, какой это добавляет новый смысл секционированию?
А, ну да, я собственно это же и написал.

Я вот тут вообще не вижу постановки задачи, для чего собственно секционирование нужно в данном случае?
11 ноя 15, 00:48    [18397743]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Max_Tpop
Member

Откуда:
Сообщений: 103
Владислав Колосов,

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



alexeyvg,

Да, я забыл указать основную причину разбиения таблицы, заканчивается место на одном из дисков (скоростной RAID массив), хотелось бы перенести архивные данные на более медленный диск.
В основном просмотр данных осуществляется за последний квартал, архивные данные практически не используются.
11 ноя 15, 09:07    [18398248]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Владислав Колосов
Member

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

а какая разница - Вы все равно будете строить кластерный индекс, что приведет в пересортировке и фрагментации.
Переключите модель восстановления в Simple, если это возможно, и заливайте в пустую таблицу с кластерным индексом запросом с сортировкой по полю кластера.
11 ноя 15, 11:03    [18398827]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Max_Tpop
Да, я забыл указать основную причину разбиения таблицы, заканчивается место на одном из дисков (скоростной RAID массив), хотелось бы перенести архивные данные на более медленный диск.
В основном просмотр данных осуществляется за последний квартал, архивные данные практически не используются.
А, понял, да, тогда нормальное решение.

Но в общем нужно тогда прорабатывать модель подробнее.

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

Соответственно, если у вас просто PK GUID, то вы, например, при любом джойне с этой таблицей, делаете поиск во всех секциях.
Либо накладываете условие по полю секционирования - но вы же не знаете, к каким датам относится предполагаемый результат?

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

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

В общем, про модель данных и использование секционирования нужно думать.
11 ноя 15, 11:40    [18399009]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
alexeyvg
Max_Tpop
Да, я забыл указать основную причину разбиения таблицы, заканчивается место на одном из дисков (скоростной RAID массив), хотелось бы перенести архивные данные на более медленный диск.
В основном просмотр данных осуществляется за последний квартал, архивные данные практически не используются.
В общем, про модель данных и использование секционирования нужно думать.
Собственно, главный вопрос - что такое DataID ?

Если ссылок на него нет, сделан он, потому что ПК должен быть, а GID выбран потому, что это красиво, то тогда задача облегчается - нужно просто сделать правильный составной ПК, в котором первым полем будет дата.
Например, dtMeasure, TVID, TypeDataID, если они уникальны.
11 ноя 15, 11:48    [18399052]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Max_Tpop
Я знаю, что секционирование это переливка данных, но все же думаю, что в пустую таблицу (без индексов и без ключей) данные сольются быстрее (сегодня буду проводить тесты).
Важнее разработать правильную модель данных с учётом секционирования (разделение на оперативный и архивный носители), а технику переливки уже можно обсудить потом.
Логичнее проводить тесты на новую модель, на типичные и нетипичные запросы, смотреть планы и обращения к дискам.
11 ноя 15, 11:52    [18399081]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Max_Tpop
Насчет кластерного индекса по GUID,у я в курсе всех нюансов, но это сложилось исторически и сейчас не подлежит изменению.
Вот собственно вопрос - как, где сейчас используется этот DataId?
11 ноя 15, 11:52    [18399088]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Max_Tpop
Member

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

Насчет столбца DataID вы абсолютно правы, он сейчас есть, но не используется вовсе.
Набор dtMeasure, TVID, TypeDataID - не уникален
11 ноя 15, 11:57    [18399111]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Max_Tpop
Member

Откуда:
Сообщений: 103
Сейчас пока думаю, что самые оптимальные индексы будут такие:
Кластерный по dtMeasure, DataID
Некластерный по dtMeasure, TVID, TypeDataID и условие по DeleteStateCode=0
11 ноя 15, 12:04    [18399155]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Владислав Колосов
Member

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

я бы для секционирования (и кластерного индекса) выбрал dtMeasure.
11 ноя 15, 12:04    [18399161]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
Max_Tpop
Member

Откуда:
Сообщений: 103
Владислав Колосов,

Да, наверно это будет лучше. Правда сервер будет проставлять идентификаторы для уникальности адреса записи, т.к. даты повторяются, но все же это будет лучше чем с GUID'ом.
11 ноя 15, 12:15    [18399226]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование таблицы. Нуждаюсь в советах!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Max_Tpop
Да, наверно это будет лучше. Правда сервер будет проставлять идентификаторы для уникальности адреса записи, т.к. даты повторяются, но все же это будет лучше чем с GUID'ом.
...
Сейчас пока думаю, что самые оптимальные индексы будут такие:
Кластерный по dtMeasure, DataID
Некластерный по dtMeasure, TVID, TypeDataID и условие по DeleteStateCode=0
...
Набор dtMeasure, TVID, TypeDataID - не уникален

Просто сделать кластерный уникальный индекс по dtMeasure, TVID, TypeDataID, DeleteStateCode, DataID

Будет и правильное секционирование, и правильный индекс для выборки данных.

Но, конечно, вы ещё подумайте; зная бизнес-смысл этих данных, их использование, может быть, найдёте лучшее решение.
11 ноя 15, 16:39    [18401289]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить