How to detect tempdb usage?

On-premise, we are using SQL Server 2019 Standard Edition under READ_COMMITTED isolation level and I want to test how one database with perform with READ_COMMITTED_SNAPSHOT (the Azure default).

The query store is enabled.

I want to log some metrics (CPU usage, tempdb usage, IO) with READ_COMMITED for a week, and then the same with READ_COMMITTED_SNAPSHOT. Then if the performance is better (which I expect), to plan how to (if) scale up resources in order to activate the isolation level for all databases.

We have tools for logging the CPU usage, but not for the tempdb. I am wondering is there a query or utility which I can use in order to get the current tempdb usage (for example 20-50-70% at given moment) and create such log?

I am interesting in the tempdb, as the rows versions are going to be stored there and I am concern about some legacy code and heavy/long running updates which I may need first to rewrite in order to switch the isolation level.

I am looking for this data because there is a lot of legacy code which is slow to execute and blocks other queries. I have new code which executes in milliseconds, however sometimes, because of long-running CRUD operations and because of blocking, it is executed for 25+ secs which is bad. And I am in process of rewriting such legacy code but sometimes the particular case cost me few days, sometimes - few months. And of course, the client hates to wait...

lemonheads

Some alternative methods, if you don't want to log absolutely everything about your server to tables. Since you want to capture the version store specifically, these might be more useful in general.

SELECT
    SUM(user_object_reserved_page_count)
        * 8 / 1024.0 / 1024.0 AS user_objects_gb,
    SUM(internal_object_reserved_page_count)
        * 8 / 1024.0 / 1024.0 AS internal_objects_gb,
    SUM(version_store_reserved_page_count)
        * 8 / 1024.0 / 1024.0 AS version_store_gb,
    SUM(unallocated_extent_page_count)
        * 8 / 1024.0 / 1024.0 AS free_space_gb,
    SUM(mixed_extent_page_count)
        * 8 / 1024.0 / 1024.0 AS mixed_extent_gb
FROM tempdb.sys.dm_db_file_space_usage;

SELECT
    DB_NAME(database_id) AS database_name,
    reserved_page_count,
    reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;  

SELECT
    dopc.object_name,
    dopc.counter_name,
    dopc.instance_name,
    dopc.cntr_value
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name IN
(
    'Longest Transaction Running Time' ,
    'Version Store Size (KB)' ,
    'Version Cleanup rate (KB/s)' ,
    'Version Generation rate (KB/s)'
);

You can also log sp_WhoIsActive to a table to capture tempdb usage at the query level, though this won't differentiate between version store and temporary objects, etc.

StackExchange Database Administrators Q#303918, answer score: 6