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

Откуда:
Сообщений: 88
Добрый вечер. У меня есть здоровая таблица - без clumnstore индекса занимает примерно 800Гб, с ним - примерно 80. Т.е я его использую как архиватор, не знаю правильно ли или нет, но использую. И еще по этой таблице создано два дополнительных некластеризованых индекса, собственно по которым все запросы делают условия поиска.
Так вот появилась задача добавления данных в эту таблицу. Естественно напрямую добавлять даже 2Гб данных крайне медленно. Потому думал использовать секционирование. Делаю так:
1. Создал подобную "первой" таблицу.
2. сделал секции по полю дата, по нужным мне периодам
3. сделал для новой пустой таблице два некластеризованых индекса и columnstore индекс
4. создал код для формирования временной таблицы из секционной
5. у старой "большой" удалил 2 некластеризованых индекса и пересоздал их кодом который получился для них же из п.4 (почему-то с уже созданными входящее переключение не получалось сделать) (а columnstore оставил ибо нет места его удалять, таблица же вырастет)
6. сделал входящее переключение в секцию старой таблицы с новыми индексами
Так вот, после эти всех сумбурных манипуляций запросы перестали использовать самые нужные 2 некластеризованые индексы! А стали делать фул скан colunm store индекса!!!? почему? и как оптимизатор заставить пользовать нужные индексы?
Или я все делаю в корне не верно? Подскажите пожалуйста тогда как?
ЗЫ. делал подобные манипуляции на таблице без columnstore индекса - все работает...
9 авг 17, 23:46    [20712264]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Может вместо columnstore индексов использовать page сжатие?...
9 авг 17, 23:47    [20712265]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
aleksrov
Member

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

Лютый бред использовать индексы для сжатия. Причин много, к примеру выполните запрос
set statistics io on
select column1
from table
where column1 = value, где column1 это столбец который есть в nonclustered index, а потом добавьте column2 чтобы необходимо было сделать key lookup с columnstore индексу и увидите разницу, большую.
А еще вот, тоже для размышления http://www.nikoport.com/2015/04/04/clustered-columnstore-indexes-part-50-columnstore-io/

Columnstore только для хранилищ данных, для oltp не подходит никак. Ну или если нагрузка смешная с уклоном в чтение большого кол-ва данных. Используйте сжатие на уровне страниц, только это также влияет на поизводительность, поэтому прежде чем такое мутить протестируйте.
10 авг 17, 09:18    [20712569]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
o-o
Guest
aleksrov
Лютый бред использовать индексы для сжатия

почему бред, columnstore сжимает куда лучше, чем page compression.
вот если сейчас он разожмет и отребилдит с page compression,
то уже больше не влезет в свои 80Gb.

и потом, раз у него таблица была ридонли,
по ней явно не OLTP-запросы.
а DWH-запросы, как правило, выгребают много,
и COLUMNSTORE может оказаться очень кстати
10 авг 17, 09:24    [20712583]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
aleksrov
Member

Откуда:
Сообщений: 948
o-o
aleksrov
Лютый бред использовать индексы для сжатия

почему бред, columnstore сжимает куда лучше, чем page compression.
вот если сейчас он разожмет и отребилдит с page compression,
то уже больше не влезет в свои 80Gb.

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


Я не увидел что она read-only, подумал что обычная рабочая таблица. Но автор пишет "И еще по этой таблице создано два дополнительных некластеризованых индекса, собственно по которым все запросы делают условия поиска", т.е. у него точечные запросы, а если это так то noncl поверх column не лучший вариант.
К примеру я выполнил на своей таблице где есть CCI и один NonCl зпрос который выбирает одку строку, план был соответсвенно seek по NCI и key lookup по CCI, кол-во лог чтение 3, кол-в лог чтений лобов 680, lob read ahead 470 и ссответсвенно чтение 1 сегмента.

Если это DWH или Read-only то да, для этого эти индексы и созданы, но у автора похоже не тот случай.
10 авг 17, 09:47    [20712658]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
o-o
Guest
aleksrov
Я не увидел что она read-only, подумал что обычная рабочая таблица.

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

aleksrov
Но автор пишет "И еще по этой таблице создано два дополнительных некластеризованых индекса, собственно по которым все запросы делают условия поиска"

на самом деле мы не знаем, что делают его запросы.
может, индексы просто покрывающие, а условия там типа where year = 2006
и идет все равно скан некластерного, хотя и с предикатом поиска.
и вот это он может тоже назвать "по которым все запросы делают условия поиска"
10 авг 17, 10:09    [20712743]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
aleksrov
Member

Откуда:
Сообщений: 948
o-o
aleksrov
Я не увидел что она read-only, подумал что обычная рабочая таблица.

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

aleksrov
Но автор пишет "И еще по этой таблице создано два дополнительных некластеризованых индекса, собственно по которым все запросы делают условия поиска"

на самом деле мы не знаем, что делают его запросы.
может, индексы просто покрывающие, а условия там типа where year = 2006
и идет все равно скан некластерного, хотя и с предикатом поиска.
и вот это он может тоже назвать "по которым все запросы делают условия поиска"


Вобщем согласен, пусть автор более подробно опишет свою проблему и тогда уже будет видно.
10 авг 17, 10:26    [20712796]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
aleksrov
o-o
пропущено...

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

пропущено...

на самом деле мы не знаем, что делают его запросы.
может, индексы просто покрывающие, а условия там типа where year = 2006
и идет все равно скан некластерного, хотя и с предикатом поиска.
и вот это он может тоже назвать "по которым все запросы делают условия поиска"


Вобщем согласен, пусть автор более подробно опишет свою проблему и тогда уже будет видно.

Попытаюсь описать подробнее.
Табилца до недавнего времени была рид-онли (загрузил один раз и работал с ней), но теперь появилась необходимость ежедневно _один раз в день_ добавлять в нее данные за предыдущий день и работать с актуальными данными.
Запросы примерно такие и используют индексы подобным образом:
select * from tbl where data = '2017-05-05' and fld2 = '123456789'
select * from tbl from tbl where data = '2017-05-05' and fld2 like '1234%'
select * from tbl where data > '2017-06-05' and fld2 like '32145%'
SELECT * FROM zzz LEFT JOIN tbl ON zzz.fld=tbl.fld2 WHERE tbl.data BETWEEN @StartDate AND @EndDate,
select * FROM xxx LEFT JOIN tbl ON xxx.fld=tbl.fld2 AND xxx.DATA=tbl.DATA WHERE (xxx.fld = '123456789') AND (xxx.DATA BETWEEN @DateStart AND @DateEnd)
10 авг 17, 13:47    [20713468]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
o-o
Guest
Badhabit
Табилца до недавнего времени была рид-онли (загрузил один раз и работал с ней), но теперь появилась необходимость ежедневно _один раз в день_ добавлять в нее данные за предыдущий день и работать с актуальными данными.
Запросы примерно такие и используют индексы подобным образом:
select * from tbl where data = '2017-05-05' and fld2 = '123456789'
select * from tbl from tbl where data = '2017-05-05' and fld2 like '1234%'
select * from tbl where data > '2017-06-05' and fld2 like '32145%'
SELECT * FROM zzz LEFT JOIN tbl ON zzz.fld=tbl.fld2 WHERE tbl.data BETWEEN @StartDate AND @EndDate,
select * FROM xxx LEFT JOIN tbl ON xxx.fld=tbl.fld2 AND xxx.DATA=tbl.DATA WHERE (xxx.fld = '123456789') AND (xxx.DATA BETWEEN @DateStart AND @DateEnd)

сегодня значит мой хрустальный шар все правильно показал :)
----
вопрос: а именно все поля выгребаете или все же определенные колонки?
10 авг 17, 13:52    [20713483]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
o-o
вопрос: а именно все поля выгребаете или все же определенные колонки?

сама таблица это примерно следющее:
CREATE TABLE tbl
(
 DATA DATE NULL
 ,fld VARCHAR(32) NULL
 ,f1 FLOAT NULL
 ,f2FLOAT NULL
 ,f3 FLOAT NULL
 ,f4 FLOAT NULL
 ,f5 FLOAT NULL
 ,f6 FLOAT NULL
 ,f7 FLOAT NULL
 ,f8 FLOAT NULL
 ,f9 FLOAT NULL
 ,f10 FLOAT NULL
 ,f11 FLOAT NULL
 ,f12 FLOAT NULL
 ,zzzz VARCHAR(128) NULL
 ,xxxx VARCHAR(8) NULL
 ,REC_ID BIGINT NULL
) 
Выбираю не все поля. Обычно нужны только f1, f3, f5, f7, f9, f11 по соответсвуюшим условиям data и fld
10 авг 17, 14:10    [20713550]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Badhabit, т.е. обычно
select data, fld2, f1, f3, f5, f7, f9, f11 from tbl ...
10 авг 17, 14:16    [20713575]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
0wl
Member

Откуда:
Сообщений: 53
Badhabit, а вы сравнивали планы с сolumnstore и с некластерными индексами?

В исследовательских целях нужный индекс можно гвоздями приколотить к запросу при помощи хинта INDEX . Дальше уже можно будет понять, чего не хватает некластерным индексам для того, чтобы оптимизатор их выбирал. Вот только на продакшне такие хинты оставлять не стоит, иначе через какое-то время ситуация поменяется и база начнет радовать неожиданными тормозами
10 авг 17, 14:37    [20713652]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
0wl
Badhabit, а вы сравнивали планы с сolumnstore и с некластерными индексами?

Я сравнил две версии одной и той же таблицы (по каждой сделан column store index и два некластеризованых по полям data и fld) просто одна секционирована, а вторая нет.
Запрос такой:
select data, fld2, indt, f1, f3, f5, f7, f9, f11 from tbl where data = '2017-01-17' and fld2 like '123456%'
В случае не секционированой таблицы план запроса показывает, что будет осуществлен поиск в каждом из некластеризованых индексов и потом hash match и вывод
А в случае c секционированой таблицы в плане написано просто просмотр кластеризованого column store index, т.е. запрос собирается перелопатить все мои 80ГБ...

0wl
В исследовательских целях нужный индекс можно гвоздями приколотить к запросу при помощи хинта INDEX . Дальше уже можно будет понять, чего не хватает некластерным индексам для того, чтобы оптимизатор их выбирал. Вот только на продакшне такие хинты оставлять не стоит, иначе через какое-то время ситуация поменяется и база начнет радовать неожиданными тормозами

А можно пример для тупых?))
10 авг 17, 15:07    [20713731]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Badhabit, и как я понимаю если "гвоздями приколотить к запросу при помощи хинта INDEX", то надо будет во все запросы вносить измененения?
10 авг 17, 15:29    [20713833]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
o-o
Guest
Badhabit
Badhabit, и как я понимаю если "гвоздями приколотить к запросу при помощи хинта INDEX", то надо будет во все запросы вносить измененения?

зачем везде прибивать,
надо в одном запросе хинт прописать и выполнить со включенной статистикой.
и тут же выполнить тот же самый запрос без всякого хинта.
и сравнить, где вышло больше чтений и был ли сервер прав, отказавшись от некластерных индексов
10 авг 17, 16:08    [20714030]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Сделал так.
from tbl WITH (TABLOCK, INDEX = ix_tbl_data, INDEX = ix_tbl_fld2) 

вроде как теперь ищет в индексах...
Но только вопрос можно ли сделать подобное один раз для таблицы чтобы не менять код запросов (в некоторых запросах нет возможность поменять код и они следовательно без это дописки будут тормозить...)
10 авг 17, 16:11    [20714039]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
o-o
Guest
Badhabit
в некоторых запросах нет возможность поменять код и они следовательно без это дописки будут тормозить...

подождите везде прописывать, дождитесь окончания выполнения.
может он не зря не хотел использовать эти индексы
10 авг 17, 16:13    [20714050]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Еще теоритический вопрос. Если план выполнения запроса пишет, что будет выполнен просмотр индекса columnstore это как я понимаю будет означать что он сделает в принципе фул-скан "сжатой" таблицы или нет?
21 авг 17, 16:54    [20739473]     Ответить | Цитировать Сообщить модератору
 Re: Columnstore index и секционирование  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 643
Блог
Badhabit,

Full Scan по columnstore может работать быстро, если включается segment elimination. Если данные по какой то колонке вставлялись в порядке возрастания, то фул скан выкинет сегменты для которых значение не попадает в диапазон min max для сегмента
26 авг 17, 10:24    [20750801]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить