SQL Server performance issue after an upgrade (2019-12-16)

 SQLServerCentral Newsletter for December 16, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server performance issue after an upgrade]( - [Controlling how SQL Prompt Formats your Code: The Knobs and Dials]( - [A Toolbox Every DBA Should Have]( - [From the SQL Server Central Blogs - Data Lake Architecture using Delta Lake, Databricks and ADLS Gen2 Part 4]( - [From the SQL Server Central Blogs - Tracking CPU Use Over Time]( Question of the Day - [Updating Statistics]( [Redgate SQL Monitor]( The Voice of the DBA  The Evolving DBA The world is changing. Plenty of people have written about the changes in the job of the DBA ([DBAKevlar](, [Brent Ozar](), including [myself](. While lots of vendors might postulate their database removes the need for a DBA, and [some might believe that]( I think that's disingenuous. Microsoft doesn't hire DBAs, but they hide that job under other titles. We still need DBAs, though the job changes a little in the future. Recently I saw a post from new PASS Board director, [Melody Zacharias](, where she notes that [DBAs are more important than ever](. She gives us five reasons, and I tend to agree with most of them. There is a need to somehow keep track of and manage data, much of which will continue to live in relational stores. As much as there is grown for key-value, graph, streaming, document, and other styles of data stores, we still use relational databases for many tasks. I don't see that going away anytime soon, especially for legacy software that will continue to live for some time. There is also the aspect of a DBA that involves the cleaning, checking, and verifying that data is in the form and format we expect, not to mention the need to ensure the data is intact and protected, and not changed inappropriately (or corrupt). Every system needs people that can dig through data and help decode its meaning as well as ensuring that business users can get it in to a report of some sort. Those tasks don't go away. While end users might have more self-service query tools, that doesn't ensure that they will actually know where the data is or how to reformat it. No end user tool is going to fix mm/dd/yyyy for a user expecting dd/mm/yyyy. There is also the need to just manage large amounts of data. I know data scientists have struggled to work with large scales of data in the past, and not just because of hardware limitations. They spend an inordinate amount of time gathering and organizing data that is used for analysis or AI/ML operations. Future organizations won't want to waste their time on those tasks and turn to some sort of DBA to help manage the large data needs of those people performing high level analysis. The DBA isn't going away, though the job is changing. Checking logs and backups is likely something you'll do less of in the future if you work in Operations. Instead, you'll manage more data, and you'll certainly need some coding skills to ensure you can perform in [GitOps environments](, where we don't click on things, but rather use code to define our systems and the configuration that we expect. The evolution can be daunting, but it can also be exciting. You have time to learn and change and build new skills. Embrace the opportunity to grow and change in your career as a DBA. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [SQL Server performance issue after an upgrade]( Kanishka Basak from SQLServerCentral Recently, I came across a problem where the client reported severe performance degradation of their OLAP system. Most of the reports that were running were either timing out or were returning data after a long time. The problem started right after the client had undergone an upgrade, that included the following, Software change, that includes […] [Controlling how SQL Prompt Formats your Code: The Knobs and Dials]( Additional Articles from Redgate Phil Factor explores and discusses the current state of the art in SQL Formatting, as done automatically by SQL Prompt. [A Toolbox Every DBA Should Have]( Additional Articles from Database Journal As a DBA it is important to have a toolbox that supports the work you do. You need to have a toolbox that allows you to work smarter instead of harder. Read on to learn about some key tools every DBA should have in their toolbox! From the SQL Server Central Blogs - [Data Lake Architecture using Delta Lake, Databricks and ADLS Gen2 Part 4]( Gerard Wolfaardt from Gerard’s Tech This is the fourth and final post in a series about modern Data Lake Architecture where I cover how we can build high quality data lakes using Delta Lake,... From the SQL Server Central Blogs - [Tracking CPU Use Over Time]( Grant Fritchey from The Scary DBA A question that I’ve seen come up frequently just recently is, how to track CPU use over time. Further, like a disk filling up, people want to know how...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Updating Statistics When are statistics updated? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Moving a Default Schema I have the user, JoeDev, that has a default schema of dbo. I want to move this to the [dev] schema. What code should I run? Answer: ALTER USER JoeDev WITH DEFAULT_SCHEMA = dev Explanation: The ALTER USER command is used to change the default schema. Ref: ALTER USER - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Always on in standard and enterprise edition]( - Hello everyone! I would glad to know, what is the difference in Always on between Standard and Enterprise edition? And also there is something that change in 2012 and above? Thanks! [Migrating DB encrypted by master key]( - I'm migrating DBs from a 2008 R2 instance onto a 2017 instance. I have one DB that is encrypted by the master key, how do I migrate this successfully across to a new instance? [Log Shipping - What has changed]( - We currently have a situation with a vendor where they will only give us access to our data via 5 minute log ship files that we import into our read only db. this is fine for most queries, but we need a way to get details about what has changed in each period in the […] SQL Server 2017 - Development [Insert in batches using select coalesce option]( - Hi, I am trying to insert multiple tables data into one table using Insert in batches and with select coalesce. I am getting Ambiguos column (id) error. Can some one please help me to resolve the issue. Thank You. ERROR MESSAGE: Msg 209, Level 16, State 1, Line 26 Ambiguous column name 'id'. Line:26 - […] [Fastest Way to Calculate Total Number of records inserted per day]( - Hi, I need to show a number of records per day in the dashboard of the web application if the user reload the page the count will automatically be updated. Initially, we don't have any problem as the records grow to the millions of records the query execution takes slow. The following is the query […] [Fastest way to query Millions of records with Pagination and Total Record Count]( - HI, We had a table that contains a huge volume of data inserted per day it contains millions of rows. Currently, we had 2 million records in staging but once we hit the production the data will be easily more than 10 - 20 million rows Previously we had a pagination query that took more […] SQL Server 2016 - Administration [changing db owner with always on replication (to fix DB properties window)]( - SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group. There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things […] [What\'s the best way to keep a record of all records inserted via an SP call?]( - Hey guys, so I may be overthinking this but basically, I have a bunch of stored procedures which I want to begin keeping historical track of. I want to know which records were updated by which SP & when, and I'm just looking for the best way to do this. Is there any built in […] [Impact value about missing indexes]( - is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan? Thanks for all.  [I can't Uninstall SQL Server 2016]( - Hi, I have SQL Server 2016 installed in my machine but I can't uninstall it, it doesn't appear on my Control Panel, I looked again using CMD wmic, product get name, it also doesn't appear. But I can see it, SQL Server on the services and SQL Server Configuration Management. I also tried using CMD […] SQL Server 2016 - Development and T-SQL [BCP utility]( - Hi All I am trying to export some data from a SQL server database to a CSV file. As I need to do this for around hundreds of records, I am using a cursor to loop through and creating dynamic file names to write individual files. This seems to be working ok. Some of the […] Administration - SQL Server 2014 [Trouble connecting in single user mode]( - I have an SQL server which I need to get admin access to. Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project), I get a screen full of continuous login failures […] Development - SQL Server 2014 [How to get information about an AG group using powershell]( - Hi Experts, Does anyone has a powershell script which displays below information: for a given AG listener name or AG group name, I need to get Listnener name, AG name, replicas , replica role desc , Can we get this information using pure powershell which reads registry values or do we need to execute a […] SQL 2012 - General [SQL Monitoring]( - Hi All, I have SQL 2012 instance on a vm server which I am testing with Red Gate Monitoring Tool installed on my machine and my machine machine is set a base monitor for testing purpose. Problem I am facing is that it only monitor while I am logged on to the machine. If I […] Strategies and Ideas [Tracking History in Fact Table]( - Hi, I was hoping somebody would be able to offer advice in relation to tracking history in a fact table, in particular whether #2 below would be a viable option? I would have thought that #1 would be bad for performance given the volume of records created through history so maybe not best practice?. If […]   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -