Category: ColumnStore Index

Recently Microsoft has made an announcement about the upcoming release of SQL Server 2016. There has been great enthusiasm about features that will be released with the new SQL Server 2016 and the enhancements that are made to the new SQL Server 2016. Below are list of some of the important and useful features introduced with SQL Server 2016 or enhancements made to existing features in SQL Server 2016.
ColumnStore Index – Below are the new enhancements made to the ColumnStore Index in SQL Server 2016. This feature was first introduced in SQL Server 2012, and few enhancements were made in SQL Server 2014 and some great new enhancements are made in SQL Server 2016 as mentioned below.
– Updatable NonClustered ColumnStore Index
– ColumnStore Index on In-Memory Tables
– Single-Thread Vs. Multi-Thread Batch Execution
– Snapshot and Read-Commited Snapshot Isolation
Specify Column in Table Creation

· AlwaysOn Availability Groups – There have been some very important and useful changes to the AlwaysON feature. This feature was introduced in SQL Server 2012 and some enhancements were made in SQL Server 2014 and now in SQL Server 2016 there have been further enhancements as mentioned below.
o DTC – beginning for SQL Server 2016 Community Technology Preview 2 (CTP2), cross-database transactions are supported for AlwaysOn Availability Groups running on Windows Server 2016 Technical Preview 2.
o Automatic Failover replicas have been increased from 2 replicas to 3 replicas.
o Group Managed Service Account –
o Load Balance of Read-Intent – Beginning with SQL Server 2016 Community Technology Preview 2 (CTP2), you can configure load-balancing across a set of read-only replicas.

· Live Query Statistics – Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another.
Reference – https://msdn.microsoft.com/en-us/library/dn831878%28v=sql.130%29.aspx

· Query Store – Query store is a new feature in that provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
Reference – https://msdn.microsoft.com/en-us/library/dn817826%28v=sql.130%29.aspx
· Temporal Tables – https://msdn.microsoft.com/en-us/library/dn935015%28v=sql.130%29.aspx

With codeplex shutting down, we have moved SQL Nexus to github with a new release (6.0). Now both Pssdiag/SQLDiag manager and SQL Nexus are on github. Where to get it As you navigate to SQL Nexus, you can download code and released binary files. If you choose to download binary files, you can go to...

SQL Server : large RAM and DB Checkpointing Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory and when applicable, how to best address them. SQL Server 2016 improves...

Recently we got an inquiry from a customer who received the following message in errorlog and wanted to know why. [INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1. This is my first time to see this error. As usual, I relied...

Recently I assisted on a customer issue where customer wasn’t able to alter a memory optimized table with the following error Msg 41317, Level 16, State 3, Procedure ddl, Line 4 [Batch Start Line 35]A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases...

In a previous blog, I talked about memory optimized table consumes memory until end of the batch. In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications. By default memory optimized table variable behaves the same way as...

I worked on an interesting issue today where a user couldn’t restore a backup. Here is what this customer did: backed up a database from an on-premises server (2008 R2) copied the file to an Azure VM tried to restore the backup on the Azure VM (2008 R2 with exact same build#) But he got...

Recently, I looked an In-Memory OLTP issue with Principal Software Engineer Bob Dorr who is still my office neighbor. After restoring a database that had just one memory optimized table, we dropped the table. Even without any memory optimized tables,number of checkpoint files keep going up every time we issue a checkpoint. For a while,...

In this blog Added per-operator level performance stats for Query Processing, Senior PM in QP talks about extending operator level performance stats. They include stats related to reads, CPU and elapse time. These are very helpful to track down query performance issues. We worked on recent case where we put ActualElapsedms in a good...

In blog “Importance of choosing correct bucket count of hash indexes on a memory optimized table”, I talked about encountering performance issues with incorrect sized bucket count. I was actually investigating an out of memory issues with the following error. Msg 701, Level 17, State 103, Line 11There is insufficient system memory in resource pool...

I was working with a customer to troubleshoot memory optimized table issues. In this scenario, our customer uses a memory optimized table variable. He put 1 million rows of data into the table variable and then process it. Based on what he said, I tried to come up with a repro to see if...