SQL Server 2019 Editions and Features Announced - Database Weekly (2019-11-02)

 Database Weekly for November 3, 2019 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  A Faster tempdb I like to call tempdb the “workhorse” of SQL Server. I’ve heard some other people call it other terms that were not so flattering, but since I like to keep things positive, I’ll stick with workhorse. SQL Server uses tempdb for many things. The obvious uses are temp tables and table variables, but tempdb is heavily utilized with snapshot isolation and Always On Availability Group read-only secondaries when you use those features. It is also used for sorting, some index rebuilds, after triggers… The list goes on. It could be the busiest database on the instance, so it deserves some special care. Not only is it busy, there is often metadata contention in tempdb. Objects are created and dropped so quickly, that the metadata pages become the bottleneck even if tempdb is on the best storage. It’s not so much the objects that cause the performance issue, it’s keeping track of them. One piece of advice to improve performance of tempdb is to create multiple database files to avoid contention of the metadata pages that keep track of the objects. (See this [article]( to learn more.) My favorite explanation about why this works is from Erin Stellato (@erinstellato). To paraphrase, imagine 50 people in a room with one door. They can’t all go out the door at the same time. If there were four doors instead, the room would empty much faster. With correctly configured, multiple tempdb files, the load on these “gatekeeper” pages can be spread out, the bottleneck is alleviated, and tempdb works faster. Creating multiple files is so important that Microsoft added [Automatic TEMPDB Configuration]( with SQL Server 2016. During both the wizard and command installations, tempdb will be configured during the install with recommended settings, or you can override them. This auto-configuration has been available only with SQL Server on Windows until now. Starting with 2019, this feature is also available with SQL Server on Linux. Even after following the recommendations, some shops were still seeing contention. To solve this problem, the engineers at Microsoft came up with a new option for SQL Server 2019, [Memory Optimized Tempdb Metadata](. This means that the metadata tables in tempdb will be stored in [In-Memory OLTP]( tables. Just the term “in-memory” sounds fast, but transactions against this type of table are fast because there is no lock or latch contention. It doesn’t matter how fast the objects are created and dropped, it won’t cause metadata contention. Note that this feature is not turned on by default; you have to enable it. Like many performance features, it is available in Enterprise edition. Memory Optimized Tempdb Metadata is just one of the many features that makes SQL Server 2019 great. Kathi Kellenberger [Join the debate, and respond to the editorial on the forums](  [Redgate University]( The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Set yourself up for success in 2020]( Is DevOps central to your 2020 IT Digital transformation? How does your IT strategy underpin the business objectives? Read our latest blog to ensure you’re ready for success next year. [Test data provisioning for development – free whitepaper]( Compare the most common approaches to provisioning test data to database development teams and see how they stack up. Learn why traditional approaches like restore from backup are no longer suitable, and how successful organizations are implementing a combined data masking and data virtualization approach. [Free Solution Brief – Dedicated Database Environments for Dev, Test and CI]( Developers and testers can be blocked from having a local, private database to work in because of disk space, refresh times, and data privacy. Download this free solution brief to learn how SQL Provision removes these blockers and enables production-like data to be delivered at speed. Download now. AI/Machine Learning/Cognitive Services [Why AI at the edge will reshape cloud computing]( Edge computing allows businesses to handle thousan... [Train and Deploy Machine Learning Models Using the AzureML Service]( Microsoft’s cloud-based, scalable Azure Machine ... Administration [Emergency Mode in SQL Server]( Paul Randal answers a reader question: I had a blo... [Because Your Index Maintenance Script Is Measuring The Wrong Thing]( Two Dollars My Dear Friend™ Sean recently wrote ... [Cardinality Estimation of Table Varaibles with Nullable Columns]( Milos Radivojevic takes us through a quick demonst... [Simulating ON DELETE CASCADE]( Aaron Bertrand has put together a procedure which ... Azure CosmosDB [Offset and Limit with Cosmos DB]( Hasan Savran takes us through the OFFSET and LIMIT... [Redgate Database Devops]( Azure Databricks and Spark [Azure AD Credential Passthrough and Databricks]( Anna Shrestinian, et al, explain how Azure Databri... Azure SQL Database [Cloud Era – Azure DBA]( I get asked quite a bit about my thoughts on the i... Computing in the Cloud (Azure, Google, AWS) [Event-driven architecture (EDA) with Azure Data Factory - Triggers made easy]( Creation or deletion files in your Azure Storage account may initiate data ingestion process and will support your event-driven data platform architecture. [Do you need to know about Microsoft Azure]( The problemYou don’t know anything about Microso... Conferences, Classes, Events, and Webinars [SQLSaturday #916 - Bangladesh 2019]( SQL Saturday comes to Bangladesh on Nov 16. [How to make your 2020 monitoring strategy a success]( Join Redgate’s Arneh Eskandari and BMW’s Tony Maddonna for a free webinar on Nov 20: How to make your 2020 monitoring strategy a success. Learn how to prepare for successful estate growth, expected trends for the year, and the role monitoring plays in engabling growth and stability in your organization. DMO/SMO/Powershell [PowerShell and Azure: AzureRM vs. Az]( Earlier this year Microsoft announced the new Azur... [Life of a variable in PowerShell]( Overview Recently, I was facing an issue that a va... Data Science [Why You Should Learn PostgreSQL for Data Science]( Click here to learn more about author Gilad David ... Database Design, Theory and Development [Using data modeling to deal with shady clients]( I run into the following Reddit’s question: My c... [A Guide to SQL Naming Conventions]( One of Java’s big strengths, in my opinion, is t... [Associating Data Directly with SQL Server Database Objects.]( It is easy to attach details and documentation to ... [Date Buckets in SQL Server]( Andy Mallon needs to create groups of date ranges ... [Dealing with NULLs in Java with SQL Server 2019]( Niels Berglund covers changes in SQL Server Machin... DevOps and Continuous Delivery (CI/CD) [Using Custom Deployment Scripts with SQL Compare or SQL Change Automation]( Phil Factor describes how custom pre- and post-dep... ETL/SSIS/Azure Data Factory/Biml [Validating upstream data quality in ETL processes, SQL edition]( It is a non-null constraint, not a non-ish-null co... [Embed an SSIS Package in an Azure Data Factory Pipeline]( The Azure-SSIS Team has done it again; they’ve a... General [A Quirk With Plan Caching And Dynamic SQL]( Have All Thirteen  HA/DR/Always On/Clustering [Availability Groups: What to do without MultiSubNetFailover = True]( I received an email from a client who is having is... [Build a SQL Cluster Lab Part 1]( This article is Part 1 in a series of articles sho... Microsoft News [Microsoft is replacing MSDN and TechNet forums with Microsoft Q&A]( Microsoft has released a preview of a new Microsof... [Windows Server 2019 Release Sees Server Core Adoption Soar]( Microsoft says that since the general availability... Performance Tuning SQL Server [SQL SERVER – Creating a Nonclustered Columnstore Index on Temp Table]( I was delivering training on SQL Server Performanc... [Analyzing "death by a thousand cuts" workloads]( Erin Stellato shows how to analyze patterns in you... [SQL SERVER – Using NEWID vs NEWSEQUENTIALID for Performance]( The first key column as uniqueidentifier datatype.... [Removing SSMS Completion Time–#SQLNewBlogger]( Another post for me that is simple and hopefully s... PowerPivot/PowerQuery/PowerBI [DAX Studio – What do all those numbers mean in the server timing output?]( Originally posted on: ... [Excel PivotTable Query Performance With Analysis Services, Power Pivot And Power BI (Revisited)]( Back in 2016 I wrote the following blog post about... [AutoML in Power BI, DAX and Overlays… (Roundup | October 28, 2019)]( Thanks for watching this week's Power BI news roun... [Analyzing DAX DISTINCTCOUNT Performance]( Marco Russo takes a look at how fast DISTINCTCOUNT... [Quickly review your Power BI Data model with VertiPaq Analyzer]( Patrick shows a quick way to review your Power BI ... Professional Development [In IT and Data, You Need to Skill Up to Keep Up]( Skilling up becomes more important (and necessary)... [Other Data People are Getting Ahead of You.]( The next time you need to look for a job, I’ve g... [Why I Make Animated Gifs for Presentation Demo Backups]( PASS Summit 2019 starts up next Monday with pre-co... SQL Server News [Enhanced HA/DR Benefits for SQL Server]( Amit Banerjee has just made a bunch of DBAs happy:... [What’s New with Standard Edition]( Niko Neugebauer is jazzed about SQL Server 2019 St... [SQL Server 2019 Feature Power Rankings]( Watch me comment on all of the features in this we... SQL Server Security and Auditing [skip-2.0 and SQL Server Security]( K. Brian Kelley has the lowdown on skip-2.0: Probl... T-SQL [Getting Table Row Counts]( Adrian Buckman has a new stored procedure: Sometim... [T-SQL 101: #41 Using string literals in SQL Server]( When we write string literal values, we need to su... [SQL SERVER – Getting Second Row from Table]( The other day during one of my old clients sent me... [How to create date buckets in T-SQL]( I was fiddling around with some data, looking at m... [Getting table row counts with sp_Tablecount]( Sometimes you just want to get a quick row count f... [Validating Upstream Data Quality with T-SQL]( Ed Elliott has a pattern to try: We can select tho... [SQL: Just like tables, IN clauses have no default order]( In Stack Overflow, I saw a poster with a query lik... Tech News [50 years ago today, the Internet was born. Sort of.]( The precursor to the Internet carried its first lo...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -