@SeanGallardy Some coworkers and I are on site tomorrow with David Browne (Rangers room iirc). I don't know if you would care to stop by, but feel free!
@sp_BlitzErik when you get a chance, let me know if anything is obviously wrong with this
SELECT FLOOR(0.01 * wg.request_max_memory_grant_percent * rs.max_target_memory_kb) max_memory_grant_kb
from sys.dm_exec_requests req
INNER JOIN sys.dm_resource_governor_workload_groups wg ON req.group_id = wg.group_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs ON wg.pool_id = rs.pool_id
WHERE req.session_id = @@SPID
AND rs.pool_id <> 1 -- impossibly small to measure performance improvement
AND rs.resource_semaphore_id = 0 -- assume everything goes to large resource semaphore
OPTION (FAST 1); -- gotta go fast
goal is to get the max available memory grant for a session. RG may or may not be turned on
Warning: Online index operation on table '' will proceed but concurrent access to the table may be limited due to residual lock on the table from a previous operation in the same transaction.
I was judging log space usage by looking at sys.dm_tran_database_transactions after the create index finished, but before I committed it
for the online build, there are two active transactions. for one of them, log space used goes up and down. after the query is done, you only see the one with the small amount of log space
for the offline build, there are two active transactions. log space only goes up. after the query is done, you only see the one with the large amount of log space
