SQL Updates Newsletter - February 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
  • Filtered Indexes and Forced Parameterization: Can’t we all just get along?
  • SET IMPLICIT_TRANSACTIONS Behavior On Azure SQL Data Warehouse and APS
  • 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.

Recent Blog Posts and Articles

Recent Training and Technical Guides


Monthly Script and Tool Tips



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