Performance impact of memory grants on data loads into Columnstore tables

Reviewed by: Dimitri Furman, Sanjay Mishra, Mike Weiner, Arvind Shyamsundar, Kun Cheng, Suresh Kandoth, John Hoang Background Some of the best practices when bulk inserting into a clustered Columnstore table are: Specifying a batch size close to 1048576 rows, or at least greater than 102400 rows, so that they land into compressed row groups directly…. 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 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

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

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

Scaling-out SQL Server disks and data files on Windows Azure Virtual Machines…a real-world example

Authors: Tim Wieman , Sanjay Mishra Technical Reviewers: Silvano Coriani, Cindy Gross, Chuck Heinzelman, Shep Sheppard, Mike Weiner, Nicholas Dritsas, Steve Howard, James Podgorski, Juergen Thomas, Ewan Fairweather This blog will demonstrate a real-world customer example of scaling out Microsoft SQL Server TempDB data files across multiple data disks in a Windows Azure Virtual Machine…. Read more

SQLCAT.Com – eBook Downloads

As of September 1, 2013 we decided to remove SQLCAT.COM site and use MSDN as the primary vehicle to post new SQL Server content. This was done to minimize reader confusion and to streamline content publication.  MSDN SQLCAT blogs already includes most SQLCAT.COM Content and will continue to be updated with more SQLCAT learnings.  You can also… Read more

SQLCAT.com consolidated index of Whitepapers

In an effort to migrate as much content as possible from SQLCAT.com to the MSDN SQL CAT site we have consolidated all of the Whitepapers published over the last few years.   While all of the content actually exists on Technet and MSDN download sites, SQLCAT was a great resource to start looking.  Hopefully this will… Read more

SQL Server In-Memory OLTP Internals Overview for CTP1

Are you interested in learning more about SQL Server 2014 In-Memory OLTP (project codenamed Hekaton)? Please review this Internals Overview with some excellent details around the new technology with SQL Server 2014: http://download.microsoft.com/download/F/5/0/F5096A71-3C31-4E9F-864E-A6D097A64805/SQL_Server_Hekaton_CTP1_White_Paper.pdf Enjoy and let us know if you have other questions/comments etc… on the content or In-Memory OLTP in general with SQL Server 2014!… Read more