SQL Updates Newsletter – March 2017

Recent Releases and Announcements

Issue Alert


Recent Blog Posts and Articles

  • Columnstore Index – How to Estimate Compression Savings
    • Customers can invoke sp_estimate_data_compression_savings  stored procedure to estimate the storage savings for ROW and PAGE compression
    • This stored procedure has not been extended to estimate storage savings from columnstore index. This is something we could consider for the future.
    • To estimate compression savings for columnstore index, we recommend the following steps: (1)Create a staging table with identical schema (2)Load 2 million rows into the staging table. [We] have chosen 2 million arbitrarily but it needs to be at least 1 million. (3) Use sp_spaceused to find the size of the table (4) Now create columnstore index on the table (5) Measure the storage using sp_spaceused. Compare the numbers in (3) and (5)
    • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-to-estimate-compression-savings/
  • Moving databases to new storage within an Availability Group
  • Data Simulator For Machine Learning
  • Getting more statistics information programmatically
  • Five reasons to run SQL Server 2016 on Windows Server 2016 — No. 1: Security
    • The security functionality in Windows Server 2016 includes the following:
    • Device Guard helps lock down what runs on the server so that you are better protected from unauthorized software running on the same server as your SQL Server application.
    • Credential Guard to protect SQL Server admin credentials from being stolen by Pass-the-Hash and Pass-the-Ticket attacks. Using an entirely new isolated Local Security Authority (LSA) process, which is not accessible to the rest of the operating system, Credential Guard’s virtualization-based security isolates credential information to prevent interception of password hashes or Kerberos tickets.
    • Control Flow Guard and Windows Defender protect against known and unknown vulnerabilities that malware can otherwise exploit. Control Flow tightly restricts what application code can be executed — especially indirect call instructions. Lightweight security checks identify the set of functions in the application that are valid targets for indirect calls. When an application runs, it verifies that these indirect call targets are valid. Windows Defender works hand-in-hand with Device Guard and Control Flow Guard to prevent malicious code of any kind from being installed on your servers.
    • https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/23/five-reasons-to-run-sql-server-2016-on-windows-server-2016-no-1-security/
  • Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost
    • SQL Server professionals know that database transactions can be gated by log write speed. If the log is faster, more database updates are possible. Windows Server 2016 helps solve this with Persistent Memory (aka Storage Class Memory).
    • Storage Spaces Direct in Windows Server 2016 allows use of industry-standard servers with local storage as a highly available, scalable alternative to expensive storage area networks (SANs) — with read speeds that can exceed 25 GB/second.
    • Windows Server 2016 has built-in capability to provide in-memory with 24 terabytes of available server memory. Plus, new CPU maximums have been increased by three times so that you can run up to 640 CPU cores.
    • https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/30/five-reasons-to-run-sql-server-2016-on-windows-server-2016-part-2/

Recent Training and Technical Guides


Monthly Script and Tool Tips

  • Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
  • Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads
    • https://support.microsoft.com/en-gb/kb/2964518
    • Note for SQL Server 2016: You no longer have to enable these trace flags in SQL Server 2016 because the auto-detection of the associated logic for trace flags is already incorporated into the product.
  • Troubleshooting SQL Server backup and restore operations



Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services