SQL Updates Newsletter – November 2016
Recent Releases and Announcements
- Microsoft Security Bulletin MS16-136 - Important Security Update for SQL Server (3199641)
- SQL Server 2016 Service Pack 1 generally available
- https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/
- https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/sql-server-2016-service-pack-1-generally-available/
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/16/whats-new-in-sql-server-2016-sp1-for-reporting-services/
- Cumulative Update #3 for SQL Server 2016 RTM
- Cumulative Update #6 for SQL Server 2012 SP3
- Cumulative Update #15 for SQL Server 2012 SP2
- Announcing SQL Server on Linux public preview
- Simplifying our SSRS SharePoint integration story
- Starting with SQL Server v.Next, there’ll be only one installation mode for Reporting Services: “Native” mode
- With the Technical Preview of Power BI reports in Reporting Services, you can view and interact with Power BI reports in your web browser, and in time, we aim to support web-based viewing of Excel workbooks in Native mode as well.
- We’ll enable you to integrate Reporting Services Native mode with SharePoint
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/17/simplifying-our-sharepoint-integration-story/
- In-Memory OLTP in Standard and Express editions, with SQL Server 2016 SP1
- In-Memory OLTP is now available in SQL Server Standard Edition and Express Edition, as long as you have SQL Server 2016 SP1.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/in-memory-oltp-in-standard-and-express-editions-with-sql-server-2016-sp1/
- Microsoft R Server for HDInsight is now generally available
- Open sourcing the Microsoft JDBC Driver
- We are excited to announce the open sourcing of the Microsoft JDBC Driver for SQL Server
- https://blogs.msdn.microsoft.com/jdbcteam/2016/11/17/open-source-jdbc-maven/
- Microsoft releases the latest update to Analytics Platform System
- Information about enabled trace flags is added to the showplan XML in SQL Server 2014 SP2 and SQL Server 2016
- The new TraceFlags element is added to the QueryPlan element in the showplan XML
- This element lists all trace flags that are enabled at the instance or session level when the query plan is compiled, and for actual execution showplans, a specific query is executed.
- https://support.microsoft.com/en-us/kb/3170115
- SQL Server 2016 SP1, has made available a new class of query hints via the USE HINT statement option
- Using trace flags in a query involves QUERYTRACEON and requires SA permissions.
- This first release of USE HINT options addresses most common scenarios for QUERYTRACEON usage, but going forward, any new USE HINT hints and database-level settings may not have a respective Trace Flag counterpart.
- USE HINT options are documented query hints that add clarity and ease of use + do not require elevated privileges
- Example: SELECT * FROM FactInternetSales fis INNER JOIN DimProduct dp ON fis.ProductKey = dp.ProductKey WHERE CurrencyKey = 98 AND SalesTerritoryKey = 10 OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION' ))
- https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-use-hint-query-hints/
- SQL Nexus 5.5.0.1 has been released
- Released: Public Preview for System Center Management Packs for SQL Server
Recent Whitepapers/E-books/Training/Tutorials
- Overview Video of Azure Data Lake (Now Generally Available!)
- Time Travelling with Temporal Tables
- Behind the Scenes: Predicting the Early Onset of Brain Disease with BTT IoT Machine Learning
- More with SQL Server on Linux
- Free Online Workshop on Cortana Intelligence Suite: Register Now!
Monthly Script Tips
- Community contributions to the PowerShell scripts for Reporting Services
- SQL Server Columnstore Indexes Scripts Library
- Free scripts and tools for your Production SQL Server
- [Script Of Nov. 30] How to install SQL Server Public Preview on Linux by Bash
- [Script Of Nov. 29] How to find the data directory for a SQL Server instance
- [Script Of Nov. 18] How to switch UAC level via powershell scripts
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.
- 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: Description of the Update Cache folder in SQL Server
- 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
- 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)
- Unable to drop a user in a database
- https://blogs.msdn.microsoft.com/psssql/2016/11/15/unable-to-drop-a-user-in-a-database/
- Eventually, we discovered …some CLR triggers (sys.assembly_modules ) that used the particular user in “EXECUTE AS” clause.
- .Net 4.6.2. Framework client driver for Always Encrypted resulting in intermittent failures to decrypt individual rows
- https://blogs.msdn.microsoft.com/sqlreleaseservices/net-4-6-2-framework-client-driver-for-always-encrypted-resulting-in-intermittent-failures-to-decrypt-individual-rows/
- The SQL Product team has identified an issue with .Net 4.6.2 framework client driver for Always Encrypted enabled database on SQL Server 2016 and Azure SQL Database. The issue can lead to intermittent failure while trying to decrypt the records from the Always Encrypted enabled database.
- SQL Server 2016 SP1: Known issues
Recent Blog Posts and Articles
- How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1
- New sys.dm_exec_query_statistics_xml Dynamic Management Function is available in SQL Server 2016 SP1
- Source Control in SQL Server Management Studio (SSMS)
- If source code integration is an important aspect of how you use SSMS, you can enable the Visual Studio packages manually.
- https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/21/source-control-in-sql-server-management-studio-ssms/
- CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1
- SQL Server 2016 SP1 has a new T-SQL language statement – CREATE OR ALTER. This statement creates an object if it does not exist, or alters it if it is already there.
- Example: create or alter procedure procTest as begin …. end
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/
- Improving Analysis Services Performance and Scalability with SQL Server 2016 Service Pack 1
- With SQL Server 2016 Service Pack 1 (SP1), Analysis Services can deliver even more performance and scalability improvements through NUMA awareness and optimized memory allocation based on Intel Threading Building Blocks (Intel TBB),
- NUMA awareness is only enabled by default on systems with at least four NUMA nodes.
- https://blogs.msdn.microsoft.com/analysisservices/2016/11/17/improving-analysis-services-performance-and-scalability-with-sql-server-2016-service-pack-1/
- Columnstore Index: Which Columnstore Index is right for my workload?
- In-Memory OLTP: Is your database just in memory or actually optimized for memory?
- Deep Learning made easy in Azure
- Introducing Batch Mode Adaptive Memory Grant Feedback
Recommended KB’s To Review Regularly
- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
- https://support.microsoft.com/en-us/kb/2920151
- Note: November 2016 (KB3197874) security monthly quality rollup for Windows 8.1 and Windows Server 2012 R2
- 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