TempDB Contention, Query Store Tuning and SQL Tamagotchi (Database Weekly 2019-08-24)

 Database Weekly for August 24, 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  SQL Tamagotchi! Mini-Servers as Database Pets I love small SQL Server installations as much as the big beasts. There is much pleasure to be had from buying second-hand, 19-inch racks and installing SQL Server instances on them, getting strange little black boxes intended for routers and turning them into network SQL Server instances, or even spinning up Ubuntu virtual machines in Azure as unlikely hosts for database instances. There was a time that I used to wonder why I did it. I supposed that it is the same pleasure that one gets from keeping goldfish or a pet rat. It's difficult to justify in economic rather than emotional terms. I won't pretend I do it out of dedication to work. It's fun, though not without frustrations. It is useful, though, to have them when I'm trying out different technologies, different database systems and generally keeping up to date. It is good to keep all this play separate from the day-to-day work, and it allows me to try things out well away from any corporate systems. It also helps with some SQL Server Development work. As well as providing a great skunkworks, I believe that it is useful to have a pet SQL Servers for work. Even if most of your work is on shared servers, a lot of testing, especially when you test out resilience and scalability, is better done when you are a god in an empty universe that you can destroy. Even development work is usually helped by doing it on a slow server because it is completely obvious when you get it wrong. There was a happy time, long ago, when I used to be able to see the arm of the hard drive flicking backwards and forwards frantically whenever I got an index wrong. Ah, nostalgia. Perhaps it is slightly strange that I like to create a local network of data stores and databases just when everyone who knows stuff is increasingly using the cloud and hosted services. However, I think there is room for both technologies, when you're doing development work. After all, all you need is an Ubuntu 16.04 machine with at least 2 GB, preferably lot more, and a good SD card, hang it on a decent local network, and you have a very responsive system. Linux SQL Server has the ultimate virtue of working just like Windows SQL Server, and it just takes a few minutes to install. You can soon simulate some quite complicated setups. The lazy way, I reckon, is to host instances in docker containers on a Linux server. Once you have a good docker image, it is quick work to set things up. A Windows host seems to work just as well, though the hard work is done in a VM. This gives you a very flexible server that can be doing SQL Server work one week and then MongoDB, PostgreSQL or Apache CouchDB the next. However, personally, I'm even more fascinated by the idea of using a Raspberry Pi 4 with SQL Server or stuffing SQL Server onto an Intel 'Coffee Lake' mini-ITX rack. Phil Factor [Join the debate, and respond to the editorial on the forums](  [Redgate Data Masker]( 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 [Database Build Blockers: Cross-Server Database Dependencies]( Phil Factor demonstrates how to tackle builds when databases make cross-server references. The technique uses synonyms to represent the remote objects, and local 'stub' objects to overcome the problems caused by 'missing references' when building the individual objects. [Resolving Cross Database Dependencies in SQL Change Automation using Local Databases or Clones]( Kathi Kellenberger explains how to avoid build problems, when working with set of inter-dependent databases, by restoring or provisioning copies of all required databases to the development, build, or test instance. [Managing Data Masking Rules in Larger Teams using Source Control]( What if you have several people in the team who are responsible for data security across your databases, and they need to work together to develop and maintain the data masking configurations, which must then be applied consistently as part of an automated provisioning process? How should they do it? The solution turns out to be simple: source control. Administration [Disable all Triggers on a Database]( Sometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on. Analysis Services / BI on the MS Stack [High Memory Usage and Calculated Columns]( An explanation of how calculations involving large time ranges and ineffective DAX expressions can cause big spikes in memory used, in Azure Analysis Services. Azure SQL Database [Research Paper Week: Constant Time Recovery in Azure SQL DB]( If you’re considering enabling Constant Time Recovery in SQL Server 2019 (when it ships), you should read the paper to understand the risks and drawbacks of a feature like this. [Research Paper Week: Automatic Indexing in Azure SQL DB]( This easy-to-read 12-page paper is wonderfully candid about how Azure’s auto-indexing does what it does, the challenges it faces, how it’s succeeded, and times when it’s failed. [Azure SQL DB Serverless]( The new serverless model, currently in public preview, provides a compute tier that is optimal for a single database that has unpredictable usage patterns. [Prevent mistakes with Azure Resource Locks]( Azure Resource Locks are the Azure version of child proof locks on your kitchen drawers. They are handy when you have to give people a little more access to an Azure environment than you might like, and want to remove the chance of someone accidentally deleting a resource. Azure SQL Managed Instance [Track unauthorized backups using Azure SQL Managed Instance Audits]( If you don’t use TDE on a database, or there is a risk that someone can remove it and then take a backup, then Azure Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening. Backup and Recovery [Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store]( ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. And now, SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance. Computing in the Cloud (Azure, Google, AWS) [Looks Like A VM Problem]( The Cloud is basically an AirBnB for your server. That’s cool. But, they’re still VMs, and you still need to pay close attention to how they are set up for SQL server. [TOP 5 Benefits of Azure Dedicated Hosts (Preview) for SQL Server Workloads on Azure VMs]( Discussing discuss top benefits of Azure Dedicated Hosts for SQL Server workloads, namely Physical Isolation, Compliance, Control Failovers, Cost Savings and Licensing flexibility. Conferences, Classes, Events, and Webinars [What You Need to Know About the 2019 DORA Accelerate State of DevOps Report]( Wednesday September 18 16.00-17.00 BST/ 10.00-11.00 Central - Join Jez Humble and Steve Jones to learn the latest insights from the 2019 DORA Accelerate State of DevOps Report. [Using Redgate, AKS and Azure to bring DevOps to your Database]( Tuesday 17 September 11.30-12.30 BST - Join Microsoft MVP's Hamish Watson and Rob Sewell to learn practical solutions on how to bring DevOps to your database. [SQL in the City Streamed: 20 Years of Redgate]( Wednesday September 4 14.00-19.00 BST/ 08.00-13.00 Central - Register for our free virtual learning event, to enjoy educational and entertaining sessions from Microsoft MVPs and celebrate 20 years of Redgate. DMO/SMO/Powershell [PowerShell 7 Preview 3]( A preview of some of what's coming in the next release (around end of year)... Data Mining / Data Analysis [A Brief History of Master Data]( Before master data and transactional data, there was the datum. And to understand that history, we must go backwards in time. Database Design, Theory and Development [Research Paper Week: In-Memory Multi-Version Concurrency Control]( This white paper compares and contrasts the methods used by SQL Server (Hekaton In-Memory OLTP), Oracle, Postgres, MySQL, SAP HANA, and others. DevOps and Continuous Delivery (CI/CD) [Key Findings for Database Professionals from the Accelerate State of DevOps Report 2019]( Speed and stability are not tradeoffs, but heavy change processes negatively affect both. [How do you make DevOps Succeed?]( Grant has been involved both in successful failed DevOps implementations, but what were the key differentiators between between the two? [Introduction to DevOps: DevOps and the Database]( When the DevOps pipeline does not include the database, the database can become a bottleneck and slow down the delivery of new features. In this article, Robert Sheldon discusses the challenges involved with including the database and how to overcome them. MDX/DAX [Rounding to 15 Minute Intervals]( Needing to round Date/Time data to the nearest 15 minute increment but discouraged at how unhelpful the DAX Date/Time function were, Dave Mason tries to do better using T-SQL. [Blank row in DAX]( There are two functions in DAX that return the list of values of a column: VALUES and DISTINCT. This article describes the difference between the two, explaining the details of the blank row added to tables for invalid relationships. Performance Tuning SQL Server [Profiler and Trace versus Extended Events]( Why on earth would you recommend to new data professionals working on modern systems, let’s say at least 2016 or better, that they should be using Trace? [A quick tuning win with Memory-Optimized Tables]( If you have Table Variables that use User-defined Table Types, a quick win is to create the type as a Memory-Optimized table. [Batch Mode Bitmaps in SQL Server]( Paul White delves into batch mode bitmaps, which have a very different implementation from their row-mode counterparts. [For Better Query Store Adoption, Make Querying It Easier]( Erik Darling on the irony of needing to tune Query Store queries in order to find queries to tune... [It’s Not Always Parameter Sniffing]( When you’re investigating performance issues, it’s important to know what things look like when the server is running well, and what things look like when the’re not. [Can DOP Change Query Plan Choices?]( After a while tuning a query, sometimes it’s fun to mess with the DOP it’s run at to see how things change. I wouldn’t consider this a query tuning technique, more like a point of interest. [Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans]( This paper proposes something really cool: watching an execution plan change over time, splitting it into parts, and then combining parts to form a SuperPlan™! [SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH]( Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting your query on the trolley to Slowsville, but had not way to prove it? Your wishes will be 100% granted in SQL Server 2019. [Monitoring TempDB Contention using Extended Events and SQL Monitor]( When the tempdb database is heavily used, processes in any database on the instance will be forced to wait, due to contention when the SQL Server engine tries to allocate pages in tempdb. Phil Factor shows how to monitor for signs of trouble. PowerPivot/PowerQuery/PowerBI [Storytelling with Power BI 5/7: Ensure consistent context]( Highlighting two features that help you to set the context, the data property of bookmarks and Sync slicers. [Dynamically Show Completed Months in Power BI]( One common requirement I have come across a few times is to limit a Power BI visual to only show completed months and exclude the current active month. This is common if you load data on a daily basis, but some of your reporting is done on a monthly basis. [Using Data Category to Have a Better Visualization in Power BI]( This article explains how to use Data Category, a lesser-known field-level configuration that can be helpful in multiple visualizations. [The Art of the Disconnected Table]( How to build a Power BI report to see the MTD and YTD of sales for a selected date, and a graph showing the sales of each day in the month of the selected day up to that day. Reporting Services [Report Builder Basics for Power Users and IT Professionals]( Explaining the Microsoft Report Builder tool which is used to build and manage paginated reports, and how to use it to create SSRS reports. SQL [Yes and No Questions Are Not So Simple]( Joe Celko explains why questions requiring yes or no answers are more complicated than you might realize in both spoken language and computing. SQL Server News [SQL Server 2019 release candidate is now available]( Amit Banerjee announces the first public release candidate for SQL Server 2019. SQL Server Security and Auditing [Tracking Data Changes - Change Tracking, Part 2]( Enabling change tracking for tables. [Data Security: How to Secure Sensitive Data]( Your audit should include investigations into your system vulnerabilities, current threats, and best practices for protection. The OWASP Top 10 vulnerabilities list is a tool that can help you determine where risks might be present and how they can be avoided. T-SQL [PARSE vs CAST and CONVERT]( T-SQL often provides multiple ways to “skin a cat”1 as they say. In this post, we’ll take a look at two “interesting” ways to convert dates and times from character-based columns into a column using the preferred datetime data-type. [Transaction Isolation Levels and sp_executesql]( Isolation Levels and sp_executesql don’t mix quite as nicely as you might hope. [Constructing a datetime from an integer date and an integer time]( Storing date and time separately as integers back before we had date and time data types - but there’s still lots of legacy code out there that use them! Testing Software [You are naming your tests wrong!]( Giving your tests expressive names is important. Proper naming helps understand what the test verifies and how the underlying system behaves. In this post, we’ll take a look at a popular, yet inferior naming convention and see how it can be improved. The Lighter Side [License Plate "NULL"]( There was a DefCon talk by someone with the vanity plate "NULL." The California system assigned him every ticket with no license plate: $12,000.  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -