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

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

SQLSweet16!, Episode 3: Parallel INSERT … SELECT

Sanjay Mishra, with Arvind Shyamsundar Reviewed By: Sunil Agarwal, Denzil Ribeiro, Mike Ruthruff, Mike Weiner Loading large amounts of data from one table to another is a common task in many applications. Over the years, there have been several techniques to improve the performance of the data loading operations. SQL Server 2014 allowed parallelism for… Read more

SQLSweet16!, Episode 2: Availability Groups Automatic Seeding

Reviewed by: Denzil Ribeiro, Murshed Zaman, Mike Weiner, Kun Cheng, Luis Vargas, Girish Mittur V, Arvind Shyamsundar, Mike Ruthruff If you have been using Availability Groups (or Database Mirroring prior to that), you are probably used to backing up the primary and restoring (with norecovery) the secondary (first a full database backup, followed by transaction… Read more

SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases

 Sanjay Mishra, with contributions from Aasav Prakash Reviewed by: Denzil Ribeiro, Murshed Zaman, Arvind Shyamsundar, Mike Ruthruff Celebrating the release of SQL Server 2016, we are starting a new blog series on exciting new scenarios and features. Aptly named “Sweet 16” series, we will discuss sixteen new capabilities in SQL Server 2016 (one on each… Read more