CODEWITHSUNDEEP
sql-serverperformance-testingquery-performancecolumnstore
Sreepathi
Sreepathi

Reputation: 109

How to improve Columnstore Index Scan

Can any one tell which DB server properties will affect the Column Store Index batch execution ?

Upvotes: 2

Views: 3008

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294227

The question, as asked, is way to broad. There are many 'DB Server' properties that affect batch execution. An obvious one to look at is the database compatibility level, witch documents effect on batch execution:

In level 120:

  • Sorts on a table with Columnstore index are in Row mode

  • Windowing function aggregates operate in row mode such as LAG or LEAD

  • Queries on Columnstore tables with Multiple distinct clauses operated in Row mode

  • Queries running under MAXDOP 1 or with a serial plan executed in Row mode

In level 130

  • Sorts on a table with a Columnstore index are now in batch mode

  • Windowing aggregates now operate in batch mode such as LAG or LEAD

  • Queries on Columnstore tables with Multiple distinct clauses operate in Batch mode

  • Queries running under Maxdop1 or with a serial plan execute in Batch Mode

This is just the tip of the iceberg. Another example of 'DB server property' is traceflag 4199, which affects many query optimizer fixes and some are batch mode related. Available server memory affects batch mode. Available number of CPUs affect batch mode. Data size and cardinality estimations affect batch mode.

So, really, you have to narrow down the question to something specific.

As for the title question, 'how to improve columnstore scan', the biggest bang for the buck is segment elimination. Read Understanding Segment Elimination.

But, first and foremost, understand how to troubleshoot performance issues. Read How to analyse SQL Server performance. Root cause your problem. Is possible that you need more RAM, not a different index. Measure.

Upvotes: 1

Related Questions