SQL Updates Newsletter – June 2018

Recent Releases and Announcements


Troubleshooting and Issue Alerts


Recent Blog Posts and Articles

  • Detecting unconscious bias in models, with R
    • For a practical demonstration of one way you can go about detecting proxy bias in R, take a look at the vignette created by Paige Bailey for the ROpenSci conference. The vignette details general principles you can follow to identify proxy bias in an analysis, in the context of a case study analyzed using R.
    • The case study demonstrates predictions biased by race even though race was not explicitly included in the adaptive boosting. That's because zipcode, a variable highly associated with race, was included in the model.  Read the complete vignette to see how Paige modified the model to ameliorate that bias, while still maintaining its predictive power. All of the associated R code is available in the iPython Notebook.
    • http://blog.revolutionanalytics.com/2018/06/understanding-bias.html
  • TEMPDB – Files and Trace Flags and Updates, Oh My!
    • Object Allocation Contention: There are some cases where having multiple files alone does not completely address PFS contention. For these cases, we have implemented a fix where we not only round robin between the files, we also round robin between the PFS pages within the file. The tradeoff with spreading the allocations throughout the file is that a shrink operation may take longer because it will need to relocate data to the beginning of the file.
    • With this change, not only will increasing the number of files help with PFS contention, increasing the size of the files (which increases the number of PFS pages in the file) will also help.
    • Metadata Contention: Whenever we remove something from the temp table metadata cache, we need to delete the corresponding rows from our metadata tables. Now we are starting to see contention on the deletes from the cache because of the increase in table metadata that comes along with some of the new features implemented in this version. To address this problem, we have made 3 main changes to how we prune the temp table cache: (1) We changed the process from synchronous to asynchronous. (2)  We reduced the number of helper threads to one per NUMA node and increased the number of tables that get removed with each pass. (3) We optimized the latching strategy used when we scan for metadata.
    • So how do you know if you have this metadata contention? Rather than PFS and SGAM pages, these will be pages that belong to system objects such as sysobjvalues and sysseobjvalues
    • We are continuing to work on improving tempdb performance and metadata contention, but in the meantime, there are some best practices you can employ in your code that might help avoid the contention: (1) Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them ends. (2) Do not alter temp tables after they have been created. (3) Do not truncate temp tables (4) Move index creation statements to the new inline index creation syntax that was introduced in SQL Server 2014.
    • Auditing Overhead:  We introduced functionality to allow for transaction-level auditing in  Common Criteria Compliance (CCC) which can cause additional overhead, particularly in workloads that do heavy inserts and updates in temp tables, even if CCC is not enabled. In SQL Server 2016 you can enable trace flag 3427 to bypass this overhead starting with SP1 CU2. Starting in SQL Server 2017 CU4, we automatically bypass this code if CCC is disabled.
    • https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/
  • Lift SQL Server Integration Services packages to Azure with Azure Data Factory
  • New memory options for Analysis Services
  • Cross-instance point-in-time restore in Azure SQL Database Managed Instance
    • Azure SQL Database Managed Instance enables you to create a database as a copy of another database at some point in time in the past. This is known as point-in-time restore feature, and up till now you could perform point-in-time restore only within the same instance.
    • The latest release of Azure SQL Database Managed Instance enables you to perform point-in-time restore of a database from one instance to another. This might be useful if you need to be sure that you could easily restore a database to another instance if there is some issue on the original instance, or if you need a database for testing or auditing purposes on the test instance and you want to use copy of some of the existing database on another server.
    • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/06/07/cross-instance-point-in-time-restore-in-azure-sql-database-managed-instance/


Recent Training and Technical Guides



Script and Tool Tips


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