SQL Server 2016 real-time operational analytics

SQL Server 2016 introduces a very cool new feature referred to as real-time operational analytics, which is the capacity to run each analytics (OLAP) and OLTP workloads on the same database tables at the equal time. This permits you to eliminate the want for ETL and a statistics warehouse in some cases (the use of one system for OLAP and OLTP in place of developing separate systems). This will assist to lessen complexity, cost, and facts latency.

Real-time operational analytics objectives the situation of a single facts supply together with a business enterprise resource planning (ERP) software on which you could run both the operational and the analytics workload. This does not update the want for a separate facts warehouse while you need to integrate facts from more than one source before running the analytics workload or whilst you require intense analytics performance the usage of pre-aggregated data such as cubes.

Real-time operational analytics uses an updatable nonclustered columnstore index (NCCI). The column store index maintains a copy of the facts, so the OLTP and OLAP workloads run towards separate copies of the facts. This minimizes the performance effect of each workloads going for walks at the same time. SQL Server routinely maintains index modifications so that OLTP changes are usually up-to-date for analytics. This makes it possible and practical to run analytics in real-time on up to date facts. This works for each disk-based totally and memory-optimized tables.

To accomplish this, all you need to do is to create an NCCI on one or extra tables which are wished for analytics. SQL Server query optimizer mechanically chooses NCCI for analytics queries at the same time as your OLTP workload maintains to run the usage of the same tree indexes as before. Learn more SQL Server DBA Online Training

The analytics question performance with real-time operational analytics will now not be as rapid as you can get with a dedicated records warehouse but the key gain is the ability to do analytics in real-time. Some groups may select to do real-time operational analytics whilst still preserving a dedicated statistics warehouse for severe analytics in addition to incorporating statistics from other assets.

Real-time Operational Analytics - How it Works

SQL Server 2012 brought Columnstore index (non-updateable, Non-Clustered Columnstore Index(NCCI)) which turned into enhanced in SQL Server 2014 to have updateable Clustered Columnstore Index(CCI). SQL Server 2016 takes it to a new degree and lets in an updateable nonclustered Columnstore index on a rowstore table.

The Columnstore index continues a replica of the information, so the operational and analytics workloads run against separate copies of the records within the identical database. This minimizes the performance impact of each workloads running at the same time. SQL Server automatically keeps index adjustments so operational modifications are usually up to date for analytics. With this design, it's miles possible and realistic to run analytics queries in real-time on updated data. This works for each conventional disk-primarily based and memory-optimized tables.

The figure underneath suggests one possible configuration the use of Analysis Services in Direct Query mode, but you could use any visualization tool or custom answer at your disposal to both connect without delay or connect via Analysis Services. This solution addresses the drawbacks cited above as the analysis takes place at once on the operational data.