How to display to GO count in SQL

Question

Is there a way to track the execution count of a GO command in SQL Server.

I need to purge a large amount of data in very small batches from a very gigantic.

The best option I have found is to loop with GO and let it run overnight. I would like to know where I am at to report back on my peers how far I am at as it will hit the production database performance.

GO is the only method I have found that allows a swift cancelation without roll back.

/*

  using a Variation of Fast Ordered Deletes
   https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/
 
  I delete from an Index view TOP 1000.
  NOTE: a direct delete from the table takes ~30min.

*/
DELETE FROM v_DataToBePurged_FOD
GO 500000000

I need to run this command manually overnight to free up space and run GO for a very large amount of time.

Is there a undocumented way to see where I am at if I run it multiple time? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go

Answer 1

You're performing a loop, so write one. This is a lot more elegant than session context, and a lot more self-documenting than a client tool batch separator that happens to have a magical argument for exec count. And to avoid getting beat up by the SSMS output buffer and being led to believe this is degradation (I can assure you that PRINT is not causing your statements to go slow), you can use RAISERROR ... WITH NOWAIT:

DECLARE @i int = 1;
WHILE @i <= 500
BEGIN
  EXEC dbo.sp_something;
  RAISERROR('Done with iteration %d.', 0, 1, @i) WITH NOWAIT;
  SET @i += 1;
END

You can even have it so it only prints out every 10th or 25th execution, e.g.

  IF @i % 10 = 0
  BEGIN
    RAISERROR('Done with iteration %d.', 0, 1, @i) WITH NOWAIT;
  END

Now, as to the actual problem (deleting a large number of rows from a table while avoiding disruption, blocking, etc.), a comment that I totally agree with is that if you are deleting > 50% of the table, it can actually be much more efficient to save off the rows you want to keep into a new table, drop the old, and rename the new. That's not always that simple because of lots of things like constraints and relationships, but it's an option worth considering.

Barring that, see this article, which talks about how to batch deletes effectively, while minimizing the impact you have on other transactions:

• Break large delete operations into chunks

Some of your comments, I'm not sure how to address, because I don't know what “prevent transaction from writing to it” or “drop the required line” or “unforeseen behaviors” mean, or whether our definitions of "heavy" and "degradation" and "commit" match. (For the latter, each statement is its own transaction unless you explicitly wrap a set of statements in a transaction.)

I truly believe you are inferring from Management Studio's dodgy print/output behavior that the statements are running slowly, and that is definitely not the thing you should be doing to measure the performance of DML.

Answer 2

Using session context allows to simulate a global variable within a session.

EXEC sp_set_session_context 'GoCount',0; 
GO 
--SESSION_CONTEXT GoCount cannot be reuse until reconnect
DECLARE @GoCount VARCHAR(100) = CONVERT(VARCHAR(100),1 +  ISNULL(CONVERT(INT,SESSION_CONTEXT(N'GoCount')),0))
EXEC sp_set_session_context 'GoCount',@GoCount;  
PRINT CONVERT(VARCHAR(100), SESSION_CONTEXT(N'GoCount'));

EXEC sp_something
GO 500