October 2019 - Using Columnstore indexes in AzureDevOps by Konstantin Kosinsky (@kkosinsky)

Abstract:
The Analytics service in Azure DevOps Services provides all-over insights to your DevOps data: work item tracking, CI/CD pipelines, testing process, etc.

Built on top of columnstore indexes, the Analytics service has to deal with 100s of millions of records and daily growth in 10s of millions, only a few minutes delay from operation store, sub-second query performance for in-product experiences as well as 3rd party extensions, Power BI integration and ad-hoc queries.

Working on it, I've learned a lot of DOs and DON'Ts about columnstore indexes. The session covers how to:
* partition columnstore indexes in a multitenant environment
* maintain columnstore indexes in case of updates and/or purge to keep segment elimination possible
* evolve database schema without downtimes and maintenance windows
* design to benefit from predicate and aggregation pushdown
* deal with edge case like ad-hoc queries for raw data and paging
* choose between CCI and NCCI based on usage patterns

About Konstantin:
Konstantin is an industry expert who loves optimizing the performance at least in 10x. More data - more fun.

Konstantin is a Principal Software Engineer at Azure DevOps Services, where he works on moving the Analytics for software engineering to a completely new level.

Konstantin was born and had been working in Ukraine for a long time, passionately sharing his knowledge with Ukrainian and other European data communities. Before joining Microsoft in 2012, he was a Data Platform MVP for many years.