SQL Updates Newsletter – April 2017


Recent Releases and Announcements

  • Reminder: Latest Improvements to the Simplified Servicing for Windows 7 and Windows 8.1
  • Server 2017 Community Technology Preview 2.0 now available
  • SQL 2017 – Introducing Batch Mode Adaptive Joins
    • The Query Processing team introduced the ability to sense a bad join choice in a plan and then dynamically switch to a better join strategy during execution.
    • The batch mode adaptive joins feature enables the choice of a hash join or nested loop join method to be deferred until after the first input has been scanned.  We introduce a new Adaptive Join operator.  This operator defines a threshold that will be used to decide when we will switch to a nested loop plan.
    • This first version supports batch mode execution; however, we are exploring row mode as a future possibility as well.
    • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/
  • SQL 2017 – Introducing Interleaved Execution for Multi-Statement Table Valued Functions
    • SQL Server has historically used a unidirectional “pipeline” for optimizing and executing queries. If cardinality estimates are incorrect, we will still end up using the original plan despite the poor original assumptions.
    • Interleaved execution … enables plans to adapt based on the revised estimates.
    • During optimization if we encounter a candidate for interleaved execution, which for this first version will be multi-statement table valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates and then resume optimization for downstream operations.
    • To determine if interleaved execution was used, examine the execution plan’s IsInterleavedExecuted plan attribute in the RuntimeInformation element.
    • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/
  • SQL Server 2017 to add Python support
  •  Cumulative Update #5 for SQL Server 2014 SP2
  • Cumulative Update #12 for SQL Server 2014 SP1
  • Cumulative Update #8 for SQL Server 2012 SP3
  • Power BI Mobile apps – April update
  • SQL Server Performance Baselining Reports Unleashed for Enterprise Monitoring
  • Power BI Desktop – April Update
  • Early technical preview of JDBC 6.1.6 for SQL Server released!
  • Database Experimentation Assistant 2.0 Technical Preview
  • Get to cloud faster with a new database migration service private preview
  • DSC Resource Kit Release April 2017
  • Delivering AI with data: the next generation of Microsoft’s data platform
    • 3 key innovation themes from the Microsoft DataAmp online event: The first is the close integration of AI functions into databases, data lakes, and the cloud to simplify the deployment of intelligent applications. The second is the use of AI within our services to enhance performance and data security. The third is flexibility—the flexibility for developers to compose multiple cloud services into various design patterns for AI, and the flexibility to leverage Windows, Linux, Python, R, Spark, Hadoop, and other open source tools in building such systems.
    • The Face API, Computer Vision API, and Content Moderator are now generally available in the Azure Portal
    • https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/delivering-ai-with-data-the-next-generation-of-microsofts-data-platform/
  • Announcing the General Availability (GA) Release of SSDT 17.0 (April 2017)
  • Released: Public Preview for SQL Server vNext Replication Management Pack (6.7.40.0)
  • Microsoft R Server 9.1 now available
  • Microsoft R Open 3.3.3 now available
  • R 3.4.0 now available
    • The most significant change in this release is that the JIT (‘Just In Time’) byte-code compiler is now enabled at level 3 by default. The inclusion of this just-in-time compiler means you’ll see similar performance benefits for your own R code without having to take any additional steps to compile it.
    • http://blog.revolutionanalytics.com/2017/04/r-340-now-available.html
  • Announcing general availability of Azure HDInsight 3.6
  • Announcing Azure Analysis Services general availability
  • Technical Preview: Database Experimentation Assistant
  • Microsoft Updates its Deep Learning Toolkit
  • SQL Server Command Line Tools for Mac: Preview now available
  • New features in the R checkpoint package, version 0.4.0
  • Latest Rev of Utilities for Microsoft Team Data Science Process (TDSP) Now Available
  • Shutting down CodePlex
    • We’ve exposed many of our key open source projects on GitHub… and we’re proud to partner closely with GitHub to promote open source.
    • As of this post, we’ve disabled the ability to create new CodePlex projects.  In October, we’ll set CodePlex to read-only, before shutting it down completely on December 15th, 2017.
    • https://blogs.msdn.microsoft.com/bharry/2017/03/31/shutting-down-codeplex/
  • Azure Management Libraries for .NET generally available now

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.
      • 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
  • Important: Default auto statistics update threshold change for SQL Server 2016
  • SQL Server Mysteries: The Case of the Not 100% RESTORE…
  • Considerations when tuning your queries with columnstore indexes on clone databases
    • Unlike traditional Btree indexes, when a columnstore index is created, there is no index statistics created on the columns of the columnstore indexes. However, there is an empty stats object created with the same name as columnstore index and an entry is added to sys.stats at the time of index creation. The stats object is populated on the fly when a query is executed against the columnstore index or when executing DBCC SHOW_STATISTICS against the columnstore index
    • The columnstore index statistics aren’t persisted in the storage. The index statistics is different from the auto created statistics on the individual columns of columnstore indexes which is generated on the fly and persisted in the statistics object.
    • https://blogs.msdn.microsoft.com/sql_server_team/considerations-when-tuning-your-queries-with-columnstore-indexes-on-clone-databases/
  • CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016
  • Try and try again: not always a good idea (at least not for SSMS)
    • Recent versions of SSMS have been compiled with .NET Framework 4.6.1. … [therefore] SSMS gets to leverage many of the newer capabilities in the .NET Framework
    • This includes connection resiliency logic. To improve the default experience for clients which connect to Azure SQL Database, SSMS will now retry 1 time after sleeping for 10 seconds. This can be perceived as a hang.
    • To change this behavior add the ConnectRetryCount parameter string into the ‘Additional Connection Parameters’ tab within the SSMS connection window.
    • https://blogs.msdn.microsoft.com/sqlcat/2017/04/06/try-and-try-again-not-always-a-good-idea-at-least-not-for-ssms/
  • Unable to restore a SQL 2008 backup within an Azure VM when using the Microsoft SQL Server Backup to Microsoft Azure Tool – Error Msg 3241

 

Recent Blog Posts and Articles

  • Programmatically find SQL Server TCP ports
  • Community driven Enhancements in SQL Server 2017
    • modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database…[allows you to] build [a] smart backup solution which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup.
    • A new DMF sys.dm_db_log_stats(database_id) will be released which exposes a new column log _since_last_log_backup_mb…[allows you to] build [an] intelligent T-log backup solution which takes backup based on the transactional activity on the database
    • Support loading tables into specified filegroups while using SELECT INTO. Example: SELECT *  INTO [dbo].[t2] ON FG2  from [dbo].[t1]
    • Setup will allow initial tempdb file size up to 256 GB per file with a warning to customers if the file size is set to value greater than 1GB and instant file initialization is not enabled.
    • The DMV sys.dm_tran_version_store_space_usage is introduced in SQL Server 2017 to track version store usage per database. This new DMV will be useful in monitoring tempdb for version store usage for dbas who can proactively plan tempdb sizing based on the version store usage requirement per database without any performance toll or overheads of running it on production servers.
    • A new DMV sys.dm_db_log_info  exposes transaction log VLF information
    • A new DMF sys.dm_db_log_stats(database_id) will be released in an upcoming CTP release of SQL Server 2017. It will expose aggregated transaction log information per database.
    • In SQL Server 2017, we have optimized the way we drain the on-going I/Os during backup resulting in dramatic gains in backup performance for small to medium databases. We have seen more than 100x improvement when taking system database backups on a 2TB machine.
    • Processor information is now exposed in sys.dm_os_sys_info. The new columns will allow you to programmatically query processor information for the servers hosting SQL Server instance – useful in managing large deployments of SQL Server. New columns exposed in sys.dm_os_sys_info DMV are socket_count, core_count, cores_per_socket.
    • Starting SQL 2016 SP1 CU2 and SQL Server 2017 CTP 2.0, DBCC CLONEDATABASE will flush runtime statistics while cloning to avoid missing query store runtime statistics in database clone.
    • In addition to this, DBCC CLONEDATABASE is further enhanced to support and clone fulltext indexes.
    • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-community-driven-enhancements-in-sql-server-2017/
  • Transparent Data Encryption (TDE) acceleration for SQL 2016 in Windows Azure
  • How SQL Server 2017 prevents plan regressions
  • What’s new in SQL Server 2017 CTP 2.0 for Analysis Services
  • Backup and restore your Azure Analysis Services models
    • One of the features that was added to Azure Analysis Services is the ability to back up your semantic models and all the data within them to a blob storage account.
    • Backups can be performed using the latest version of SQL Server Management Studio. It can also be automated through PowerShell or with the Analysis Services Tabular Object Model (TOM).
    • https://azure.microsoft.com/blog/backup-and-restore-your-azure-analysis-services-models/
  • Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 3: database uptime and reliability
    • With SQL Server 2016 and Windows Server 2016, Always On availability groups can include up to eight readable secondaries and can span multi-domain clusters. Nodes in a cluster no longer need to reside in the same domain—and indeed the nodes are no longer required to be in any domain at all. Instead, you can form a WSFC cluster with machines that are in workgroups.
    • Storage Replica is a new feature in Windows Server 2016 that offers new disaster recovery and preparedness capabilities…[it] lets you switch workloads to safe locations. Storage Replica enables synchronous and asynchronous replication of volumes between servers or clusters.
    • Windows Server 2016 Cluster OS Rolling Upgrade lets you upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 without stopping the Hyper-V or the Scale-Out File Server workloads.
    • https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/06/five-reasons-to-run-sql-server-2016-on-windows-server-2016-no-3-database-uptime-and-reliability/
  • Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 4: Reach insights faster by running analytics at the point of creation
  • Publish R functions as stored procedures with the sqlrutils package
  • Using xEvents to monitor Azure Analysis Services
  • How to find query plan choice regressions with SQL Server 2017 CTP2

Recent Training and Technical Guides

Monthly Script and Tool Tips

 

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

Comments (0)

Skip to main content