SQL Updates Newsletter - February 2018

Recent Releases and Announcements

• Gartner recognizes Microsoft as a leader in Analytics and BI platforms for 11 consecutive years
  • https://powerbi.microsoft.com/en-us/blog/gartner-recognizes-microsoft-as-a-leader-in-analytics-and-bi-platforms-for-11-consecutive-years/
• Cumulative Update #4 for SQL Server 2017 RTM
  • We have recently found an issue where if you are using Query Store feature with SQL Server 2017 CU2, and later upgrade to SQL Server 2017 CU3 (or higher when available), an attempt to use the stored showplan fails.
  • To assist DBAs in removing any of the affected plans collected while SQL Server 2017 CU2 was installed, we have created [a] T-SQL script to remove only the affected plans in Query Store.
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-4-for-sql-server-2017-rtm/
• New releases: Microsoft R Client 3.4.3, Microsoft ML Server 9.3
  • https://blog.revolutionanalytics.com/2018/02/mrclient343.html
• What’s new in SSMS 17.5: Data Discovery and Classification
  • We are excited to announce the release of SQL Server Management Studio (SSMS) 17.5
  • In addition to enhancements and bug fixes, SSMS 17.5 comes with several exciting new features such as Data Discovery and Classification is a new feature for discovering, classifying, labeling and reporting sensitive data in your databases.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2018/02/20/whats-new-in-ssms-17-5-data-discovery-and-classification/
• The February release of SQL Operations Studio is now available
  • SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux
  • https://blogs.technet.microsoft.com/dataplatforminsider/2018/02/15/the-february-release-of-sql-operations-studio-is-now-available/
• Announcing a new update for mssql-cli: Special Commands
  • mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform
  • https://blogs.technet.microsoft.com/dataplatforminsider/2018/02/21/announcing-a-new-update-for-mssql-cli-special-commands/
• Visual Studio Code is now shipping with Anaconda!
  • https://blogs.msdn.microsoft.com/pythonengineering/2018/02/15/visual-studio-code-is-now-shipping-with-anaconda/
• Introducing SQL Information Protection for Azure SQL Database and on-premises SQL Server!
  • We are delighted to announce the public preview of SQL Information Protection (Data Discovery & Classification), introducing advanced capabilities built into Azure SQL Database for discovering, classifying, labeling & protecting the sensitive data in your databases. Similar capabilities are also being introduced for on-premises SQL Server via SQL Server Management Studio.
  • https://blogs.msdn.microsoft.com/sqlsecurity/2018/02/20/introducing-sql-information-protection-for-azure-sql-database-and-on-premises-sql-server/
• Announcing the JDBC Driver 6.4 RTW!
  • https://blogs.msdn.microsoft.com/jdbcteam/2018/02/28/announcing-the-jdbc-driver-6-4-rtw/
• Microsoft’s focus on transforming healthcare: Intelligent health through AI and the cloud
  • In the lead-up to next week’s Healthcare Information and Management Systems Society (HIMSS) conference, we’re announcing a number of solutions, projects and AI accelerators that we believe will help make intelligent health possible: Microsoft Genomics, Microsoft Azure Security and Compliance Blueprint: HIPAA/HITRUST – Health Data & AI, AI Network for Healthcare, Microsoft 365 Huddle Solution Templates, Project Empower MD, Project InnerEye
  • https://blogs.microsoft.com/blog/2018/02/28/microsofts-focus-transforming-healthcare-intelligent-health-ai-cloud/
• DSC Resource Kit Release February 2018
  • https://blogs.msdn.microsoft.com/powershell/2018/02/07/dsc-resource-kit-release-february-2018/
• Public Preview: Azure SQL Database – Managed Instance Management Pack
  • We are happy to announce the public preview release of System Center Management Pack for Azure SQL Database – Managed Instance! This is a brand new MP to monitor the new Azure SQL Database – Managed Instance.
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/public-preview-azure-sql-database-managed-instance-management-pack/

Troubleshooting and Issue Alerts

• 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)
• Filtered Indexes and Forced Parameterization: Can’t we all just get along?
  • The presence of filtered indexes should make you cautious about enabling forced parameterization at the database level. The two features don't always play nicely, and the combination can cause query performance problems and/or 8622 errors.
  • https://blogs.msdn.microsoft.com/bartd/2018/02/08/filtered-indexes-and-forced-parameterization/
• SET IMPLICIT_TRANSACTIONS Behavior On Azure SQL Data Warehouse and APS
  • https://blogs.msdn.microsoft.com/apsblog/2018/02/20/set-implicit_transactions-behavior-on-azure-sql-data-warehouse-and-aps/
• Analyze Synchronous Commit Impact on High Commit Rate Workloads
  • Synchronous commit can introduce latency to your high transaction workloads, this can be especially impactful in environments that must complete large ETL batches on the weekends or overnight in order to prepare the data for the next business day. Using explicit transactions can optimize workload performance and still take advantage of the strengths synchronous commit offers (eliminate data loss).
  • https://blogs.msdn.microsoft.com/alwaysonpro/2018/02/06/analyze-synchronous-commit-impact-on-high-commit-rate-workloads/
• Uniqueifier considerations and error 666
  • If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
  • While it´s unlikely that you will face an issue related with uniqueifiers, we have seen rare cases where customer reaches the uniqueifier limit of 2,147,483,648, generating error 666.
  • Msg 666, Level 16, State 2, Line 1: The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID <PARTITIONID>. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
  • If you use the statement ALTER INDEX ALL ON <TABLE> REBUILD WITH (ONLINE = ON), it will reset the uniqueifiers (across all version starting SQL Server 2005 to SQL Server 2017)
  • https://blogs.msdn.microsoft.com/psssql/2018/02/16/uniqueifier-considerations-and-error-666/
• More Showplan enhancements – UDFs
  • We added two new attributes: UdfCpuTime and UdfElapsedTime. These provide the total CPU and elapsed time (again, both in ms) that is spent inside all scalar user-defined functions, during the execution of a query. Before this improvement, the internal execution of these functions was hidden from the query plan of the calling query.
  • These will also be available in the upcoming SQL Server 2016 SP2.
  • https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/
• More Showplan enhancements – Row Goal
  • To assist in the discoverability of Optimizer row goal use and its impact in query execution [we've added] a new operator property EstimateRowsWithoutRowGoal.
  • This will also be available in the upcoming SQL Server 2016 SP2.
  • When a query uses a TOP, IN or EXISTS clause, the FAST query hint, or a SET ROWCOUNT statement, that row goal is used as part of the query optimization process.
  • If the row goal plan is applied, the estimated number of rows in the query plan is reduced, because the Optimizer assumes that a smaller number of rows will have to be processed, in order to reach the row goal.
  • https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-row-goal/
• LogReader errors ‘Validating publisher’ after AlwaysOn failover.
  • https://blogs.msdn.microsoft.com/repltalk/2018/02/21/logreader-errors-validating-publisher-after-alwayson-failover/

Recent Blog Posts and Articles

• SELECT *, Indexes, Data Access, and the Optimizer!
  • https://blogs.technet.microsoft.com/dataplatform/2018/02/15/select-indexes-data-access-and-the-optimizer/
• Simplify REST API development for modern Single-page apps with SQL Server
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/01/29/simplify-rest-api-development-modern-single-page-apps-sql-server/
• Sync SQL data in large scale using Azure SQL Data Sync
  • https://blogs.technet.microsoft.com/dataplatforminsider/2018/02/15/sync-sql-data-in-large-scale-using-azure-sql-data-sync/
• Migrating to Azure SQL Database with Zero Downtime for Read-Only Workloads
  • The MSAsset engineering team used a phased, incremental approach for moving from SQL Server to Azure SQL Database.  This incremental approach helped reduce the risk of project failure and allowed the team to learn and adapt to the inevitable unexpected variables that arise with complex application migrations.
  • [They] set up transactional replication from SQL Server to Azure SQL Database, for use in accommodating read-only activity [and created] up to four active geo-replication readable secondary databases (in the same region) to accommodate read-only traffic scale requirements.
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/02/15/migrating-to-azure-sql-database-with-zero-downtime-for-read-only-workloads/
• SQL Server Replication Enhancement – Dynamic Reloading of Agent Profile Parameters
  • With SQL Server 2017 Cumulative Update 3, we introduced an improvement to SQL Server Replication wherein, changes to the Replication agent parameters can be reloaded dynamically, without having to restart the agent.
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-replication-enhancement-dynamic-reloading-of-agent-profile-parameters/
• Applying NLP in Sentiment Classification & Entity Recognition Using Azure ML and the Team Data Science Process
  • https://blogs.technet.microsoft.com/machinelearning/2018/02/14/applying-nlp-in-sentiment-classification-entity-recognition-using-azure-ml-team-data-science-process/

Recent Training and Technical Guides

• Expanded Data and Analytics technical journey – 5 new technical services added!
  • https://blogs.technet.microsoft.com/ptsblog/2018/02/12/expanded-data-and-analytics-technical-journey-5-new-technical-services-added/
• Power BI Desktop February Feature Summary
  • https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2018-feature-summary/
• Power BI Service and Mobile January Feature Summary
  • https://powerbi.microsoft.com/en-us/blog/power-bi-service-and-mobile-january-2018-feature-summary/
• Free: Microsoft AI Bootcamp Materials for Emerging & Pro Developers
  • https://blogs.technet.microsoft.com/machinelearning/2018/02/06/microsoft-ai-bootcamp-materials-for-emerging-pro-developers-now-available-for-free/
• Transform your industry with new Microsoft IoT in Action webinar series
  • https://azure.microsoft.com/en-us/blog/transform-your-industry-with-new-microsoft-iot-in-action-webinar-series/
• Get started with Azure Cosmos DB through this technical training series
  • https://azure.microsoft.com/en-us/blog/get-started-with-azure-cosmos-db-through-this-technical-training-series/

 

Monthly Script and Tool Tips

• Presenting with SSMS
  • How to use the “Presentation Mode” that’s now built into SSMS v17.0+
  • https://blogs.technet.microsoft.com/dataplatform/2018/02/25/presenting-with-ssms/
• [R language] DataExplorer: Fast Data Exploration With Minimum Code
  • https://blog.revolutionanalytics.com/2018/02/dataexplorer.html
• MAP Toolkit 9.8 Now Available!
  • https://blogs.technet.microsoft.com/mapblog/2018/02/22/map-toolkit-9-8-now-available/
• PowerTip: Use PowerShell to read an RSS feed
  • Example: Invoke-RestMethod -Uri 'https://blogs.msdn.microsoft.com/sqlupdates/rss.aspx'
  • https://blogs.technet.microsoft.com/heyscriptingguy/2018/02/21/powertip-use-powershell-to-read-an-rss-feed/
• Windows PowerShell and the Azure Text-to-Speech Rest API (Part 1)
  • https://blogs.technet.microsoft.com/heyscriptingguy/2018/02/28/windows-powershell-and-the-azure-text-to-speech-rest-api-part-1/
• PowerShell and the REST API for the IT pro
  • https://blogs.technet.microsoft.com/heyscriptingguy/2018/02/21/powershell-and-the-rest-api-for-the-it-pro/

 

 

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