SQL Server Resumable Index Operations (2023-06-26)

SQLServerCentral Newsletter for June 26, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server Resumable Index Operations]( - [[Video] Office Hours: Professional Development & Training Questions]( - [Cheat Sheet for SQL Server DBAs - Monitoring Current Activity, Blocking and Performance]( - [From the SQL Server Central Blogs - Parameter Sensitive Plan Optimization in SQL Server 2022. What is it and is it any good?]( - [From the SQL Server Central Blogs - Using Azure Automation and Runbooks to Run Azure SQL Database Maintenance Tasks]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Catching a Deleted Column]( Featured Script - [User-Created GENERATE_SERIES() Function for Older SQL Server Versions]( The Voice of the DBA  Concurrency Challenges Around Schema Changes I saw [a great question on Twitter]( from Frank Pachot, a developer advocate of [Yugabyte](. He wrote: Without thinking how your preferred database deals with it, what do you expect if: - session 1 starts to reads table T - session 2 drops table T - session 1 continues to read The choices in his poll were: session 2 waits, session 2 fails, session 1 fails, both fail. My first thought was SQL Server and the default need for session 2 to get an exclusive lock. In that case, session 2 would wait. Most people answered that same way, but then Frank [posted a follow-up]( with a link [to his blog](. The answer for Yugabyte is that session 1 fails as it gets the message that the table was deleted. Leaving aside the decision to drop a table, imagine this is some schema change instead. In the blog, some good points are raised about how to handle high concurrency changes, and the potential problems with having session 2 wait. On a busy system, this could cause lots of blocking as threads stack up behind session 2. It's an interesting read about the challenges of distributed system design and how to handle changes. In some sense, I get that this makes sense, but I wonder where this causes issues. If any schema change on the table by session 2 were to cause an error in session 1, that would be bad. However, does this mean that the database engine must now evaluate whether a column change impacts a query in flight? Then decide to send an error? What about evaluating views or procedures/functions that depend on Does this mean that all nodes need to sync up the schema changes quickly, and at a higher priority than data movements? I don't know exactly how Yugabyte distributes data, and if there are copies on multiple nodes, but I assume there are. This adds complexity to the communication between nodes, which is likely needed. Honestly, if someone drops a table and they should have, we probably don't want clients getting results. If they do this accidentally, I'd like to know about it quickly. The question is interesting, and there are multiple ways to look at this, but I found it fascinating to spend a few minutes thinking about the complexities of data in distributed systems and the challenges involved. This also made me think that the people who keep data safe and fix problems when they occur are invaluable in the modern world. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Server Resumable Index Operations]( Almighty from SQLServerCentral The ability to pause and then resume index rebuilds has been added to SQL Server. This is a fantastic feature, and with the release of SQL Server 2019, we can now replicate it on the actual create index process. Learn how to use this feature in this short article. [Technical Article]( [[Video] Office Hours: Professional Development & Training Questions]( Additional Articles from SQLServerCentral A lot of today’s questions from have a common theme, but it’s just a coincidence. Listen to the answers. [External Article]( [Cheat Sheet for SQL Server DBAs - Monitoring Current Activity, Blocking and Performance]( Additional Articles from MSSQLTips.com It seems like every DBA has a USB stick or a shared drive filled with scripts they can pull up in almost any situation. How can I build such a library of T-SQL files? [Blog Post]( From the SQL Server Central Blogs - [Parameter Sensitive Plan Optimization in SQL Server 2022. What is it and is it any good?]( Matthew McGiffen from Matthew McGiffen DBA Parameter Sensitive Plan (PSP) optimization is a new feature in SQL Server 2022 that aims to improve the performance of parameterized queries. It is part of the Intelligent Query... [Blog Post]( From the SQL Server Central Blogs - [Using Azure Automation and Runbooks to Run Azure SQL Database Maintenance Tasks]( Tracy Boggiano from Database Superhero’s Blog I’ve been using Azure SQL Database for quite some while and have set up it in many various ways to run Ola’s Index Optimize and The post Using Azure Automation... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Catching a Deleted Column I have this code in a SQL Server 2019 database: CREATE TABLE moduletest( col1 int, col2 int, col3 int) GO CREATE OR ALTER PROC usp_getTest AS SELECT col1, col2, col3 FROM dbo.moduletest GO --run once to get proc in cache EXEC dbo.usp_getTest; GO I now run this code: ALTER TABLE dbo.moduletest DROP COLUMN col3 GO When I run this next code, what is returned? EXEC sp_refreshsqlmodule 'usp_getTest' GO 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) Central Management Server Credentials How can I store credentials for each instance in a Central Management Server (CMS)? Answer: All CMS connections use Windows Auth with the current user credentials. Explanation: The CMS only stores the address of the instance. All security uses the credentials of the user that connects to the CMS. Ref: Create a Central Management Server and Server Group - [ [Discuss this question and answer on the forums](  Featured Script [User-Created GENERATE_SERIES() Function for Older SQL Server Versions]( Jonathan AC Roberts from SQLServerCentral In the world of SQL Server, adaptation is key. While the built-in GENERATE_SERIES() function was a valuable addition in SQL Server 2022, its absence in older versions created a functionality gap. Enter the user-written GENERATE_SERIES function. Adapted from Jeff Moden's "dbo.fnTally", it offers an efficient means to generate a series of numbers within a defined range in older SQL Server versions. Its design mirrors the built-in function in SQL Server 2022, making the transition between versions as simple as removing the dbo. prefix. This forward-thinking design reflects the ingenuity of the SQL Server community, ensuring a seamless, efficient database migration experience. CREATE FUNCTION [dbo].[GENERATE_SERIES] ( @Start bigint, @Stop bigint, @Step bigint = 1 ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH H2(N) AS ( SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) )V(N)) --16^2 or 256 rows , H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows , H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows , Tally AS ( SELECT N = ROW_NUMBER() OVER (ORDER BY N) FROM H8 ) , StepTally AS ( SELECT value = @Start + (@Step * (N - 1)) FROM Tally ) SELECT TOP(ABS(@Stop - @Start) / ABS(@Step) + 1) value FROM StepTally WHERE @Step <> 0 ; [More »](  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 [Listener IP]( - I have a listener which is listening to IP which is online. However, I have one of the IP which is showing failed status instead of offline. Is that something to be concern? [delete as head blocker]( - Hi All, Need some help in finding out of what is the problem in the query and how can we make the query faster. On one of the production environment, a DELETE statement was running over 50 mins and blocked more than 100 sessions. I wasn't able to get the actual plan. However, including estimated […] [Availability group]( - Looking for the options whenever the primary replica is refreshed then synching the secondary replica would need to be done and which would take long time if there are more that 1 secondary replica's. Checking to see is there a faster way to sync the secondary replica after the primary replica refresh? Thanks in Advance! SQL Server 2016 - Development and T-SQL [SQLCLR functions]( - Hi all  We've written some SQLCLR functions in C# to try and get rid of the UDF's we'd written (that were a lot slower). Is there any way to add in some sort of help text to explain what the functions does, what parameters are required, what format the parameters should be in, etc? […] [Converting rows into columns]( - I have a table that contains three columns with ID, dates and value. I want to convert the contents into rows so that the date column will become a row and the FldVal will become the contents. Date and FldVal could contain different values. I think I can use Pivot, but not sure what to […] SQL Server 2019 - Administration [Insert Token Replication in AG]( - Hello , I work in a context where my transactional replication is configured in AG the replication monitor is configured in a single node sometimes when I try to trace a Token for monitoring replication I get a message that tells me my database is in read ony Does anyone of you have an […] [Restore script is not working]( - Below script is not working .Not sure what is wrong in it. Any suggessions pls.  --get the last backup file name and path Declare @FileName varChar(255) Declare @cmdText varChar(255) Declare @BKFolder varchar(255) Declare @DT datetime set @FileName = null set @cmdText = null set @BKFolder = '\\xxxxx\xxxxx\' set @DT = getdate() create table #FileList […] [Best Practice Analyzer for SQL Server 2019]( - Hello All I am looing to run the BPA for SQL Server 2019 version , but don't see BPA available for SQL 2019. Please help me if any other same as BPA available ( replacing BPA ) to use for SQL Server 2019. Thanks, [XE Module start histogram not show all]( - Hi, module_start doesn't find some events in non-filtered session, and finds them when filtering for database name. I exec manually from ssms procedure and it count in filtered XE but wasn't in non filtered XE. tried also NO_EVENT_LOSS option but still same. I found the same problem in other SQL instances. This can be problem […] SQL Azure - Administration [Finding Last Time the Users Connected to a Particular Azure PaaS Database.]( - Can we Find the Last Time the Users Connected to a Particular Azure PaaS Database. If yes then please could the logic for that be shared as well. Reporting Services [Redirecting HTTP to a new server being blocked by SSRS]( - We have migrated SSRS between servers as part of a SQL version upgrade. The original server has IIS on it (for MDS) and I want to use HTTP Redirection to send the traffic from the old server to the new one. If I go to "" it redirects without issue but if I go to […] [SSRS REPORT]( - Hi I have report in Crystal looks like below Is possible to create similar to this in SSRS? here is the one I already start working on Analysis Services [Excel PivotTable Fields order different after upgrade to SQL 2022 SSAS tabular]( - Hello, We have upgraded one of our SSAS Tabular environments to SQL 2022, and are noticing changes in the way the measures and dimensions are shown in the “PivotTable Fields” section in Excel when connecting to models on this server. The tabular model we are using has compatibility level 1500, and when we deploy this […] SQL Server 2022 - Administration [Devil's Advocate]( - We're planning to start upgrading all of our 200+ SQL Server 2016 instances to SQL Server 2022 over the coming year or two. We have a Volume Licence Agreement and all of our instances run either Enterprise or Developer edition. We have one Azure instance for a chatbot. The rest is on-premises. I'm combing through […] SQL Server 2022 - Development [Why do my queries lose connection?]( - I've used SSMS for many years (currently v18.12.1) and only in the last few months have I seen this problem. I tend to open SSMS at the start of the working week and close it down at the end (5 days later). During that time I might open a dozen or more query windows and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -