SQL Updates Newsletter – January 2017

Recent Releases and Announcements

• Support for SQL Server 2012 SP2 ended on 1/10/2017. Please upgrade to the latest service pack.
  • https://www.microsoft.com/lifecycle
• Cumulative Update #1 for SQL Server 2016 SP1
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-1-for-sql-server-2016-sp1/
• Cumulative Update #4 for SQL Server 2016 RTM
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-4-for-sql-server-2016-rtm/
• Cumulative Update #7 for SQL Server 2012 SP3
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-7-for-sql-server-2012-sp3/
• Cumulative Update #16 for SQL Server 2012 SP2
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/last-cumulative-update-16-for-sql-server-2012-sp2/
• SQL Server next version CTP 1.2 now available
  • Now in CTP 1.2, Microsoft is bringing the power of SQL Server to SUSE Linux Enterprise Server.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2017/01/20/sql-server-next-version-ctp-1-2-now-available/
• Announcing the SQL Server v.Next Early Adoption Program
  • The SQL Server Early Adoption Program (SQL EAP) is a Microsoft program started in January 2017 to help both customers and partners adopt the next version of SQL Server before general availability.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2017/01/20/announcing-the-sql-server-v-next-early-adoption-program/
• Released: Public Preview for SQL Server vNext Management Pack
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-vnext-management-pack/
• Pssdiag & Sqldiag Manager update
  • We’ve released a new version of Pssdiag & Sqldiag Manager which supports SQL Server 2016.
  • https://blogs.msdn.microsoft.com/psssql/2017/01/12/pssdiag-sqldiag-manager-update/
• Announcing Columnstore Indexes and Query Store support in Database Engine Tuning Advisor
  • The latest version of Microsoft SQL Server Database Engine Tuning Advisor (DTA) supports two new features: (a) Ability to recommend columnstore indexes in addition to rowstore indexes, and (b) Ability to automatically select a workload to tune from the Query Store.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2017/01/10/announcing-columnstore-indexes-and-query-store-support-in-database-engine-tuning-advisor/
• Data Migration Assistant (DMA) v3.0 is now available
  • DMA replaces all previous versions of SQL Server Upgrade Advisor and should be used for upgrades for most SQL Server versions.
  • https://blogs.msdn.microsoft.com/sql_server_team/data-migration-assistant-dma-v3-0-is-now-available/
• SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring
  • Ability to host SQL Server Performance Dashboard Reports on a central reporting server
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/
• Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available
  • Save Power BI reports to your SSRS Technical Preview web portal
  • https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/17/power-bi-reports-in-sql-server-reporting-services-january-2017-technical-preview-now-available/
  • Getting Started: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/18/getting-started-with-the-technical-preview-of-power-bi-reports-in-sql-server-reporting-services/
• New Updates to the Windows Data Science Virtual Machine (DSVM)
  • Microsoft R Server 9.0.1. A major update to the enterprise scalable R extension from Microsoft
  • R Studio Desktop now preinstalled
  • R Tools for Visual Studio is now updated to version 0.5, bringing in multi-window plotting and SQL tooling to run R code on SQL Server 2016.
  • Microsoft Cognitive Toolkit now on Version 2 Beta 6
  • Apache Drill, a SQL based query tool that can work with various data sources and formats, can make use of prepackaged and configured drivers to access various Azure data services such as Blobs, SQLDW/Azure SQL, HDI and Document DB.
  • JuliaPro is available to DSVM users and is now pre-installed and pre-configured on the VM.
  • Updated Deep Learning Toolkit for the Windows DSVM extension which helps you jump start deep learning on Azure GPU VMs, and without having to spend time installing GPU framework dependencies and drivers or configuring the various deep learning tools.
  • https://blogs.technet.microsoft.com/machinelearning/2017/01/27/new-year-new-updates-to-the-windows-data-science-virtual-machine/

Recent Whitepapers/E-books/Training/Tutorials

• SQL Server on Linux: The How - Part 2
  • https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-on-Linux-The-HOW-PART-2
• Whitepaper and Code Sample for Automated Partition Management
  • https://blogs.msdn.microsoft.com/analysisservices/2017/01/17/whitepaper-and-code-sample-for-automated-partition-management/
• [Video] Kicking off the year with CES, Windows Holographic VR headsets, Windows Developer Day and more...
  • https://channel9.msdn.com/Shows/This+Week+On+Channel+9/TWC9-Kicking-off-the-year-with-CES-Windows-Holographic-VR-headsets-Windows-Developer-Day-and-more
• [Video] MSSQLTIGER and January 2017 PASS Virtual Conferences
  • https://blogs.msdn.microsoft.com/sql_server_team/mssqltiger-and-january-2017-pass-virtual-conferences/

Monthly Script/Tool Tips

• [PowerShell] How to dynamically determine the correct Process\Working Set performance counter instance
  • Determine which “Process\Working Set” counter corresponds to a specific instance of SQL Server in an environment with  multiple instances of SQL Server running on the same machine.
  • Example: \Process(sqlservr#1)\Working Set: this may refer to a default or named instance of SQL
  • https://blogs.msdn.microsoft.com/sqlupdates/2017/01/19/how-to-dynamically-determine-the-correct-process-working-set-counter-instance/
• Getting Started with Always Encrypted using PowerShell
  • https://blogs.msdn.microsoft.com/sqlsecurity/2017/01/04/getting-started-with-always-encrypted-using-powershell/
• [Sample Script] How six lines of code + SQL Server can bring Deep Learning to ANY App
  • https://blogs.technet.microsoft.com/dataplatforminsider/2017/01/05/how-six-lines-of-code-sql-server-can-bring-deep-learning-to-any-app/
• Code Sample for Automated Partition Management
  • https://github.com/Microsoft/Analysis-Services/tree/master/AsPartitionProcessing
• ASTrace on the Analysis Services Git Repo
  • The ASTrace utility provides the ability to capture an Analysis Services trace and log it into a SQL Server table. The table can be queried later or read using SQL Server Profiler. The ASTrace utility runs as a Windows service that connects to Analysis Services, then creates a trace, and logs trace events into a SQL Server table using the SQL Server Profiler format.
  • https://blogs.msdn.microsoft.com/analysisservices/2017/01/17/astrace-on-the-analysis-services-git-repo/
• A Plethora of Microsoft Training Options on AI, Machine Learning & Data Science, including MOOCs
  • https://blogs.technet.microsoft.com/machinelearning/2017/01/25/a-plethora-of-microsoft-training-options-on-ai-machine-learning-data-science-including-moocs/

Issue Alert

• Critical : Do NOT delete files from the Windows Installer folder. C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server.
  • https://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/30/do-not-delete-files-from-the-windows-installer-folder.aspx
• Critical: Please be aware of a critical Microsoft Visual C++ 2013 runtime pre-requisite update that may be required on machines where SQL Server 2016 will be, or has been, installed.
  • https://blogs.msdn.microsoft.com/sqlcat/2016/07/28/installing-sql-server-2016-rtm-you-must-do-this/
  • If KB3164398 or KB3138367 are installed, then no further action is necessary. To check, run the following from a command prompt:
    • powershell get-hotfix KB3164398
    • powershell get-hotfix KB3138367
  • If the version of %SystemRoot%\system32\msvcr120.dll is 12.0.40649.5 or later, then no further action is necessary. To check, run the following from a command prompt:
    • powershell "get-item %systemroot%\system32\msvcr120.dll | select versioninfo | fl"
• Important: If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build.
  • In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.
  • https://support.microsoft.com/en-us/kb/3196535
• Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
  • https://support.microsoft.com/kb/239530
• Important: Default auto statistics update threshold change for SQL Server 2016
  • https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
  • SQL Server 2016:  Default is new threshold if database compatibility level is 130.  If database compatibility  is below 130, old threshold is used (unless you use trace flag 2371)
• SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption
  • If ALL user databases have TDE disabled and you restart SQL Server, tempdb is no longer encrypted […] instead of using sys.databases, use sys.dm_database_encryption_keys to tell which databases are truly enabled for encryption.
  • We will look at fixing the issue with sys.databases in the future
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/
• Unicode strings and implicit conversions
  • You might concatenate non-unicode string variables and get a result that is trimmed, even if  the sum of all string sizes is not over the variable data type limit.
  • The problem is due to prefixing a non-unicode string with the letter N (example DECLARE @var VARCHAR(8000) = N’…abc…’), the implicit conversion may result in a unicode string
  • https://blogs.msdn.microsoft.com/sql_server_team/unicode-strings-and-implicit-conversions/
• Solving SQL Connectivity issues: A new guided walkthrough just got published
  • We recently published a new document that provides a one stop shop for solving majority of connectivity issues that you may run into when working with SQL Server.
  • https://blogs.msdn.microsoft.com/psssql/2017/01/29/solving-sql-connectivity-issues-a-new-guided-walk-through-just-got-published/

Recent Blog Posts and Articles

• Why can’t I get a parallel plan when rebuilding my index?
  • The leading key of the index of non-partitioned tables needs to have multiple distinct values in order for parallelism to work.. Essentially, each parallel thread gets a set of value ranges and works on them and later ‘merges’ them together.  So if you just have one value for the leading key column, only one thread can work on it.  In other words, you won’t get a parallel plan.
  • https://blogs.msdn.microsoft.com/psssql/2017/01/09/why-cant-i-get-a-parallel-plan-when-rebuilding-my-index/
• Evaluating Shared Expressions in Tabular 1400 Models
  • With shared expressions, you can encapsulate complex or frequently used logic through parameters, functions, or queries. A classic example is a table with numerous partitions. Instead of duplicating a source query with minor modifications in the WHERE clause for each partition, the modern Get Data experience lets you define the query once as a shared expression and then use it in each partition.
  • https://blogs.msdn.microsoft.com/analysisservices/2016/12/30/evaluating-shared-expressions-in-tabular-1400-models/
• Columnstore Index Performance: SQL Server 2016 – Multiple Aggregates
  • SQL Server 2016 is able to process multiple aggregates (example COUNT + AVG in one query)  in batch mode. https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-multiple-aggregates/
• Columnstore Index Performance: Rowgroup Elimination
  • SQL Server provides information on the number of rowgroups eliminated as part of query execution.
  • You may not even need partitioning to filter the rows for the date range if rows are inserted in order (allowing SQL Server to pick the rowgroups that contain the rows for the requested date range using the Min/Max date ranges in the relevant column for each rowgroup)
  • column_store_segments: min_data_id and  max_data_id
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-rowgroup-elimination/
• Columnstore Index Performance: SQL Server 2016 – No Performance Cliff
  • In SQL Server 2014, Batch Mode execution mode was only supported when the query was executed with DOP > 1. This is now possible in SQL 2016.
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-no-performance-cliff/
• Columnstore Index Performance: Column Elimination
  • Data in Columnstore index  is organized as columns.
  • This organization allows each column to be stored and accessed independently of other columns unlike rowstore where all columns in a row are stored together. Using columnstore, SQL Server can execute the query retrieving only the columns referenced in the query.
  • If a fact table has 50 columns and the query only accesses 5 columns, only those columns would need to be fetched.
  • Since data is read compressed in SQL Server memory, you get the similar savings for SQL Server memory.
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-column-elimination/
• Columnstore Index Performance: SQL Server 2016 – String Predicate Pushdown
  • The string predicate push down leverages the ‘dictionaries’ that are created per column with each compressed rowgroups.
  • Dictionary entries store the full column value and each column segment contains the reference to the dictionary entry. If the same value is repeated multiple times, it is stored in the dictionary once but referenced multiple times. SQL Server 2016 utilizes dictionary entries to speed the string predicates.
  • Columnstore index in SQL Server 2016 allows string predicates to be pushed down to the SCAN node (eliminating the need for a FILTER node)  resulting in significant improvement in query performance.
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-string-predicate-pushdown/
• Columnstore Index Performance: SQL Server 2016 – Window Aggregates in BatchMode
  • SQL Server 2016 introduces BatchMode execution model for aggregates  computed over a set of rows defined by the OVER clause.
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-window-aggregates-in-batchmode/
• Columnstore Index Performance: SQL Server 2016 – Aggregate Pushdown
  • In SQL Server 2016, the aggregate operator itself is pushed to the SCAN node (i.e. closer to the source of the data).
  • Aggregate push down is only done for the rows in compressed rowgroup.
  • SCAN node now has a property to show the number of rows that were aggregated locally.
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-aggregate-pushdown/
• Making Service Broker Application Highly Available With AlwaysOn
  • https://blogs.msdn.microsoft.com/alwaysonpro/2017/01/26/making-service-broker-application-highly-available-with-alwayson/
• Columnstore Index: SQL Server 2016 – Improved DMV performance
  • In the latest servicing release for SQL Server 2016, we have modified the DMV dm_db_column_store_row_group_physical_stats to remove some internal inefficiencies which results into improved query performance and reduced memory grant requirement by the DMV
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-dmv-performance-sql-server-2016-sp1-cu1-improved-performance-of-sys-dm_db_column_store_row_group_physical_stats/

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