Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ColumnStore индекс и исключение сегментов  [new]
РаненыйЗаяц
Guest
Здравствуйте,
Где можно почитать про исключение сегментов при сканировании колоночного индекса?
13 дек 15, 16:14    [18554054]     Ответить | Цитировать Сообщить модератору
 Re: ColumnStore индекс и исключение сегментов  [new]
ОтветилУшел
Guest
РаненыйЗаяц,

Например здесь http://social.technet.microsoft.com/wiki/contents/articles/4995.sql-server-columnstore-performance-tuning.aspx#Maximizing_the_Benefits_of_Segment_Elimination
13 дек 15, 16:26    [18554074]     Ответить | Цитировать Сообщить модератору
 Re: ColumnStore индекс и исключение сегментов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
ОтветилУшел,

Чем это отличается от партиций? И чем круче?
14 дек 15, 13:58    [18557320]     Ответить | Цитировать Сообщить модератору
 Re: ColumnStore индекс и исключение сегментов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
a_voronin,

Да в общем-то, это просто разные механизмы. Но если все-таки сравнить, то вот, например:
http://rusanu.com/2012/05/29/inside-the-sql-server-2012-columnstore-indexes/
Segment elimination thus works much like partition elimination works in classic row stores (BTrees and Heaps), but the columnstore segment elimination is actually better:

  • Columnstore segments min and max values are stored in metadata for every column. Partition elimination works only on predicates that reference the partitioning column, but segment elimination works on any predicates that reference any column.

  • Columnstore segments min and max values are from the actual data in the segment. Partition elimination works not on actual data values but on partition definition boundaries, which may be much larger than the actual values present in the partition. Thus segment elimination kicks in more often.

  • Segments are much smaller than partitions, so simple probabilities tell us that more small segments will be eliminated where a partition would had to scan the entire partition, even when much of the data does not qualify.
  • 14 дек 15, 14:17    [18557470]     Ответить | Цитировать Сообщить модератору
     Re: ColumnStore индекс и исключение сегментов  [new]
    a_voronin
    Member

    Откуда: Москва
    Сообщений: 4902
    Кто-то может на данном примере продемонстрировать эффективность этого исключения сегментов ?

    USE tempdb
    GO 
    
    DROP TABLE [dbo].[T]
    GO 
    
    CREATE TABLE [dbo].[T](
    	[X] [int] NOT NULL,
    	[Y] [int] NOT NULL,
    	[Z] [int] NOT NULL, 
    	[W] [int] NOT NULL, 
    )
    GO
    
    CREATE CLUSTERED COLUMNSTORE INDEX ICS ON [dbo].[T]
    GO
    
    WITH A AS 
    (
    	SELECT 0 AS N UNION ALL SELECT 1
    ),
    B AS 
    (
    	SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM A A1, A A2, A A3, A A4, A A5, A A6, A A7, A A8
    ),
    C AS 
    (
    	SELECT TOP 5000000
    		N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM B B1, B B2, B B3
    )
    INSERT INTO T
    SELECT N / 10000, N % 1001, N % 101, N
    FROM C
    GO 
    
    SELECT X, COUNT(*), COUNT(DISTINCT Y), SUM(CAST(W AS BIGINT)) FROM [dbo].[T]
    WHERE Z = 37
    GROUP BY X
    ORDER BY X
    
    14 дек 15, 15:48    [18558137]     Ответить | Цитировать Сообщить модератору
     Re: ColumnStore индекс и исключение сегментов  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    a_voronin,

    В принципе, можно привести и на вашем примере. Но с небольшой модификацией условия.
    +
    USE tempdb
    GO 
    
    DROP TABLE [dbo].[T]
    GO 
    
    CREATE TABLE [dbo].[T](
    	[X] [int] NOT NULL,
    	[Y] [int] NOT NULL,
    	[Z] [int] NOT NULL, 
    	[W] [int] NOT NULL, 
    )
    GO
    go
    CREATE CLUSTERED COLUMNSTORE INDEX ICS ON [dbo].[T];
    GO
    
    WITH A AS 
    (
    	SELECT 0 AS N UNION ALL SELECT 1
    ),
    B AS 
    (
    	SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM A A1, A A2, A A3, A A4, A A5, A A6, A A7, A A8
    ),
    C AS 
    (
    	SELECT TOP 5000000
    		N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM B B1, B B2, B B3
    )
    INSERT INTO T
    SELECT N / 10000, N % 1001, N % 101, N
    FROM C
    GO 
    dbcc traceon(3605, 646, -1) 
    go
    dbcc errorlog
    go
    set statistics time, io, xml on
    SELECT X, COUNT(*), COUNT(DISTINCT Y), SUM(CAST(W AS BIGINT)) FROM [dbo].[T]
    WHERE W = 37 -- <-- not Z
    GROUP BY X
    ORDER BY X
    set statistics time, io, xml off
    go
    exec sp_readerrorlog;
    go
    dbcc traceoff(3605, 646, -1) 
    go
    --select * from sys.column_store_segments
    

    Результат:
    Xact (xactid=330881952) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=3)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=263772608) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    Xact (xactid=142007696) skipped row group (dbid=2 rowsetid=2233785416407187456 rowgroupid=2)
    ...


    Дело в том, что когда строится колоночный индекс, сервер выбирает изначальный порядок строк для сегментирования согласно внутреннему алгоритму, который учитывает частоту появления каждого значения в столбце для наилучшего сжатия. Можно косвенно повлиять на это построив предварительно обычный кластерный индекс, но это не всегда так. В вашем примере значение 37, встречается везде в таблице в столбце Z, видимо, это не лучший кандидат на сортировку.

    Если вы выполните свой пример и посмотрите на метаданные (комментированная строка): то увидите, что в метаданных сегментов минимальное значение колонки Z - 1, максимальное 100. 37 принадлежит этому интервалу. Так что сегменты не могут быть исключены. Сервер ведь не знает, по какой колонке вы будете делать запросы, так что исходит из своего внутреннего алгоритма для большинства случаев.

    Если мы поменяем колонку на W, более разнообразную в значениях - все ок. Исключение сегментов можете наблюдать в результатах в error_log, либо расширенного события если включить трейс. Механизм не совершенен. Можете запостить просьбу на коннект о том, чтобы указывать колонку сегментирования при построении колоночного индекса.
    14 дек 15, 18:36    [18559017]     Ответить | Цитировать Сообщить модератору
     Re: ColumnStore индекс и исключение сегментов  [new]
    a_voronin
    Member

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

    Хорошо, но есть партиционирование. В том числе никто не мешает партиционировать таблицу с колумстором. Что в общем-то можно считать обходным маневром для создания дополнительного "индекса" при колумсторе. так вот, даёт ли это "исключение сегментов" выигрыш против партиционирования?
    15 дек 15, 15:03    [18563123]     Ответить | Цитировать Сообщить модератору
     Re: ColumnStore индекс и исключение сегментов  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    a_voronin,

    А что лучше временная таблица или табличная переменная? Ответ как всегда - это зависит...

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

    Кроме того, ничего не мешает серверу использовать и тот и другой механизм. Партицируйте вон в вашем эксперименте по Z и добавьте "WHERE W = 37 and Z = 10".

    Получилось:
    Actual Partitions Count = 1
    Seek Keys[1]: Prefix: PtnId1001 = Scalar Operator((2))
    

    И вместе с тем:
    Xact (xactid=22338480) skipped row group (dbid=2 rowsetid=6052837907831128064 rowgroupid=0)
    Xact (xactid=22338480) skipped row group (dbid=2 rowsetid=6052837907831128064 rowgroupid=0)
    


    +
    USE tempdb
    GO 
    drop table [dbo].[t];
    drop partition scheme ps;
    drop partition function pf;
    go
    create partition function pf (int) as range left for values (1, 50, 80);
    go
    create partition scheme ps as partition pf all to ([primary]);
    go
    
    GO 
    
    CREATE TABLE [dbo].[T](
    	[X] [int] NOT NULL,
    	[Y] [int] NOT NULL,
    	[Z] [int] NOT NULL, 
    	[W] [int] NOT NULL, 
    ) 
    on ps(Z)
    GO
    go
    CREATE CLUSTERED COLUMNSTORE INDEX ICS ON [dbo].[T];
    GO
    
    WITH A AS 
    (
    	SELECT 0 AS N UNION ALL SELECT 1
    ),
    B AS 
    (
    	SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM A A1, A A2, A A3, A A4, A A5, A A6, A A7, A A8
    ),
    C AS 
    (
    	SELECT TOP 5000000
    		N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    	FROM B B1, B B2, B B3
    )
    INSERT INTO T
    SELECT N / 10000, N % 1001, N % 101, N
    FROM C
    GO 
    
    dbcc traceon(3605, 646, -1) 
    go
    dbcc errorlog
    go
    set statistics time, io, xml on
    SELECT X, COUNT(*), COUNT(DISTINCT Y), SUM(CAST(W AS BIGINT)) FROM [dbo].[T]
    WHERE W = 37 and Z = 10
    GROUP BY X
    ORDER BY X
    set statistics time, io, xml off
    go
    exec sp_readerrorlog;
    go
    dbcc traceoff(3605, 646, -1) 
    go
    select * from sys.partitions where object_id('[T]') = object_id
    
    15 дек 15, 15:34    [18563372]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить