Comparing Two Execution Plans (2021-03-30)

 SQLServerCentral Newsletter for March 30, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Comparing Two Execution Plans]( - [Building reusable table build scripts using SQL Prompt]( - [Using Kerberos Configuration Manager for SPNs Validation]( - [From the SQL Server Central Blogs - Filtering SQL Compare to a Schema]( - [From the SQL Server Central Blogs - Moving Into Consulting 101]( Question of the Day - [The Command Palette in Azure Data Studio]( The Voice of the DBA  Daily Coping Tip Cultivate loving kindness towards others today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Generating Art I'm not much of an artist, at least not in the traditional sense. My son practices drawing objects, and my daughter has studied some painting in college. I'm somewhat amazed at the things they notice and reproduce on a flat surface at times. In the digital world, I'm not much better, as simple tables and data are more my style, though Power BI makes me look more talented than I am, I likely could assemble a home page for Netflix, given the images of various movies. I'd also like to think that I'd consider the various things Netflix has, like localization and appropriate titles for all ages. There's [a blog that talks about the automated process]( they use to generate different home pages, depending on who is viewing. It's an interesting read, and it shows off some of the considerations for building a simple software application. What is interesting to me in all this is the importance of a lot of data. Especially metadata that is needed to ensure the process works well. After all, the software can't determine what's appropriate, language, etc. without some tagging to ensure that the correct images get added to the correct version of the home page. Those of us that work with data know how important it is to get clean, updated, managed data, and metadata. Without a way to ensure you know what information assets you have, it's not as easy to build applications to process them as you'd like. On the other hand, having lots of metadata can make it very easy to customize the software in a variety of ways. Something to think about as you collect new bits of data from your sources. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Comparing Two Execution Plans]( Emmitt Albright from SQLServerCentral This is a short look at a technique that I discovered recently in Management Studio (SSMS). The technique is to compare two graphical execution plans in the tool to understand what they are doing and how two different queries might affect a particular system, both with the image and with the properties' data behind the […] [Building reusable table build scripts using SQL Prompt]( Additional Articles from Redgate You need a fast, general-purpose way to save the results of a query or batch or procedure into any sort of worktable, such as a temporary table or a table variable or table valued parameter. A simple SELECT…INTO isn't versatile enough for these requirements, and the alternative ways to handcraft the list of columns are slow and error prone. Phil Factor shows how to create a 'table-build generator' that will do all this, and save you a lot of time, especially if you use a lot of working tables in your code. [Using Kerberos Configuration Manager for SPNs Validation]( Additional Articles from MSSQLTips.com Learn how to manage and troubleshoot Kerberos authentication for SQL Server using the Kerberos Configuration Manager. From the SQL Server Central Blogs - [Filtering SQL Compare to a Schema]( Steve Jones - SSC Editor from The Voice of the DBA One of the things that numerous clients have some to Redgate about is allowing a team of developers to work on a single shared database without creating conflicts. While... From the SQL Server Central Blogs - [Moving Into Consulting 101]( Will Assaf from SQL Tact Introduction to this blog series After being laid off by a Great Recession-era mortgage company in 2007, I entered into consulting. I made that switch from in-house DBA to consulting...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Command Palette in Azure Data Studio I want to zoom in on my code while using Azure Data Studio. I need to get to the command palette to do this, but how can I do this? 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) Secure Connections I have SQL Server 2017. I want to encrypt connections to SQL Server from clients. What option should I use? Answer: TLS encryption Explanation: SSL was discontinued with SQL Server 2016. In SQL Server 2017, you would use TLS encryption. Ref: - Enable encrypted connections to the Database Engine - - Discontinued database engine functionality - [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 [Availability Group]( - Planning to setup AG group between 4 replica's and 3 are located in the same DC and a DR node in different DC. Between Primary and SR1 it would be synchronous commit for Automatic failover and HA option. Primary Replica - Full, Diff and Log backups & Maintenance Jobs etc. Secondary Replica 1(Synchronous commit) - […] SQL Server 2017 - Development [Substract 2 values]( - Hello, I would like to minus from rows. 'Other Cost' minus the rest of the rows. select 1 Period,'Main' Category,100 Value union all select 1 Period,'Cost' Category,5 Value union all select 1 Period,'OtherCost' Category,20000 Value I tried like this but is not working. Can someone guide me. select Period,Category , SUM(CASE WHEN Category='Main' THEN [Value] […] SQL Server 2016 - Administration [SQL Server Agent issue]( - I am running into an issue and hoping to get some help. Every time I reboot my server, SQL Server service and agent starts automatically. I see that the Agent is running but when I run a job or any job, I get this error (See screenshot below). I then restart the SQL agent and […] SQL Server 2016 - Development and T-SQL [Return incorrect numeric value]( - Hello, I have make this query to return the max numeric value from the right for adding +1 : SELECT CAST(Isnull(MAX(REVERSE(SUBSTRING(reverse(Ltrim(Rtrim(cod))),0,CHARINDEX ('-',REVERSE(Ltrim(Rtrim(cod))))))),1) + 1 as varchar)[NextNumber] From QNC (Nolock) Where qnc.cod LIKE 'NCREC-CL'+'.'+'CMP' + '-'+'%' --This Return : 10 that is incorrect, the correct value must be 11 --if i modify my query and Group […] [Counting Instances in TEXT]( - Hello Community, I have been presented with the following SQL challenge Find the number of times the words 'bull' and 'bear' occur in the contents. We're counting the number of times the words occur so words like 'bullish' should not be included in our count. Output the word 'bull' and 'bear' along with the corresponding […] Administration - SQL Server 2014 [how to checkf if SQL Server CPU/RAM has bottleneck]( - how to checkf if SQL Server CPU/RAM has bottleneck? thanks! [service broker endpoint in disabled or stopped state]( - I had posted a thread some time back and since that didn't lead anywhere, trying to find answers again. Have a alwaysOn group to host sharepoint (SP) databases. SP was set up to use the cluster name for connection. But we noticed that when the failover occurred SP still pointed to the previous primary. So, […] Development - SQL Server 2014 [SELECT Name column from Name Table from either of 2 columns in main table]( - I have a table that selects an ID Username from the user's Loginname and another ID that gets that info from a program dropdown. The original design saved only the LoginName. Later the additional filed was added for users that login with a generic login. My problem now is how do I display the actual […] [CASE Statement with Dates in WHERE clause]( - I have a column [BatchDate] that contains dates in text format: 032221 032121 032021 031921 etc.... For days Tuesday, Wednesday, Thursday, Friday, I'm trying to get results on just those days. However, on Monday, I want results from Friday, Saturday, Sunday, Monday, to catch weekend orders. I'm not getting the syntax correct. As well, there […] SQL 2012 - General [how to do the following operation while the tables under different schema?]( - how to do the following operation while the tables under different schema? for example, there are two tables with same name but under different schema in the same database, one table saletable under schema A and the other table saletable under schema B, how to do this operation , such as sp_helpindex , sp_spaceused and […] SQL Server 2019 - Administration [Unable to force Queryplan in QueryStore, failure 8695]( - The queryplan looks forced but it's not used. Looking in "sys.query_store_plan" it says last_force_failure_reason = 8695 and last_force_failure_reason_desc = 'GENERAL_FAILURE'. The code 8695 doesn't seem to be documented anywhere (not even MS as far as I can see). Anyone knows better? Thanks alot in advance  Amazon AWS and other cloud vendors [use SCT to synchronize schema]( - Hello , I did a SQL database migration to RDS AWS DMS there were missing objects (index, foreign key, founction) Can I use SCT AWS Schema Conversion to add these objects? thanks General [How can I let system use more CPU and RAM of SQL Server ?]( - I have a system, when I perform one function (nobody uses this server), it works very slow, but I check the SQL server database(SQL server is 2005 ) CPU Utilization rate is about 15%, and RAM usage is low 6.5GB, the total RAM is 16GB. why the CPU and RAM utilization rate is so low? […] SQLServerCentral.com Announcements [Email Issues - 23 Mar 2021]( - We are experiencing some email issues with our provider, apparently from SPAM reports and filters. Since we are entirely opt-in and do not purchase any emails, we hope this is a misconfiguration that is resolved quickly. MySQL [Time n Date(Metrics)]( - I have the following code for time and dates metrics: SELECT DATE(o.created_datetime) as Date, o.store_uid as 'Store UID' , o.uid as 'Order' , o.rating as 'Rating' , IF(o.accepted_datetime < o.driver_ready_datetime, TRUE, FALSE) as 'Switch' , timestampdiff(minute, o.created_datetime, o.accepted_datetime) as 'Accepted Order' , if(accepted_datetime < driver_ready_datetime, timestampdiff(minute, o.accepted_datetime, o.driver_ready_datetime), timestampdiff(minute, o.created_datetime, o.driver_ready_datetime)) as'Invoiced Order' , timestampdiff(minute, […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -