How to detect tempdb usage?

Question

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...

Answer 1

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.

Answer 2

Brent Ozar way

You could either setup Brent Ozar's tools to monitor the database usage (as with perfmon data).

Once you added his script on a dba database, you have to schedule a regular collect of data through the use of sp_BlitzFirst stored procedure.

Here is what I do personally (you'll have to replace the parameters with those that suite to your environment) :

EXEC sp_BlitzFirst 
@OutputDatabaseName = 'AdminSQL',
@OutputSchemaName = 'dbo', 
@OutputTableName = 'BlitzFirst',
@OutputTableNameFileStats = 'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
@OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = 'BlitzCache',
@OutputTableNameBlitzWho = 'BlitzWho';

DECLARE @retentionPoint AS DATETIME2 = dateadd(week, -2, getdate());

DELETE FROM [dbo].[BlitzCache] WHERE CheckDate < @retentionPoint;
DELETE FROM [dbo].[BlitzFirst] WHERE CheckDate < @retentionPoint;
DELETE FROM [dbo].[BlitzFirst_FileStats] WHERE CheckDate < @retentionPoint;
DELETE FROM [dbo].[BlitzFirst_PerfmonStats] WHERE CheckDate < @retentionPoint;
DELETE FROM [dbo].[BlitzFirst_WaitStats] WHERE CheckDate < @retentionPoint;
DELETE FROM [dbo].[BlitzWho] WHERE CheckDate < @retentionPoint;

I execute this every 15 or 30 minutes through SQL Server Agent, depending on the precision I want for my analysis.

Once you have the data you want, you just have to collect the delta views with some tool like PowerBI or Excel to show a graph :

SELECT * FROM dbo.BlitzFirst_PerfmonStats_Deltas WHERE CheckDate >= @analyzisStartDate AND CheckDate <= @analyzisEndDate;
SELECT * FROM dbo.BlitzFirst_WaitStats_Deltas WHERE CheckDate >= @analyzisStartDate AND CheckDate <= @analyzisEndDate;

Microsoft SQL Server DataCollector way

You can setup DataCollection into a DataCollector through "Management" > "Data Collection".

Then once the data collection had enough data, you can simply use the reports from the DataCollector datawarehouse.

Take care, as this solution is becoming more and more deprecated. Query store is somehow trying to replace it, but the analysis doesn't take the height DataCollection does.

Answer 3

I am interesting in the tempdb, as the rows versions are going to be stored there

Nope. Azure SQL Database uses Accelerated Database Recovery, which relocates the version store into the database to be used in failover and recovery.

So just turn ADR on and measure the IO of your database.

Here is how to turn ON Accelerated Database Recovery :

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = ON