SQLSweet16!, Episode 7: Install Option for Instant File Initialization

Reviewed by: Dimitri Furman, Kun Cheng, Denzil Ribeiro Database Instant File Initialization helps improve performance of certain file operations. Prior to SQL Server 2016, enabling instant file operation has been cumbersome (editing the Local Security Policy to add the SQL Server service account to the Perform Volume Maintenance Tasks policy, followed by restarting SQL Server… Read more

Migrating data to Azure SQL Data Warehouse in practice

Authors: Rangarajan Srirangam, Mandar Inamdar Contributors and Reviewers: John Hoang, Sanjay Mishra, Alexei Khalyako, Sourabh Agarwal, Osamu Hirayama, Shiyang Qiu Overview: Migrate data to Azure SQL Data Warehouse Azure SQL Data Warehouse is an enterprise-class, distributed database, capable of processing massive volumes of relational and non-relational data. It can deploy, grow, shrink, and pause in seconds. As… Read more

SQLSweet16!, Episode 6: DBCC CHECKDB with MAXDOP

Reviewed By: Dimitri Furman DBCC CHECKDB is a common database maintenance task. It can take up significant amount of system resources, and can impact the performance of the production workload. There are some very good articles on the web on optimizing performance of DBCC CHECKDB and minimizing performance impact. SQL Server 2016 (and now backported… Read more

SQLSweet16!, Episode 5: TRUNCATE Selected Partitions

Reviewed By: Denzil Ribeiro, Kun Cheng Deleting all rows from a given partition is a very common operation on a partitioned table, especially in a sliding window scenario. In a sliding window scenario, when a new period starts, a new partition is created for the new data corresponding to this period, and the oldest partition… Read more

Using Table Valued Parameters with Always Encrypted in SQL Server 2016 and Azure SQL Database

Reviewed by Panagiotis Antonopoulos, Jakub Szymaszek, Raghav Kaushik Always Encrypted is one of the compelling features in SQL Server 2016 and in Azure SQL DB which provides a unique guarantee that data in the database cannot be viewed, accidentally or intentionally by users who do not have the ‘master key’ required to decrypt that data…. Read more

Changes in SQL Server 2016 Checkpoint Behavior

Reviewed by: Denzil Ribeiro, Mike Weiner, Arvind Shyamsundar, Sanjay Mishra, Murshed Zaman, Peter Byrne, Purvi Shah SQL Server 2016 introduces changes to the default behavior of checkpoint. In a recent customer engagement, we found the behavior change to result in higher disk (write) queues on SQL Server 2016 vs. the same workload on SQL Server… Read more

Installing SQL Server 2016 RTM? You must do this!

Arvind Shyamsundar, Lee Woods Reviewed by Jeff Papiez, Mike Weiner, Troy Moen, Suresh Kandoth It has been a while now since SQL Server 2016 has been generally available. We trust you are excited with the great capabilities that SQL Server 2016 brings to you, and have either already installed or will be installing it soon…. Read more

Real World Parallel INSERT…SELECT: What else you need to know!

Arvind Shyamsundar Reviewed by: Gjorgji Gjeorgjievski, Sunil Agarwal, Vassilis Papadimos, Denzil Ribeiro, Mike Weiner, Mike Ruthruff, Murshed Zaman, Joe Sack In a previous post we have introduced you to the parallel INSERT operator in SQL Server 2016. In general, the parallel insert functionality has proven to be a really useful tool for ETL / data… Read more

SQLSweet16!, Episode 4: SQL Server R Services makes you a smarter T-SQL Developer

Sanjay Mishra, Arvind Shaymsundar Reviewed By: Joe Sack SQL Server 2016 has several new features with SQL Server R Services being one of the most interesting ones. This feature brings data science closer to where most data lives – in the database! It also opens up a world of extensibility to pure database developers by… Read more

Transaction dependency limits with memory optimized tables – Error 41839

Reviewed by: Jos de Bruijn,Mike Weiner, Mike Ruthruff , Kun Cheng As we discussed in a prior blog on considerations around validation errors , part of transaction processing on memory optimized tables include transactions taking dependencies on other transactions during validation and commit processing. With SQL Server 2014 and SQL Server 2016, there is a… Read more