Columnstore indexes, PowerShell secrets, and more in Database Weekly (2020-04-24)

Database Weekly for April 23, 2021 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Looking Forward to SQL Server vNext Releases of on-premises SQL Server arrived quite rapidly for a few years with seven releases during the years spanning 2008 and 2019. While it has technically been less than two years since SQL Server 2019 reached general availability in November 2019, it feels like ages since we have heard any news about what’s coming next. At least for me, time has moved slowly over the past year due to the pandemic, so maybe it's not been so long since 2019 was released. Many DBAs are probably fine with waiting a bit and would like a break from upgrading so frequently. I’ve heard lots of “we are skipping version X and upgrading straight to version Y” from customers over the years. Of course, many also have wish lists of features they would like to see to make their SQL Servers run better or provide more value. Over the past five years or so, I’ve noticed that Microsoft is really listening to customers about where the issues are and functionality we’d like to see. One example is tempdb configuration to avoid contention. Starting with 2016, the installation automatically sets up multiple files, and you can specify size and autogrowth settings during installation. The startup flags (1117 and 1118) recommended for tempdb are no longer needed as that functionality is now baked in. There’s also a new recommended min and max memory configuration during installation. While it is easy enough to configure tempdb and memory after installation, there are a lot of shops that don’t have SQL Server expertise in-house so why not configure these settings according to best practices up front? In my case, I am most interested in Microsoft’s investment in [Intelligent Query Processing](. I’m excited about the possibility of queries running faster with minimal or no code changes. Here are a few of items on my wish list: - Expand [Adaptive Joins]( to Row Mode. The Adaptive Joins feature means that the cached plan can switch between Hash Join and Nested Loops Joins depending on the number of rows. Right now, this feature requires that a columnstore index be part of the query, but I hope that someday this functionality will be expanded to Row Mode and that requirement will be eliminated. - Chip away at the restrictions on [Scalar UDF Inlining](. This feature inlines the code from some UDFs so that it looks like the code is just part of the query, drastically improving performance in some cases. There is a long list of restrictions that keep a UDF from being inlined. I would like to see Microsoft work on some of these. What a game changer this would be! - Add [Batch Mode on Rowstore]( to Standard Edition. Batch Mode is a part of columnstore to improve the performance of large analytic queries by working on batches of rows. Batch Mode can now work on queries without a columnstore index. This is especially exciting for some of the window functions where scaling is a problem – window aggregates, percentile_cont, percentile_disc, percent_rank, and cume_dist. I’m happy with how this works, but I would like to see more shops be able to take advantage of it. - Materialize common table expressions. I love how readable Common Table Expressions (CTEs) make a complex query, but the performance is not always great. If the query uses the CTE in multiple places, the tables in the CTE are touched multiple times. I would love to see the results of the CTE saved in memory so that the data could be reused in the query. I can’t wait to see what the SQL Server teams at Microsoft are planning for SQL Server vNext, and, hopefully, some of my wish list items will be included.    Kathi Kellenberger [Join the debate, and respond to the editorial on the forums](  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 [DevOps as a bludgeon]( With both personal experience and the real world evidence to back it up, Grant Fritchey says, unequivocally, a well-implemented DevOps process helps the organization. But it's not always easy to bridge a siloed organization, and sometimes organizations adopt DevOps for the wrong reasons. [How to Generate Fake Test Data for SQL Server]( How to use a SQL random data generator to fill SQL Server tables with realistic test data, to shift left database unit testing, integration testing and performance testing so that it is performed during the early stages of database development. [Detecting Database Drift during Flyway Database Development]( How to detect database drift prior to running a database migration, so that you can be certain that a database hasn't been subject to any 'uncontrolled' changes that could affect the migration or result in untested changes being deployed to production. [Take our survey to win a $500 Amazon gift card]( Take the 2021 State of Database Monitoring survey and tell us about how you monitor your databases, instances and servers. By taking the survey, you’ll contribute to (and get early access to) the leading industry-wide report on database monitoring, and be entered to a prize draw for a $500 Amazon gift card. AI/Machine Learning/Cognitive Services [Insurance price prediction using Machine Learning (ML.NET)]( In this article, Chandra Kudumula shows how to use... [AI Adoption in the Enterprise 2021]( During the first weeks of February, we asked recipients of our Data and AI Newsletters to participate in a survey... Administration of SQL Server [All you need to know about Columnstore Indexes in one article]( I realised the other week, that despite a bunch of posts on indexes over the years, I’ve never written a blog post on Columnstore indexes.... Azure SQL Database [Altering a Computed Column in a Temporal Table in Azure SQL]( System-versioned temporal tables were introduced in SQL Server 2016... [How Do You Shrink Your Azure SQL Database?]( In the early days of Azure SQL Database (ne SQL Azure) we had size limits that were minuscule–I vaguely remember 5 and 10 GB... [Query serverless SQL pool from an Apache Spark Scala notebook]( Azure Synapse Analytics provides multiple query runtimes that you can use to query in-database or external data... [Extended Events and Azure SQL Database]( From Scary DBA (Grant Fritchey) Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you’re... DMO/SMO/Powershell [How to identify the the current user on Powershell]( I'm executing a Powershell Script . When I started... [How to configure SQL Server static port with Powershell]( Question: I have an SQL Server Instance - currently configured with a Dynamic Port. I'd like to change the setting from a Dynamic port configuration to a Static port... [Unlocking PowerShell Secrets]( So I’ve been kicking the tires and trying to do more with the Secrets Management modules from Microsoft, now that they are out of pre-release status... Data Privacy, Compliance, and GDPR [Through the Data Lens: Protecting Data Privacy]( From Dataversity Click to learn more about author Ian Pitt. 2020 presented some of the most unexpected challenges as the pandemic pushed many organizations to advance their digital transformation at an... DevOps and Continuous Delivery (CI/CD) [Deploy from one source to multiple SQL Server database types using GitHub Actions]( In this post I want to share how to deploy from one source to multiple SQL Server database types using GitHub Actions. [Azure Data Studio – Multiple Repos and Explorer]( If you read my T-SQL Tuesday post from this month, I mentioned that I’ve been using Azure Data Studio on daily basis. [5 Practical Ways to Improve CI/CD Processes]( From IT Pro - Microsoft Windows Information, Solutions, Tools If you "do" DevOps you likely have a basic set of CI/CD processes in place, but there's almost certainly room for improvement. [How different roles view database DevOps]( From Simple Talk Redgate released the 2021 State of Database DevOps Report in February, and I wrote a short article talking about the key insights from the report. More recently, I decided... [How to improve DevOps communication clarity]( Communication is at the heart of DevOps, but it can be difficult to achieve. Mike Cuppett explains how to improve DevOps communication clarity. DocumentDB/Key-Value/Graph/other NoSQL Databases [Why Graph Databases Are an Essential Choice for Master Data Management]( From Dataversity Click to learn more about author Brian Platz. Within the Data Management industry, it’s becoming clear that the old model of rounding up massive amounts of data, dumping it... HA/DR/Always On/Clustering [Properly Dealing with Encryption of Databases in an AlwaysOn Availability Group Scenario]( From SQL Server – {coding}Sight Total: 1 Average: 5Availability Groups are fantastic for High Availability/Disaster Recovery solutions, and I’m sure that fellow DBAs will agree with me. However, there will be times when we... NoSQL [How to choose between SQL and NoSQL databases]( Organizations have many choices when it comes to databases. In this article, Robert Sheldon explains how to choose between SQL and NoSQL databases. Performance Tuning SQL Server [SQL Server Parallelism Settings – Video Tip]( From Steve Stedman Cost threshold for parallelism and the max degree of parallelism are 2 important setting for your SQL Server. Find out how to set these and what they should be... [Execution Plan Video Training – Announcing the next block]( From SQL Server Fast It’s time! Time to formally announce the release... [Common Query Plan Patterns For Joins: Sorting Lookups]( From Erik Darling Data Pantsuit Most people see a lookup and think “add a covering index”, regardless of any further details. Then there they go, adding an index with 40 included columns to... PowerPivot/PowerQuery/PowerBI [Power BI Defects Dashboard and Pareto Chart]( Last week we built a Manufacturing Yield dashboard that showed first and final pass yield numbers... [Let’s continue the big data discussion with SQLBI (April 17, 2021)]( From Guy in a Cube Let's keep the big data discussion going with Powe... [How to Create the Date Table in Power BI]( From SQL Server – {coding}Sight Total: 1 Average: 5The time intelligence functions... [Power BI Data Modeling Sessions]( From Paul Turley's SQL Server BI Blog This is going to be a very busy week for presentat... [Power BI Dataflow Performance, Premium Per User And The Enhanced Compute Engine]( From Chris Webb's BI Blog Over the years I have written a lot about Power BI... Professional Development [get good feedback]( From Storytelling with Data We recently kicked off a new 10-week course, which... [Things I Love About Teleworking in Iceland]( From Brent Ozar (Personal blog) We moved to Iceland in January to work remotely, and there’s a lot about it that feels like we’re living in the future. It’s an easy transition for Americans.... T-SQL [Basic OFFSET–#SQLNewBlogger]( From SQLServerCentral Blogs Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. The other day I saw an article on... The... [Altering a Computed Column in a Temporal Table in Azure SQL]( From DCAC System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version... [QUOTENAME and dynamic SQL]( Fairly quick one today, just talking about the QUOTENAME() function... [GREATEST / LEAST Function Alternatives in SQL Server]( One of the most upvoted requests on feedback.azure.com is for Microsoft to add MAX/MIN as non-aggregate functions in SQL Server... [SQL Server TRIM, LTRIM, and RTRIM Functions]( From SQL Server – {coding}Sight Total: 2 Average: 5 The TRIM function of SQL Server is designed to remove leading and trailing spaces from a character string. A leading space is a space that...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -