Improve your indexes, unblock your database builds in Database Weekly

 Database Weekly for August 10, 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  The Sequel to SQL SQL (originally SEQUEL) was envisioned as a fourth-generation declarative language that would require minimal training and that anyone could use to get information from databases. I remember reading the marketing froth when it was introduced. I was impressed. The concept was very much the zeitgeist, in the eighties and nineties. A Basic generator was released optimistically called 'The Last One' (it wasn't). Visual Basic was going to do away with all the labour of C or C++ programming (it didn't). 4GL languages (e.g. BusinessObjects) were developed that generated SQL under the covers. Breathlessly, the marketing men said it would put an end to SQL coding (it hasn't). The industry is pock-marked with failed attempts to do away with the need for skilled professional developers. You'd have thought that anyone who popped their heads up above the parapet and declaimed loudly their brilliant idea that would do away with the need for skilled IT developers, they'd be generally considered an appropriate target. Bizarrely, it seems that many businesses are now betting on it happening. [IDC's annual survey]( came up with results that enabled them to predict that within five years we will have a new 'developer class' producing 'code without custom scripting', and they will represent nearly a third of the developer population. Apparently, this class of developer-less "code engineers" and "digital innovators"' will 'supplement traditional developers by leveraging visually guided development tools, low-code development platforms, no-code development platforms, and model-driven development tools to create and refine digital solutions.' Old grey-muzzled developers like me will wallow in the nostalgia. It will be like an 'eighties and nineties' revival party, but presumably without the mistakes. Remember the craze for outsourcing development work? Went well, didn't it? Err, no, quite the contrary in my experience. Why doesn't the industry learn from their past mistakes, or even take an interest in what went wrong and why? Could it be that the wrong people in the industry learn from bitter experience? It is somewhat odd that so many of the opinion-formers on the technology of the IT industry have so little experience working in the industry as technologists. Few other professions are so clearly led by marketing people. The surgeon, for example, who eyes you up whilst fingering a scalpel doesn't often get his expertise and advice from non-medical marketing experts. The bridge you drive over during your commute was designed by engineers who were driven only by professional experience and technical knowledge, not soft-focus stock images in glossy brochures. The IT industry is odd in this way. Clearly there is something wrong with the profession. We technologists are poor communicators and slow to point out the obvious technical issues with heavily marketed technologies and development practices. Instead, we occasionally look up from the terminal, shrug, do a little tinkering, and pop a new buzzword on our CV. It's easier. Phil Factor Phil Factor [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. Administration [Switching Out Table Partitions: A Walkthrough]( Kenneth Igiri demonstrates a process you can use to ensure that data older than six months is moved out of a table in a clean fashion. [DBA Training Plan 8: What’s Inside Your Own Databases]( What’s using the most space, what’s getting used the most often, and what you might be able to clean out to make your job easier? [Memory Consumption by Object]( SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. [SQLSkills SQL101: Upgrading to a Different Edition of SQL Server]( Glenn Berry walks through the simple steps to upgrade from one SQL Server Edition to another, such as from Standard Edition to Enterprise Edition. Azure CosmosDB [Writing SQL Against Cosmos DB]( Hasan Savran shows how to use CosmosDB’s SQL API, which uses T-SQL like language to query Cosmos DB documents . Azure SQL Database [Azure SQL Database Clustered Columnstore Index]( If you are, at minimum, running a Standard (S3) database, then you can now transform your row-store tables to the columnstore format without blocking incoming transactions [Run a PowerShell Script Against all of Your Azure SQL Databases]( Just about every command you run against an Azure SQL Database requires you to supply the server name and the resource group name as parameters, so you need to get the list of server names for each resource group. Joey d'Antoni shows how it's done. Computing in the Cloud (Azure, Google, AWS) [Performance Tuning On the Cloud]( Whenever people talk about “The Cloud” I often hear: “cloud is expensive” and, sooner or later, “if performance aren’t good we can scale up in minutes later” .The mindset of taking care of performance issue by scaling up/down cloud resources is a golden egg goose for cloud providers, and a money sink for businesses. Conferences, Classes, Events, and Webinars [SQL in the City Streamed- the birthday edition]( 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. [Fast and Reliable Development with Redgate Solutions for SQL Server]( Thursday August 29 18.00-19.00 BST / 10.00-11.00 Pacific - Register for this Microsoft hosted webinar to learn how Redgate tools for SQL Server help deliver software quickly. [How to boost team productivity with SQL Clone 4]( Wednesday August 21 16.00-17.00 BST/ 10.00-11.00 Central - SQL Clone enables dev, test and CI environments to be created and refreshed in seconds, on demand or through self-service, with the latest copy of production data, masked for compliance. [Redgate SQL Source Control]( DevOps and Continuous Delivery (CI/CD) [Introduction to DevOps: DevOps and the Database]( Database DevOps offers the promise of quicker, easier and more secure deployments while bringing application and database development efforts in line with one another. But DevOps is not a one-size-fits-all solution to application delivery, nor is it meant to be a developer-first strategy that leaves DBAs and IT administrators behind. DocumentDB/Key-Value/Graph/other NoSQL Databases [Graph Databases – Introduction]( Graph data is characterized by a very high relationship-to-entity ratio. If you have to model this in the relational world, the number of relationships will be too large and difficult to represent, let alone query on. This is the kind of problem that graph data modeling and querying helps us model and deal with. ETL/SSIS/Azure Data Factory/Biml [SSIS Design Preferences]( Meagan Longoria shares her set of preferences for the design of Integration Services packages and ETL processes. [Efficient maintenance of SSISDB]( The SSIS Server Maintenance Job has been turned off for over a year, SSISDB has ballooned in size, and there is now only 10MB left on the data drive. The required retention period is only 14 days, but the native maintenance procedure simply can't cope with deleting so many rows. John McCormack explains how he got round this sticky problem. General [Aireforge Studio Community Edition]( There’s also loads of great free scripts and programs out there like sp_whoisactive, Ola’s maintenance scripts and the awesome dbatools project, and now there’s another free tool to add to your armoury; Aireforge Studio Community Edition. Performance Tuning SQL Server [Index Choice and Parameter Sniffing]( Rounding out a few posts about SQL Server’s choice of one or more indexes depending on the cardinality estimates. Today we’re going to look at how indexes can contribute to parameter sniffing issues. [tempdb Enhancements in SQL Server 2019]( If you were struggling with tempdb contention, the best advice has always been to create multiple data files that are sized the same, with the same auto-growth settings, enable trace flag 1118 (and maybe 1117), and reduce your tempdb use. From the customer side, this has been the limit of what can be done, until SQL Server 2019. [The Curious Case of… what is the wait resource (0:0:0)?]( Worried about why you're seeing the wait resource (0:0:0)? What should you do? Simple answer: ignore it and troubleshoot the other waits. [What columns are in that index?]( Erin Stellato discuses a common pattern of adding columns from the clustering key to the key definition for the nonclustered index and what this means when it comes to creating your nonclustered indexes. [Clustered Index Seek]( A wonderful analysis of how a Clustered Index Seek... [Does SQL Server Choose Multiple Indexes Based On Selectivity?]( Erik Darling explains the problems you'll encounte... [Does SQL Server Choose Different Indexes Based On Selectivity?]( The general rule about leading column selectivity ... PowerPivot/PowerQuery/PowerBI [Power Query/M Optimisation: Getting The Maximum Value From A Column, Part 1]( Chris Webb shares some newly learned Power Query/M optimisation tricks that will give you some new ideas to try when optimizing your own queries. [Data Sonification in Power BI]( Data sonification uses variations in audio to hear differences in data values. This post attempts to show how to produce audio tones in Power BI for greater accessibility. It also demonstrates how to blend data with a standard range of audio pitches. [Using Version Control with Power BI Desktop Files]( Adding files, checking files in and out, viewing previous versions and reverting to previous versions. Professional Development [How to Persuade Your Company to Change]( You have an idea for a new process or a new product, or it might be a big change to existing processes or products. The challenge is getting it to happen, especially when doing so requires time from your coworkers. R Language [Files are fraught with peril]( Dan Luu discusses how file systems differ from each other and other issues we might encounter when writing to files. [Finding duplicates in data frame across columns and replacing them with unique values using R]( You have a dataset with many variables, and you wa... SQL Server Security and Auditing [Feature Restrictions in SQL Server 2019 are Worse Than Useless]( I love some of the new functionality in SQL Server 2019 but nobody’s perfect and occasionally the SQL Server team makes a mistake. A new feature introduced in SQL Server 2019 CTP 3.2, “Feature Restrictions”, is an unfortunate example of such a mistake. It’s a misguided attempt at improving security that not only increases the chances of SQL Injection, but it also prevented useful changes from being made. SQL Server on Linux [Linux distributions you can install SQL Server on]( Kevin Chant summarizes the current Linux distributions on which you can install SQL Server and explains a few of the pros and cons of each. T-SQL [T-SQL Regular expression: LIKE Operator and its use-cases]( Demonstrating the different use cases where we can use LIKE operator to search for data from a table based on a specific pattern. [“But NOLOCK Is Okay When The Data Isn’t Changing, Right?”]( Nope, not even close. Brent Ozar demonstrates. [5 Things You Need To Know When Reading SQL Server Execution Plans]( What you need to know to read an execution plan. [SQL Server String concatenation behavior]( The same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) (or NVARCHAR(4000) ) [Snapshot Isolation in SQL Server]( Snapshot isolation is a great alternative to transactional isolation when you have read-heavy loads. If you want to use it with mixed read/write loads, prepare for update conflicts and be sure to keep an eye on tempdb performance and growth. [Problems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)]( Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is "tiny bumfit"; I think the tiny bumfit bus goes from Penrith to Carlisle. [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. The final part of the article shows an automated technique using a Redgate tool (SQL Change Automation). [Abstraction Isn’t Magic]( Erik Darling gets called in to tune a lot of prett... Virtualization and Containers/Kubernetes [Expose Multiple Docker Ports]( I was working with containers recently with Jenkins. I didn’t want the server process running on my machine all the time, but I did need to allow some communication. Jenkins uses 8080 by default, but agents need another port. The answer? Multiple –p parameters. [Running Containers in a Virtual Machine]( You want to work with containers inside a virtual machine to do some automation testing. Is that possible? Yes, and very easy, as Grant Fritchey demonstrates.  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -