MIN_ACTIVE_ROWVERSION() is a system function that returns the lowest active rowversion value in the current database. Its use then is very similar to @@DBTS. In fact, the docs for MIN_ACTIVE_ROWVERSION() (currently) say: If there are no active values in the database, MIN_ACTIVE_ROWVERSION() returns the same value as @@DBTS + 1. Does it though? You may be tempted then to replace some of your @@DBTS expressions with expressions like this: Broken example /* This is not always equivalent to @@DBTS */ SELECT CAST(MIN_ACTIVE_ROWVERSION() - 1 AS rowversion); Try to figure out why this is broken b ..read more

Takeaway: If you want to extract rows from a table periodically as part of an ETL operation and if you use Read Committed Snapshot Isolation (RCSI), be very careful or you may miss some rows. Yesterday, Kendra Little talked a bit about Lost Updates under RCSI. It’s a minor issue that can pop up after turning on RCSI as the default behavior for the Read Committed isolation level. But she doesn’t want to dissuade you from considering the option and I agree with that advice. In fact, even though we turned RCSI on years ago, by a bizarre coincidence, we only came across our first RCSI-related iss ..read more

When I deploy database changes, I like my scripts to be quick, non-blocking, rerunnable and resumable. I’ve discovered that: Turning on Resource Governor is quick and online Turning off Resource Governor is quick and online Cleaning or removing configuration is easy Modifying configuration may take some care Turning on Resource Governor Just like sp_configure, Resource Governor is configured in two steps. The first step is to specify the configuration you want, the second step is to ALTER RESOURCE GOVERNOR RECONFIGURE. But unlike sp_configure which has a “config_value” column and a “run_valu ..read more

What’s the best way to avoid most blocking issues in SQL Server? Turn on Read Committed Snapshot Isolation (RCSI). That’s it. Configuring RCS isolation level To see if it’s enabled on your database, use the is_read_committed_snapshot_on column in sys.databases like this: select is_read_committed_snapshot_on from sys.databases where database_id = db_id(); To enable the setting alter the database like this: ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON Is it that easy? Kind of. For the longest time at work, we ran our databases with this setting off. Mostly because that’s the default ..read more

Yes, It turns out that you can specify two indexes in a table hint: SELECT Id, Reputation FROM dbo.Users WITH (INDEX (IX_Reputation, PK_Users_Id)) WHERE Reputation > 1000 And SQL Server obeys. It uses both indexes even though the nonclustered index IX_Reputation is covering: But Why? I think this is a solution looking for a problem. Resolving Deadlocks? My team wondered if this could be used as to help with a concurrency problem. We recently considered using it to resolve a particular deadlock but we had little success. It’s useful to think that SQL Server takes locks on index rows inste ..read more

Using the right triangle above draw a vertical line separating the area of the triangle in to two parts with the same area. The triangle on the left is 70.7% of the width of the original triangle. Cumulative Storage Costs Think of this another way. The triangle above is a graph of the amount of data you have over time. And if you pay for storage as an operational expense such as when you’re renting storage in the cloud (as opposed to purchasing physical drives). Then the cost of storage is the area of the graph. The monthly bills are ever-increasing, so half of the total cost of storage will ..read more

When I wrote Take Care When Scripting Batches, I wanted to guard against a common pitfall when implementing a batching solution (n-squared performance). I suggested a way to be careful. But I knew that my solution was not going to be universally applicable to everyone else’s situation. So I wrote that post with a focus on how to evaluate candidate solutions. But we developers love recipes for problem solving. I wish it was the case that for whatever kind of problem you got, you just stick the right formula in and problem solved. But unfortunately everyone’s situation is different and the major ..read more

I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps. How does SQL Server use the Windows registry? First, it’s useful to know a bit about how SQL Server uses the registry. We can watch registry activity using Process Monitor (procmon). On a fairly quiet local machine, I see these SQL Server processes “querying” registry keys: There is some background process reading Query Store settings (every minute). HKLM ..read more

Just like PIVOT syntax, UNPIVOT syntax is hard to remember. When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS. CREATE OR ALTER FUNCTION dbo.GenerateUnpivotSql (@Sql NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN RETURN ' WITH Q AS ( SELECT TOP (1) ' + ( SELECT STRING_AGG( CAST( 'CAST(' + QUOTENAME(NAME) + ' AS sql_variant) AS ' + QUOTENAME(NAME) AS NVARCHAR(MAX) ), ', ' ) FROM sys.dm_exec_describe_first_result_set(@sql ..read more

System procedures like sp_replincrementlsn and system functions like fn_cdc_get_min_lsn and fn_cdc_get_max_lsn return values that are of type binary(10). These values represent LSNs, Log Sequence Numbers which are an internal way to represent the ordering of transaction logs. Typically as developers, we don’t care about these values. But when we do want to dig into the transaction log, we can do so with sys.fn_dblog which takes two optional parameters. These parameters are LSN values which limit the results of sys.fn_dblog. But the weird thing is that sys.fn_dblogis a function whose LSN parame ..read more