SQL Updates Newsletter – May 2018

Recent Releases and Announcements


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.
  • 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
  • Important: Default auto statistics update threshold change for SQL Server 2016
  • Replication Enhancement – Improved Distribution Database Cleanup
    • The Distribution Cleanup job, which runs every 10 minutes on the Distributor Server is responsible for cleaning up the transactions and commands based on the specified transaction retention period.
    • In case of a highly active environment the cleanup job would need to delete a substantially large set of expired transactions and commands. The cleanup job uses a while loop to delete entries from the MSRepl_Commands and MSRepl_Transactions in batches of 2000 and 5000 rows, respectively.
    • The new implementation of the procedure allows users to parameterize the batch sizes inside the cleanup stored procedure and introduces a new adaptive approach to determine the batch sizes for each iteration. The procedure will, by default, honor any batch sizes that are mentioned explicitly as parameters. If no parameters are supplied, then the procedure will start with the default batch sizes of 2000 and 5000 rows and increase/decrease the batch sizes based on the performance of the previous iteration of the delete operation.
    • The new implementation also separates the subscription cleanup task from the main distribution cleanup work, by moving the subscription cleanup to the Expired subscription cleanup job that runs at the publisher.
    • Finally, In the older implementation the cleanup job reports the row counts based on the last iteration of the while loop and not the cumulative values for the rows deleted. This has been changed to accumulate and report out the cumulative numbers.
    • https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-improved-distribution-database-cleanup/


Recent Blog Posts and Articles

  • Emerging AI Patterns
    • In this blog we’ll dive into the patterns we see emerging for the use of AI across industries and experiences.
    • Virtual Agents: The first pattern is the use of virtual agents to interact with employees, customers, and partners on behalf of a company.
    • Ambient Intelligence:  This is using AI to both map a physical space and activity to a digital space, and then allowing actions on top of the digital graph.  Think about a warehouse that can detect a person walking in one aisle and a forklift driving in another that are on a collision course, the AI can prevent the pending accident.
    • AI-Assisting Professionals: AI can be used to help almost any professional be more effective. For example, we can help people in finance with forecasting. We also see AI starting to assist doctors in areas like genomics and public health. There are great examples in sales, marketing, legal and practically every other profession.
    • Autonomous Systems: You might think of self-driving cars when you think about autonomous systems, but it also extends to robotic process automation and network protection.
    • https://blogs.msdn.microsoft.com/stevengu/2018/05/24/emerging-ai-patterns/
  • CPU and Memory Allocation on Azure SQL Database Managed Instance
    • There is no explicit configuration option for memory allocated to a Managed Instance (MI) because on an MI, memory allocation is proportional to the number of vCores used.
    • Traditional SQL Server methods will not determine the actual amount of memory your MI instance can use.
    • MI instance is resource-governed at the OS level using a job object. Running a process such as SQL Server in a job object provides resource governance for the process at the OS level, including CPU, memory, and IO. This resource governance is what allows the service to share the same VM among multiple instances belonging to the same customer, without resource contention and “noisy neighbor” problems.
    • The sys.dm_os_job_object DMV exposes job object configuration for the MI instance. This is the DMV that should be used to determine the actual compute resources (vCores and memory) allocated to the MI instance
    • https://blogs.msdn.microsoft.com/sqlcat/2018/05/04/cpu-and-memory-allocation-on-azure-sql-database-managed-instance/
  • Azure SQL DB Managed Instance – sp_readMIerrorlog
    • When you connect to an MI instance, you can view its error log, via sp_readerrorlog.  But when you look at the MI error log, you may be surprised by the large volume of messages.  This diagnostic data is needed for Microsoft engineers to manage the service and troubleshoot any problems efficiently.
    • Sp_readmierrorlog is a simple stored procedure that returns the contents of the instance error log, filtering out messages that are unlikely to be useful to an MI customer.
    • The procedure is open source and is hosted on GitHub.
    • sp_readmierrorlog has the same familiar parameters and result set that sp_readerrorlog has.
    • https://blogs.msdn.microsoft.com/sqlcat/2018/05/04/azure-sql-db-managed-instance-sp_readmierrorlog/
  • How to Develop a Currency Detection Model using Azure Machine Learning
  • A Scalable End-to-End Anomaly Detection System using Azure Batch AI
  • Extract management insights from SQL Data Warehouse with SQL Operations Studio


Recent Training and Technical Guides



Script and Tool Tips


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