SQL can usually imbed the conditions into the query plan to access the table rather than scanning the entire table first.  Of course you'd have to look at the query...

This does look like something maybe outside of SQL Server?!

Although, in order to truncate the log, a checkpoint must be taken after backup.  So step 4b. should be a CHECKPOINT,...

Yes, the script is 100% safe to run in a prod environment.

It does change a couple of settings for max efficiency, but first it captures the current settings and reinstates...

That looks far more like what I would expect (even with only 2 days' worth of activity).

I can tell you that (1) you have the correct clustered index (2) I...

Not much showing.  Only thing you can really do for now is REORGANIZE the columnstore index(es).

Unfortunately there's very little activity against this table shown in the stats -- no more than 5 days' worth of activity.  Either SQL was restarted or a change was made...

Was the first result set empty??!

Not nearly enough to go on to analyze indexes.

If you'll run this script and post the two results from it, we can go further from there.  PRIOR to running, be...

SELECT 
  p.name AS [Plan], --<<--
--everything else same as before--

I would think you'd need to just do this:

...
BEGIN TRY
IF @object NOT IN ('TableA', 'TableB') --<<--
BEGIN --<<--
SET @subject = @@SERVERNAME + N' : ' + @event +...

Are you partitioning the table solely to improve SELECT performance?

How is the table clustered?  That is, what is the clustered index on the table now.  If the clus index starts...

DECLARE @time_to_stop_running time
SET @time_to_stop_running = '4:30PM'

WHILE 1 = 1
BEGIN
    SELECT <column(s)>
    FROM dbo.<your_error_table>
    WHERE ...
   ...

The business I'm at certainly doesn't require more than 200 gender "types."  Neither does yours I'm sure, nor anyone else's at this point (I guess some ultra-woke colleges...

Thank you all.

And as for space, if we have 100GB of data, then we have a 100GB of data. But I'd rather have 90GB data if...

Thank you all.

And as for space, if we have 100GB of data, then we have a 100GB of data. But I'd rather have 90GB data if that simply...