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