Columnstore Index Performance: Column Elimination

Data in Columnstore index https://msdn.microsoft.com/en-us/library/gg492088.aspx  is organized as columns. This organization allows each column to be stored and accessed independently of other columns unlike rowstore where all columns in a row are stored together. Using columnstore, SQL Server can execute the query retrieving only the columns referenced in the query. For example, if a FACT table has 50 columns and the…

2

A walkthrough of Loan Classification using SQL Server 2016 R Services

Joseph Sirosh, Data Group Corporate Vice President, had shown during his keynote session how customers are able to achieve a scale up of 1 million predictions/sec using SQL Server 2016 R Services. We will get down to the nuts and bolts of how you can emulate a similar setup using Lending Club data using SQL…

1

Split update operation can result in higher number of replicated commands

In the recent past, we worked on an issue where a number of updates on a replicated article (part of transactional replication) were delivered to the subscriber as a series of DELETEs and INSERTs as opposed to UPDATEs. In this post, I will explain the scenarios under which this situation can occur and what the…

3

SQL Server 2016 SP1: Know your limits

With the recent announcement of SQL Server 2016 SP1, we announced the consistent programmability experience for developers and ISVs, who can now maintain a single code base and build intelligent database applications which scale across all the editions of SQL Server. The processor, memory and database size limits does not change and remain as–in all…


Extended per-operator level performance stats for Query Processing

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016, such as CPU and I/O. Observe the example, where by executing the below query in AdventureWorks2014, on SQL Server 2016 SP1, we get a Spill Warning in…


Clustered Columnstore Index in Azure SQL Database

Columnstore index is the preferred technology to run analytics queries in Azure SQL Databases. We recently announced general availability of In-Memory technologies for all Premium databases. Similar to In-Memory OLTP, the columnstore index technology is not available in databases in the Standard and Basic pricing tiers today. Please refer to the blog https://azure.microsoft.com/en-us/blog/clustered-columnstore-index-in-azure-sql-database/ for the details…

0

Developers Choice: Query progress – anytime, anywhere

One of the most important actions when a performance issue hits, is to get precise understanding on the workload that’s executing and how resource usage is being driven. The actual execution plan is an invaluable tool for this purpose. Query completion is a prerequisite for the availability of an actual query plan, but with LQS…