SQL Server Columnstore Performance Tuning Eric N Hanson

SQL Server Columnstore Performance Tuning Eric N Hanson Principal Program Manager Microsoft Corporation

demo Outer Join Performance Limit

Batch object bitmap of qualifying rows Column vectors 8

column_id segment_id min_data_id max_data_id 1 1 20120101 20120131 1 2 20120115 20120215 1 3 20120201 20120228 select Date, count(*) from dbo. Purchase where Date >= 20120201 group by Date

Physical DB Design, Loading, and Index Management

Maximizing the Benefits of Segment Elimination

partition 1 Date = 20120301 segment 1 min(Date) = 20120301, max(Date) = 20120301 segment 2 “ segment 3 “ segment 4 “ segment 5 “ partition 2 Date = 20120302 segment 1 min(Date) = 20120302, max(Date) = 20120302 segment 2 “ segment 3 “ segment 4 “ segment 5 “

select f. region_id, avg(f. duration) from fact_CDR f where f. region_id = 1 and f. date_id between 20120101 and 20120131 group by f. region_id

Additional Tuning Considerations

Date License. Num Measure 20120301 XYZ 123 100 20120302 ABC 777 200 Date License. Id Measure 20120301 1 100 20120302 2 200 License. Id License. Num 1 XYZ 123 2 ABC 777

http: //social. technet. microsoft. com/wiki/contents/articles/sqlserver-columnstore-performance-tuning. aspx http: //social. technet. microsoft. com/wiki/contents/articles/sqlserver-columnstore-index-faq. aspx Tech. Ed 2011 session DBI 312

mva

Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //northamerica. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn

Complete an evaluation on Comm. Net and enter to win!

to evaluate this session now on