There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no secondary indexes.
We use SqlBulkCopy to quickly insert thousands or millions of ids into the table under a single batch id, so that various queries can join against it to perform filtering using that set of record ids associated with that batch. That approach much faster than attempting multiple queries that all send lists of ids in the query string. As soon as the queries complete, the batch of record ids is deleted. The table is empty on average when no queries are running, but may have hundreds of active batches as we process thousands of API requests per second that may use the table.
My question is, what kind of maintenance steps, if any, are required of a DBA to maintain these tables. For example, would it benefit at all from periodic index rebuilding, or will SQL Server handle cleanup of the deleted rows (freeing page blocks, etc.) well on its own. Also, is there any special configuration of the table or clustered index that would help, especially considering that the identifiers are random Guids (uniqueidentifiers).
Generally we use the latest version of SQL Server, Enterprise Edition. Some servers are in Azure, some in AWS.
I'm also interested in hearing alternatives. For example, I assume this approach is best with a permanent table, but I could also create a session-local temp table as part of a transaction that gets deleted when finished, rather than inserting and deleting rows from a permanent table. I just don't know how that would perform in comparison.