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

Откуда:
Сообщений: 26
Прошу помощи в настройке производительности. Сам в основном работал на ORACLE, там все вроде понятно. С SQL Server 2008 проблема. Документацию читал, вроде все делал, помогает не сильно.

Ситуация следующая:
В SQL2008 есть большая таблица фактов товарных остатков(пока 300 миллионов записей) с группой ключевых столбцов для связи со справочником(схема звезда).
Поля- ключи со следующей селективностью: Дата(1000 значений), филиал(200 значений), товар(200000 значений).
Таблица будет использоваться аналитическим приложением для создания ad-hoc выборок со справочниками в различных комбинациях. На данный момент выборка остатков в группировке по всем филиалам за 1 дату работает 30 секунд. Для подсчета данных за месяц требуется более 10 минут. Это для поставленной задачи ооочень долго.

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

Вопросы:
1) Как лучше построить индексы: Отдельно по каждому ключевому полю, одним составным индексом или сделать индексы и по всем полям+ составной индекс
2) Какой из индексов должен быть кластерным
3) Использовать ли компрессию таблицы и партиций
4) Каким образом необходимо произвести партиционирование в связке с построенными индексами? Есть ощущение, что партиционирование не дает выигрыша в производительности. Какие дополнительные параметры партиционирование необходимо установить, если это возможно?

Построение агрегатов или кубов можно не предлагать, в моем случае это невозможно.
6 июл 09, 17:36    [7382530]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
кидай сюда ddl таблиц, код выборки, его план + statistic io
а там уже посмотрим
6 июл 09, 18:01    [7382716]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
заодно и настройки сервера
"max degree of parallelism", "max worker threads"
6 июл 09, 18:11    [7382774]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
йцу1,

USE [DWH]
GO

CREATE TABLE [dbo].[fct_remains](
[date] [datetime] NOT NULL,
[good_id] [datetime2](6) NULL,
[fil_id] [int] NULL,
[quantity] [float] NULL,
[remains_Price_RUR] [money] NULL,
[remain_Sum_RUR] [money] NULL,
[remain_Cost_Sum_RUR] [money] NULL,

)

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

Интересует общий подход к индексированию и партиционированию в SQL Server для схемы звезда на подобных объемах.
6 июл 09, 18:22    [7382824]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
йцу1,

Данные настройки = 0
6 июл 09, 18:24    [7382833]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
приведи все-таки для примера несколько типичных выборок
Например, "выборка остатков в группировке по всем филиалам за 1 дату", "выборка подсчета данных за месяц"
6 июл 09, 18:37    [7382880]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
автор
По индексам пробовал разные комбинации: отдельно по каждому из ключевых полей

Неужто в Оракле такой подход к индексированию оправдан?


agrdm
2) Какой из индексов должен быть кластерным

Имхо - дата

agrdm
3) Использовать ли компрессию таблицы и партиций

Обязательно, причем стоит потестить оба варинта: PAGE и ROW
agrdm

4) Каким образом необходимо произвести партиционирование в связке с построенными индексами?

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

agrdm

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


Стоит выяснить по WaitStats за счет чего проседает производительность... Для этого настраивайте Data Collection и мониторьте сервер под нагрузкой
К примеру, у меня сейчас 1,7 ТБ варехаус котрый крутился на сервере с 8 ГБ озу и отличным DELL SAN имел в 95% случаях только одно бутылочное горлышко - процессоры.
6 июл 09, 19:56    [7383210]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
Александр Волок (def1983),


Неужто в Оракле такой подход к индексированию оправдан?

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

Стоит выяснить по WaitStats за счет чего проседает производительность... Для этого настраивайте Data Collection и мониторьте сервер под нагрузкой

А не подскажете, как это делается? Прошу прощения, вижу данные термины в первый раз.

Спасибо за ответы, буду пробовать!
8 июл 09, 17:06    [7392799]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
agrdm
Александр Волок (def1983),


Неужто в Оракле такой подход к индексированию оправдан?

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

Правильно ли я понял, что у вас на каждое поле существует индекс, который содержит только это поле?

agrdm

А не подскажете, как это делается? Прошу прощения, вижу данные термины в первый раз.

Спасибо за ответы, буду пробовать!

http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/
8 июл 09, 18:02    [7393174]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
agrdm
Александр Волок (def1983),


Неужто в Оракле такой подход к индексированию оправдан?

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


Правильно ли я понял, что у вас на каждое поле существует индекс, который содержит только это поле?


Да, верно, Плюс индекс по всей группе полей. Поскольку это ХД, место на диске не так важно, как скорость выполнения запросов. Есть какие-то ньюансы с этим связанные? Я смотрю статистику- каждый из индексов используется.
Один из моих вопросов как раз и касался принципа индексирования, какие из индексов целесообразно создавать? Может достаточно одного индекса, содержащего, например, дату и филиал?
Например, пользователь создает отчет - сумма по месяцу в разбивке по филиалам. Соответственно, берется 2 поля для фильтра - дата и филал. Если пользователь к выборке добавит фильтр по дате , как в этом случае будет работать оптимизатор? возьмет индекс, содержащий 3 поля(дата, филиал и продукт) или выберет, например, кластерный индекс по дате и из полученной выборки фуллсканом вытащит нужные филиалы и товары? Что в данном случае будет работать быстрее?

Мой предыдущий опыт говорит, что создание избыточных индексов оправдано с точки зрения скорости выборки. Может я заблуждаюсь?
9 июл 09, 14:33    [7396911]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

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

Если пользователь к выборке добавит фильтр по дате

прошу прощения, конечно , фильтр не по дате, а дополнительный фильтр по продукту
9 июл 09, 14:37    [7396957]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33364
Блог
agrdm, а почему кубы не хотите? задача все же классическая для них
9 июл 09, 15:02    [7397245]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
[date] [datetime] NOT NULL,
[good_id] [datetime2](6) NULL,
Шо за бред с датой ?? Очепятка ? :)

Вы храните нулевые остатки ? Если да, то зачем ?
0.3млрд это сильно. :)

Ради прикола укоротите дату до shortdate. Как никак в два раза короче.

ЗЫ: База запчастей штоле ?
9 июл 09, 15:22    [7397401]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
Критик,

Не буду вдаваться в подробности, но кубы для этой задачи не подойдут по нескольким причинам. Например, очень сложная структура товаров неуникальной привязкой к иерархиям свойст.
Во-вторых, не знаю как AS, но купленное ПО не справится с таким кубом(по факту выйдет, что справочник товаров 8 млн записей со свойствам. Сколько там будет ячеек? я максимум строил куб со 100 тыс номенклатурой по продуктовой рознице - с горем пополам).
Почему так- это особенность учетной системы:товар имеет неограниченное колическтво иерархических свойств, выходит, что товар(уникальных их всего 200 тыс привязан к куче иерархий своих свойств одновременно, т.е не уникален в справочнике. Итого выходит 8 млн уникальных привязок товаров к свойствам), много людей много думало и ничего не придумало. заказчика уломать на что-то другое тоже не получится, т.к. это повлечет за собой пересоздание ERP системы=))

Потом вопрос не столько в плоскости "как в принципе решить задачу", а в том, как порешать подобные структуры в SQL Server средствами самой СУБД.
10 июл 09, 14:34    [7402258]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

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

Дата такая, потому как это реальный id в учетной системе. Разряды так же несут смысловую нагрузку. Я понимаю, что можно сделать суррогатный ключ. Просто пока не вижу смысла, места сильно не сэкономишь, да оно и не надо.

Нулевые остатки храним. А как еще показывать в отчетности, что данный товар остаток= 0? Лефт джоин с четырьмя справочниками? или функция? Боюсь это непоправимо вдарит по производительности в отчетах. Потом надо считать показатель количество дней нулевых остатков товара и т.п.

База товаров супермаркетов и шмоток.
10 июл 09, 14:41    [7402307]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
KGP
Member

Откуда: Москва
Сообщений: 4557
agrdm
Критик,

уникальных их всего 200 тыс привязан к куче иерархий своих свойств одновременно, т.е не уникален в справочнике. Итого выходит 8 млн уникальных привязок товаров к свойствам




1. сколько уникальных [good_id] ?
2. каков % товаров с нулевыми остатками, неизменившимися с 2008г ?
3. каков % товаров с нулевыми остатками, неизменившимися с 2007г ?
10 июл 09, 15:08    [7402453]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
А что с железом? Сколько ядер, какие диски?
10 июл 09, 15:58    [7402823]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
Кластерный индекс скорее всего по дате, но уникальности маловато.
может стоит добавить поле fil_id, зависит от типичных выборок...

Секционировать надо по дате, возможно разделить по кварталам, так же зависит от выборок... Каждой секции свою файловую группу, в файловой группе несколько файлов (равно количеству ядер процессора).

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

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

А вот этого я что то не понял.
Александр Волок (def1983)
Гранулярность партиций стоить выбирать такую, чтобы среднестатистический запрос обращался сразу к нескольким партициям, в этом случае паралельное чтение партиций даст о себе знать.

Смысл секционирования как раз таки заключается в том, чтобы уменьшить количество секций, задействованных в запросе.
10 июл 09, 17:19    [7403318]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
йцу1
А вот этого я что то не понял.
Александр Волок (def1983)
Гранулярность партиций стоить выбирать такую, чтобы среднестатистический запрос обращался сразу к нескольким партициям, в этом случае паралельное чтение партиций даст о себе знать.

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


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


Возможно в мире оракла секционирование имеет роль похожу на роль кластерного индекса в SQL Server...
10 июл 09, 17:45    [7403420]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
Александр Волок (def1983)

Возможно в мире оракла секционирование имеет роль похожу на роль кластерного индекса в SQL Server...


в SQL Server разве не так?
10 июл 09, 18:34    [7403583]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
йцу1
в SQL Server разве не так?

В SQL Server для этого есть кластерные и/или покрывающие индексы.
Они служат для того чтобы ограничивать объем данных для обработки, необходимый для возврата результата.

Что касается секционирования, то хорошо описано в боле, зачем его придумали.
Секционирование делает большие таблицы и индексы более управляемыми, так как позволяет быстро и эффективно получать доступ к поднаборам данных и управлять ими, при этом сохраняя целостность всей коллекции. При использовании секционирования такие операции, как загрузка данных из системы OLTP в систему OLAP, занимают всего несколько секунд вместо минут и часов, затрачивавшихся на это в предыдущих версиях SQL Server. Операции обслуживания, выполняемые на поднаборах данных, также выполняются значительно эффективнее, так как нацелены только на те данные, которые действительно необходимы, а не на всю таблицу.


Что касается случаев прироста производительности при секционировании, то стоит прочесть:
Проектирование секций для повышения производительности запросов

Разбив таблицы на секции бессмысленно ожидать повышения производительности операций БД просто от того что секционирование было осуществлено. Особенно в олтп, особенно на системах где все файлы данных расположены на одном массиве
11 июл 09, 00:40    [7404538]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Интересная особенность:

В SQL Server все таблицы и индексы в базе данных считаются секционированными, даже если они состоят всего лишь из одного раздела. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов. Это означает, что логическая и физическая архитектура таблиц и индексов, включающая несколько секций, полностью отражает архитектуру таблиц и индексов, состоящих из одной секции.
11 июл 09, 01:07    [7404566]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
йцу1
Guest
вот статейка
11 июл 09, 12:44    [7404786]     Ответить | Цитировать Сообщить модератору
 Re: Партиционирование SQL2008  [new]
agrdm
Member

Откуда:
Сообщений: 26
Всем большое спасибо за советы и ссылки! Материал для изучения получен вполне=)

К сожалению, придется опускаться до уровня железа, файлов и тд. И потратить кучу времени на тестирование...
13 июл 09, 11:29    [7407377]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить