Enterprise Data Pipelines using Azure PaaS Services – An Introduction

Authors: Rangarajan Srirangam, Mandar Inamdar, John Hoang Reviewers: Murshed Zaman, Sanjay Mishra, Dimitri Furman, Mike Weiner, Kun Cheng Overview You might have worked with enterprise data pipelines using the SQL Server suite of products on-premises, or using virtual machines in the cloud. Now, you can build a similar enterprise data pipeline on Azure, comprised purely… Read more

Instant Log Initialization for SQL Server in Azure

Reviewed by: John Hoang, Denzil Ribeiro, Rajesh Setlem, Mike Weiner Introduction Instant File Initialization (IFI) is a well-known feature of SQL Server, providing significant performance improvement for operations that require an increase in the size of data files, such as database creation, restore, and file growth. Without IFI enabled, zeroes have to be written into… Read more

SQLSweet16!, Episode 10: “I can eat glass …”, but can I load it into a database?

Sanjay Mishra Reviewed By: Dimitri Furman, Murshed Zaman, Kun Cheng   If you have tried to use BULK INSERT or bcp utilities to load UTF-8 data into a table in SQL Server 2014 or in an earlier release (SQL Server 2008 or later), you have likely received the following error message: Msg 2775, Level 16,… Read more

SQLSweet16!, Episode 9: Scripting Always Encrypted operations

Authors: Arvind Shyamsundar, Sanjay Mishra, Subodh Patil (iCertis), Mukesh Singh (iCertis), Ravi Kumar (iCertis) Reviewers: Dimitri Furman, Benjin Dubishar, Raghav Kaushik, Jakub Szymaszek Always Encrypted is one of the highly acclaimed features in SQL Server 2016. The key value prop in Always Encrypted is that SQL Server itself cannot decrypt the data as it will… Read more

SQLSweet16!, Episode 8: How SQL Server 2016 Cumulative Update 2 (CU2) can improve performance of highly concurrent workloads

Sanjay Mishra, Arvind Shyamsundar Reviewed by: Dimitri Furman, Denzil Ribeiro, Mike Ruthruff, Mike Weiner, Ryan Stonecipher, Nitish Upreti, Panagiotis Antonopoulos, Mirek Sztajno Last year, in the SQLCAT lab we were working with an early adopter customer running pre-release SQL Server 2016 on Windows Server 2016 Tech Preview 4. The workload being used was the ASP.NET… Read more

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

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

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