Optimized Plan Forcing with Query Store in SQL Server 2022 (2023-09-04)

SQLServerCentral Newsletter for September 4, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Optimized Plan Forcing with Query Store in SQL Server 2022]( - [How To Use Flyway for Database Migration in Spring Boot]( - [Running Queries Across Many SQL Server Databases]( - [From the SQL Server Central Blogs - Another option is coming to gain extended support for SQL 2012]( - [From the SQL Server Central Blogs - Decrypting Stored Procedures The Cumbersome Way]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [The Isolation Level Across Databases]( The Voice of the DBA  Labor Day 2023 Today is [Labor Day]( in the US, a holiday for most people that was created to recognize workers, primarily those in factories and manufacturing. Many of us are labor, and while we might be off today, we ought to remember there are plenty of our peers in restaurants, hospitals, and other places that are working today. Please respect and remember that. For much of the rest of the world, it's Monday. It's actually Monday for me as I'm in Manchester, England for the [Redgate DevOps in a Day](. I'm working today, but don't worry, I'll get a makeup day sometime in October. Or November. We'll see. Enjoy your day today, and I hope labor isn't too taxing today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Optimized Plan Forcing with Query Store in SQL Server 2022]( Almighty from SQLServerCentral In this Article , We will delve into the world of Query Store and explore how to use Optimized Plan Forcing to improve performance in SQL Server 2022. We will discuss what it is, how it works, and how it can impact your system's performance. [SQLServerCentral Article]( [How To Use Flyway for Database Migration in Spring Boot]( Additional Articles from developer.com In this article, we're going to look at how we can use Flyway to manage our SQL database schema in a Spring Boot application. [External Article]( [Running Queries Across Many SQL Server Databases]( Additional Articles from SimpleTalk A challenge that reappears periodically in the world of databases (especially database management) is the need to run code on a subset of databases and to do so in a nuanced manner. [Blog Post]( From the SQL Server Central Blogs - [Another option is coming to gain extended support for SQL 2012]( SQLPals from Mission: SQL Homeostasis Another option is coming to gain extended support for SQL 2012 If you still have SQL 2012 servers, you are probably aware that support... [Blog Post]( From the SQL Server Central Blogs - [Decrypting Stored Procedures The Cumbersome Way]( Steve Jones - SSC Editor from The Voice of the DBA I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Steve Jones - SSC Editor from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Isolation Level Across Databases I have a table in a couple databases with a fullname of "Steve Jones" and a firstname of "Steve". With one connection, I run this in the database, sandbox2: BEGIN TRAN UPDATE dbo.A SET firstname = 'Delaney' WHERE fullname = 'Steve Jones' This transaction is open. I now run this on a second connection in the sandbox database: set transaction isolation level read uncommitted; use sandbox SELECT * FROM dbo.A AS a1 INNER JOIN sandbox2.dbo.A AS a2 ON a2.fullname = a1.fullname For the row with fullname="Steve Jones" that is queried in sandbox2, what is returned for the firstname? 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) Once a Month I have a procedure that I want to run once a month, on the first Tuesday of the month. What is the easiest way to get this done? Answer: There is an option in the SQL Agent scheduler to run something on the first Tuesday of the month. Explanation: The SQL Agent includes options to run something on the first occurrence of a day of the week. You can pick the first Tuesday of the month for a job to run. This is in the GUI or in sp_add_schedule. Ref: sp_add_schedule - [ [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](. --------------------------------------------------------------- Administration - SQL Server 2014 [Server memory question]( - I would like to know please how much memory I should add to my server here is what I have 51452 in MB and we be adding more data and databases,please recond how better to maintain SQL Server 2019 - Administration [Securing access to SQL Instance]( - Hi, I have a need to harden-down users access to a SQL Server instance. I have a number of SQL authenticated accounts that need to be able to create and curate database objects on a dedicated schema. At the moment they have membership of the ddl_admin role on the database, however this is proving insufficient […] [Can I set the publisher database into read-only mode]( - Hi - Can you set the db into read-only mode if it’s part of replication. As part of migration, I need to set the DBs into read-only mode before I take final backup. Can I do this if the replication is still active to another server? [ALTER or DROP TABLE could not clean up root row within 10 seconds]( - We keep getting these errors quite frequently. How do we troubleshoot and fix these errors ? Are they benign in nature or need to be fixed. Please advise. SQL Server 2019 - Development [SQLCMD output]( - I have a script that outputs a query to a text file. When using -o I am pointing to a shared directory on another server. Does sqlcmd use smb (445) or port 139 to connect when writing the file? [if condition select into temp table]( - Hi, I have a query where I search and provide the result. select * into #a from( Select ID, 1 as counter from TableA where ID=@id union all select ID, 2 as counter from TableB where ID=@id)a declare @tocheck integer set @tocheck =(select tocheck from #a) if @tocheck=1 DROP TABLE IF EXISTS #partA begin insert […] [TRY-CATCH blocks ignore converting errors in ORDER BY statement]( - BEGIN TRY SELECT * FROM ( SELECT 'int' AS [text] ) AS tmp ORDER BY CONVERT (INT, tmp.[text]) PRINT '1' END TRY BEGIN CATCH PRINT '2' END CATCH Just had an interesting case today where it seems as if when you unsuccessfully cast text as an INT, then this looks to be a severity level […] Amazon AWS and other cloud vendors [Is AWS the right choice for a dirt-simple API for myself only?]( - I’ve written a little Python script to post to multiple social medias at once, and I want somewhere to host it as an API so I don’t have to set up a static IP, keep a raspberry pi going etc. Essentially I want to be able to send a post request to this and have […] Connecting [MS OLE DB Driver for SQL Server vs MS OLE DB Driver 19 for SQL Server]( - I have two OLE DB drivers installed. Microsoft OLE DB Driver for SQL Server and Microsoft OLE DB Driver 19 for SQL Server Is it possible to uninstall the un-versioned driver (Microsoft OLE DB Driver for SQL Server) without breaking anything? Or must it be retained due to possible software dependency? Also, it looks like […] [Updates for multiple SQL Server ODBC versions]( - We have computers on our network with multiple ODBC versions installed on the same machine. For example, on one machine there is Microsoft ODBC Driver 17 for SQL Server 17.10.3.1 and Microsoft ODBC Driver 18 for SQL Server 18.1.2.1 Because of possible software dependency, it looks like you have to keep both versions and install […] Integration Services [Merging Data Sets in similar way as VLookup]( - I want to automate my manual Vlookup using Excel to SQL/SSIS. Table: is used with  The columns that are the same colour are used for Vlookup. Currently. I am having trouble loading data from tables with non primary/secondary keys to tables with primary and secondary keys (tables:). Error I am getting: SSIS package "C:\Users\Anthony.DESKTOP-ES5HL78\source\repos\WeatherDataETL_\WeatherDataETL\WeatherDataETL\Package3.dtsx" […] Discussions about Books [Elevate Your Brand with Ebook Services| Ebook Writing Hub]( - Get top-notch Ebook Writing Services for compelling and engaging content. Our expert writers create informative and captivating eBooks tailored to your niche, ensuring reader satisfaction and value. Elevate your digital presence with our professional eBook writing. Contact Us Phone:+1 (844) 546-4246 Business Email:info@ebookwritinghub.com Address:3111 Doctors Drive Los Angeles California 90017, United States SQL Server 2022 - Administration [Just updated server to 2022 - CXSYNC_PORT waits - HELP!]( - Hi Updated from SQL2014 to SQL2022 today. Seeing a ton of CXSYNC_PORT waits. Its as if something is not configured correctly, What can I check? This in an on-premise server. SQL22 ENTERPRISE Microsoft SQL Server 2022 (RTM-CU1) (KB5022375) SQL Server 2022 - Development [how new window clause works in following case.]( - hi, can i get one example of following from this link "If the is contained in a that is part of which is a simple table query, then the scope of the new window_name also includes the , if any, of that ."  yours sincerley [Multicolumn indexes and fragmentation]( - Is there anything special you should think of concerning index fragmentation and multicolumn indexes (sorry for possible bad terminology)? I am not sure how to think here, if I need to think about it all. Currently I've got a index that looks like this: int, int, bit, datetime. I think it's easier to understand when […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -