Yes, It turns out that you can specify two indexes in a table hint:
SELECT Id, Reputation
FROM dbo.UsersWITH(INDEX(IX_Reputation, PK_Users_Id))WHERE Reputation >1000
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 instead of table rows. And so the idea we had was that perhaps taking key locks on multiple indexes can help control the order that locks are taken. But after some effort, it didn’t work at avoiding deadlocks. For me, I’ve had better luck using the simpler sp_getapplock.
Forcing Index Intersection?
Brent Ozar wrote about index intersection a while ago. Index intersection is a rare thing to find in a query plan. Brent can “count on one hand the number of times [he’s] seen this in the wild”.
In theory, I could force index intersection (despite the filter values):
SELECT Id
FROM dbo.UsersWITH(INDEX(IX_UpVotes, IX_Reputation))WHERE Reputation >500000AND UpVotes >500000
SELECT Id
FROM dbo.Users WITH (INDEX (IX_UpVotes, IX_Reputation))
WHERE Reputation > 500000
AND UpVotes > 500000
But I wouldn’t. SQL Server choosing index intersection is already so rare. And so I think the need to force that behavior will be even rarer. This is not a tool I would use for tuning queries. I’d leave this technique alone.
Have You Used More Than One Index Hint?
I’d love to hear about whether specifying more than one index in a table hint has ever helped solve a real world problem. Let me know in the comments.
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 be for the most recent 29%.
Put yet another way: If you started creating a large file in the cloud every day since March 2014, then the amount you paid to the cloud provider before the pandemic started is the same amount you paid after the pandemic started (as of August 2022).
How Sustainable is This?
If the amount of data generated a day isn’t that much, or the storage you’re using is cheap enough then it really doesn’t matter too much. As an example, AWS’s cheapest storage, S3 Glacier Deep Archive, works out to about $0.001 a month per GB.
But if you’re using Amazon’s Elastic Block Storage like the kind of storage needed for running your own SQL Servers in the cloud, the cost can be closer to $.08 a month per GB.
The scale on the triangle graph above really matters.
Strategies
This stresses the need for a data life-cycle policy. An exit story for large volumes of data. Try to implement Time-To-Live (TTL) or clean up mechanisms right from the beginning of even the smallest project. Here’s one quick easy example from a project I wrote that collects wait stats. The clean-up is a single line.
Look at Netflix does approaches this issue. I like how they put it. “Data storage has a lot of usage and cost momentum (i.e. save-and-forget build-up).”
Netflix stresses the importance of “cost visibility” and they use that to offer focused recommendations for cleaning up unused data. I recommend reading that whole article. It’s fascinating.
It’s important to implement such policies before that triangle graph gets too large.
Takeaway: I look at different features to see whether non-updates are treated the same as other updates. Most of the time they are.
According to Microsoft’s documentation, an UPDATE statement “changes existing data in a table or view”. But what if the values don’t actually change? What if affected rows are “updated” with the original values? Some call these updates non-updating. This leads to a philosophical question: “If an UPDATE statement doesn’t change any column to a different value, has the row been updated?”
I answer yes to that question. I consider all affected rows as “updated” regardless of whether the values are different. I think of the UPDATE statement as more of an OVERWRITE statement. I also think of “affected rows” rather than “changed rows”. In most cases SQL Server thinks along the same lines.
I list some features and areas of SQL Server and whether non-updating updates are treated the same or differently than other updates:
The Performance of Non-Updates Non-Updates treated differently than other Updates
In 2010, Paul White wrote The Impact of Non-Updating Updates where he points out optimizations Microsoft has made to avoid unnecessary logging when performing some non-updating updates. It’s a rare case where SQL Server actually does pay attention to whether values are not changing to avoid doing unnecessary work.
In the years since, I’ve noticed that this optimization hasn’t changed much except that Microsoft has extended these performance improvements to cases where RCSI or SI is enabled.
Regardless of this performance optimization, it’s still wise to limit affected rows as much as possible. In other words, I still prefer
UPDATE FactOnlineSales
SET DiscountAmount =NULLWHERE CustomerKey =19036AND DiscountAmount ISNOTNULL;
UPDATE FactOnlineSales
SET DiscountAmount = NULL
WHERE CustomerKey = 19036
AND DiscountAmount IS NOT NULL;
over this logically equivalent version:
UPDATE FactOnlineSales
SET DiscountAmount =NULLWHERE CustomerKey =19036;
UPDATE FactOnlineSales
SET DiscountAmount = NULL
WHERE CustomerKey = 19036;
Although the presence of triggers and cascading foreign keys require extra care as we’ll see.
Triggers Non-Updates are treated the same as Updates
Speaking of triggers, remember that inside a trigger, non-updating rows are treated exactly the same as any other changing row. Just remember that:
Triggers are always invoked, even when there are zero rows affected or even when the table is empty.
For UPDATE statements, the UPDATE() function only cares about whether a column appeared in the SET clause. It can be useful for short-circuit logic.
The virtual tables inserted and deleted are filled with all affected rows (not just changed rows).
ON UPDATE CASCADE Non-Updates are treated the same as Updates
When foreign keys have ON UPDATE CASCADE set, Microsoft says “corresponding rows are updated in the referencing table when that row is updated in the parent table”.
Non-updating updates are no exception. To demonstrate, I create an untrusted foreign key and perform a non-updating update. It’s not a “no-op”, the constraint is checked as expected.
CREATETABLE dbo.TestReferenced(
Id INTPRIMARYKEY);
INSERT dbo.TestReferenced(Id)VALUES(1), (2), (3), (4);
CREATETABLE dbo.TestReferrer(
Id INTNOTNULL);
INSERT dbo.TestReferrer(Id)VALUES(2), (4), (6), (8);
ALTERTABLE dbo.TestReferrerWITHNOCHECKADDFOREIGNKEY(Id)REFERENCES dbo.TestReferenced(Id)ONUPDATECASCADE;
-- trouble with this non-updating update:UPDATE dbo.TestReferrerSET Id = Id
WHERE Id =8;
-- The UPDATE statement conflicted with the FOREIGN KEY constraint ...
CREATE TABLE dbo.TestReferenced (
Id INT PRIMARY KEY
);
INSERT dbo.TestReferenced (Id) VALUES (1), (2), (3), (4);
CREATE TABLE dbo.TestReferrer (
Id INT NOT NULL
);
INSERT dbo.TestReferrer (Id) VALUES (2), (4), (6), (8);
ALTER TABLE dbo.TestReferrer
WITH NOCHECK ADD FOREIGN KEY (Id)
REFERENCES dbo.TestReferenced(Id)
ON UPDATE CASCADE;
-- trouble with this non-updating update:
UPDATE dbo.TestReferrer
SET Id = Id
WHERE Id = 8;
-- The UPDATE statement conflicted with the FOREIGN KEY constraint ...
@@ROWCOUNT Non-Updates are treated the same as Updates
SELECT @@ROWCOUNT returns the number of affected rows in the previous statement, not the number of changed rows.
Temporal Tables Non-Updates are treated the same as Updates
Non-updating updates still generate new rows in the history table. This can lead to puzzling results if you’re not prepared for them. For example, I can make some changes and query the history like this:
INSERT MyTest(Value) VALUES ('Mike')
UPDATE MyTest SET Value = 'Michael';
UPDATE MyTest SET Value = 'Michael';
When I take the union of rows in the table and the history table, I might see this output:
It reminds me of when my GPS says something like “In two miles, continue straight on Highway 81.” The value didn’t change, but there are still two distinct ranges.
Change Tracking Non-Updates are treated the same as Updates
Change tracking could be called “Overwrite Tracking” because all non-updating updates are tracked:
ALTERDATABASECURRENTset change_tracking =ON(change_retention =2 days, auto_cleanup =on);
GO
createtable dbo.test(id intprimarykey);
insert dbo.test(id)values(1), (2), (3);
altertable dbo.test enable change_tracking with(track_columns_updated =on)-- This statement produces 0 rows:SELECT t.id, c.*FROM CHANGETABLE (CHANGES dbo.Test, 0)AS c
JOIN dbo.TestAS t ON t.id= c.id ;
-- "update"update dbo.testset id = id;
-- This statement produces 3 rows:SELECT t.id, c.*FROM CHANGETABLE (CHANGES dbo.Test, 0)AS c
JOIN dbo.TestAS t ON t.id= c.id;
ALTER DATABASE CURRENT
set change_tracking = ON
(change_retention = 2 days, auto_cleanup = on);
GO
create table dbo.test (id int primary key);
insert dbo.test (id) values (1), (2), (3);
alter table dbo.test enable change_tracking with (track_columns_updated = on)
-- This statement produces 0 rows:
SELECT t.id, c.*
FROM CHANGETABLE (CHANGES dbo.Test, 0) AS c
JOIN dbo.Test AS t ON t.id = c.id ;
-- "update"
update dbo.test set id = id;
-- This statement produces 3 rows:
SELECT t.id, c.*
FROM CHANGETABLE (CHANGES dbo.Test, 0) AS c
JOIN dbo.Test AS t ON t.id = c.id;
Change Data Capture (CDC) Non-Updates treated differently than other Updates
Here’s a rare exception where a SQL Server feature is named properly. CDC does indeed capture data changes only when data is changing.
Paul White provided a handy set up for testing this kind of stuff. I reran his tests with CDC turned on. I found that:
When CDC is enabled, an update statement is always logged and the data buffers are always marked dirty.
But non-updating updates almost never show up as captured data changes, not even when the update was on a column in the clustering key.
I was able to generate some CDC changes for non-updates by updating the whole table with an idempotent expression (e.g. SET some_column = some column * 1)
CREATE TABLE dbo.SomeTable
(
some_column integer NOT NULL,
some_data integer NOT NULL,
index ix_sometable unique clustered (some_column)
);
UPDATE dbo.SomeTable SET some_column = some_column*1;
If you’re using this feature, this kind of stuff is important to understand! If you’re using CDC for DIY replication (God help you), then maybe the missing non-updates are acceptable. But if you’re looking for a kind of audit, or a way to analyze user-interactions with the database, then CDC doesn’t give the whole picture and is not the tool for you.
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\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\QueryStoreSettings
There is also some background process writing uptime info (every minute). HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\uptime_pid
When a login is requested from a new connection, SQL Server will check to see if R Services are installed (aka Advanced Analytics).
SQL Server will check SERVERPROPERTY('IsAdvancedAnalyticsInstalled') every time to see if it has to care about logins associated with something called implied authentication. This happens on every login which will be important later. HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup\AdvancedAnalytics
If I use a function like HASHBYTES, SQL Server looks up some cryptography settings. These settings get queried only on the first call to HASHBYTES in each session.
e.g. HKLM\SOFTWARE\Microsoft\Cryptography\Defaults\Provider\Microsoft Enhanced RSA and AES Cryptographic Provider
That’s not an exhaustive list, there are many other ways SQL Server uses the Windows Registry. For example:
Many SQL Agent settings are stored there and are read regularly
xp_regread coming from using wizards in SQL Server Management Studio.
SERVERPROPERTY(N'MachineName') gets its info from HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\Hostname
And many others.
What happens when the Windows registry is slow?
SQL Server’s use of the registry can be fairly quiet – even on a busy server – so you may not see any symptoms at all. But if the calls to the registry are slow in responding, here is what you might see:
New logins will ask whether Advanced Analytics Extensions is installed. Leading to a non-yielding scheduler and a memory dump. With some effort, you might find a stack trace like the one in the appendix below.
Any other kind of memory dump caused by non-yielding schedulers in which the saved stack trace ends with ntdll!NtOpenKeyEx. The AdvancedAnalytics is just one example but it’s the most common because it’s executed first on each login.
Queries calling HASHBYTES (or other cryptography functions) will be suspended and wait with PREEMPTIVE_OS_CRYPTACQUIRECONTEXT. I mostly see this when the login checks are skipped i.e. when an open connection from a connection pool is used.
Another symptom is Availability Group failovers (allegedly). It’s harder (for me) to do AG failover post mortems and tie them definitively to slow Windows registries
Why might the registry be slow?
I’m not sure. Perhaps it’s associated with some registry cleanup process. It may have something to do with an IO spike on the C: drive.
We rebuilt a virtual machine image from scratch which seems to avoid the problem. I’m keeping my fingers crossed.
I’d love to hear if you’ve come across anything similar.
Appendix: Sample call stack for non-yielding scheduler
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.
CREATEORALTERFUNCTION dbo.GenerateUnpivotSql(@SqlNVARCHAR(MAX))RETURNSNVARCHAR(MAX)ASBEGINRETURN'
WITH Q AS
(
SELECT TOP (1) '+(SELECT
STRING_AGG(CAST('CAST('+QUOTENAME(NAME)+' AS sql_variant) AS '+QUOTENAME(NAME)ASNVARCHAR(MAX)), ',
')FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT))+'
FROM (
'+ @sql+'
) AS O
)
SELECT U.FieldName, U.FieldValue
FROM Q
UNPIVOT (FieldValue FOR FieldName IN ('+(SELECT STRING_AGG(CAST(QUOTENAME(name)ASNVARCHAR(MAX)), ',
')FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT))+'
)) AS U';
END
GO
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, DEFAULT, DEFAULT)
) + '
FROM (
' + @sql + '
) AS O
)
SELECT U.FieldName, U.FieldValue
FROM Q
UNPIVOT (FieldValue FOR FieldName IN (' +
(
SELECT STRING_AGG( CAST( QUOTENAME(name) AS NVARCHAR(MAX) ), ',
' )
FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT)
) + '
)) AS U';
END
GO
And you might use it like this:
declare @sqlnvarchar(max)='SELECT * FROM sys.databases WHERE database_id = 2';
declare @newsql nvarchar(max)= dbo.GenerateUnpivotSql(@sql);
execsp_executesql @sql;
execsp_executesql @newsql;
I find this function useful whenever I want to take a quick look at one row without all that horizontal scrolling. Like when looking at sys.dm_exec_query_stats and other wide dmvs. This function is minimally tested, so caveat emptor.
I like rowversion columns, I like that they’re system-maintained and they provide a unique deterministic way to compare or order changes in a database. But they’re not timestamps (despite the alias).
I also like datetime2 columns which are called LastModifiedDate. They can indicate the date and time that a row was last modified. But I have to take care of maintaining the column myself. I either have to remember to update that column on every update or I have to use something like a trigger with the associated overhead.
But maybe there’s another option.
GENERATED ALWAYS AS ROW START
What if I use the columns that were meant to be used for temporal tables, but leave SYSTEM_VERSIONING off?
CREATETABLE dbo.Test(
Id INTIDENTITYNOTNULL,
ValueVARCHAR(100)NOTNULL,
LastModifiedDate DATETIME2 GENERATED ALWAYS ASROWSTARTNOTNULL,
SysEndTime DATETIME2 GENERATED ALWAYS ASROWEND HIDDEN NOTNULL,
PERIOD FOR SYSTEM_TIME (LastModifiedDate, SysEndTime),
CONSTRAINT PK_Test PRIMARYKEYCLUSTERED(Id))
CREATE TABLE dbo.Test
(
Id INT IDENTITY NOT NULL,
Value VARCHAR(100) NOT NULL,
LastModifiedDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (LastModifiedDate, SysEndTime),
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (Id)
)
It’s system maintained, it’s an actual datetime and I don’t have to worry about the overhead of triggers.
But it’s not a perfect solution:
SysEndTime is required but it’s an unused column here. It’s needed to define the period for SYSTEM_TIME and it’s always going to be DATETIME2’s maximum value. That’s why I made that column “hidden”. It’s got an overhead of 8 Bytes per row.
The value for LastModifiedDate will be the starting time of the transaction that last modified the row. That might lead to confusing behaviors illustrated by this example. Say that:
Transaction A starts
Transaction B starts
Transaction B modifies a row
Transaction A modifies the same row
After all that, the last modified date will indicate the time that transaction A starts. In fact if I try these shenanigans with system versioning turned on, I get this error message when transaction A tries to modify the same row:
Msg 13535, Level 16, State 0, Line 16
Data modification failed on system-versioned table ‘MyDb.dbo.Test’ because transaction time was earlier than period start time for affected records.
Everything is tradeoffs. If you can live with the drawbacks of the system-generated last modified date column, then it might be an option worth considering.
I have trouble with procedures that use SELECT *. They are often not “Blue-Green safe“. In other words, if a procedure has a query that uses SELECT * then I can’t change the underlying tables can’t change without causing some tricky deployment issues. (The same is not true for ad hoc queries from the application).
I also have a lot of procedures to look at (about 5000) and I’d like to find the procedures that use SELECT *.
I want to maybe ignore SELECT * when selecting from a subquery with a well-defined column list.
I also want to maybe include related queries like OUTPUT inserted.*.
The Plan
So I’m going to make a schema-only copy of the database to work with.
I’m going to add a new dummy-column to every single table.
I’m going to use sys.dm_exec_describe_first_result_set_for_object to look for any of the new columns I created
Any of my new columns that show up, were selected with SELECT *.
The Script
use master;
DROPDATABASEIFEXISTS search_for_select_star;
DBCC CLONEDATABASE (the_name_of_the_database_you_want_to_analyze, search_for_select_star);
ALTERDATABASE search_for_select_star SET READ_WRITE;
GO
use search_for_select_star;
DECLARE @SQLNVARCHAR(MAX);
SELECT
@SQL= STRING_AGG(CAST('ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+QUOTENAME(OBJECT_NAME(object_id))+' ADD NewDummyColumn BIT NULL'ASNVARCHAR(MAX)),
N';')FROM
sys.tables;
execsp_executesql @SQL;
SELECT
SCHEMA_NAME(p.schema_id)+'.'+ p.nameAS procedure_name,
r.column_ordinal,
r.nameFROM
sys.procedures p
CROSS APPLY
sys.dm_exec_describe_first_result_set_for_object(p.object_id, NULL) r
WHERE
r.name='NewDummyColumn'ORDERBY
p.schema_id, p.name;
use master;
DROPDATABASEIFEXISTS search_for_select_star;
use master;
DROP DATABASE IF EXISTS search_for_select_star;
DBCC CLONEDATABASE (the_name_of_the_database_you_want_to_analyze, search_for_select_star);
ALTER DATABASE search_for_select_star SET READ_WRITE;
GO
use search_for_select_star;
DECLARE @SQL NVARCHAR(MAX);
SELECT
@SQL = STRING_AGG(
CAST(
'ALTER TABLE ' +
QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) +
'.' +
QUOTENAME(OBJECT_NAME(object_id)) +
' ADD NewDummyColumn BIT NULL' AS NVARCHAR(MAX)),
N';')
FROM
sys.tables;
exec sp_executesql @SQL;
SELECT
SCHEMA_NAME(p.schema_id) + '.' + p.name AS procedure_name,
r.column_ordinal,
r.name
FROM
sys.procedures p
CROSS APPLY
sys.dm_exec_describe_first_result_set_for_object(p.object_id, NULL) r
WHERE
r.name = 'NewDummyColumn'
ORDER BY
p.schema_id, p.name;
use master;
DROP DATABASE IF EXISTS search_for_select_star;
Update
Tom from StraightforwardSQL pointed out a nifty feature that Microsoft has already implemented.
Not sure here, but doesn't dm_sql_referenced_entities.is_select_all achieve the same thing?
selectdistinct SCHEMA_NAME(p.schema_id)+'.'+ p.nameAS procedure_name
from sys.procedures p
cross apply sys.dm_sql_referenced_entities(
object_schema_name(object_id)+'.'+object_name(object_id), default) re
where re.is_select_all=1
select distinct SCHEMA_NAME(p.schema_id) + '.' + p.name AS procedure_name
from sys.procedures p
cross apply sys.dm_sql_referenced_entities(
object_schema_name(object_id) + '.' + object_name(object_id), default) re
where re.is_select_all = 1
Comparing the two, I noticed that my query – the one that uses dm_exec_describe_first_result_set_for_object – has some drawbacks. Maybe the SELECT * isn’t actually included in the first result set, but some subsequent result set. Or maybe the result set couldn’t be described for one of these various reasons
On the other hand, I noticed that dm_sql_referenced_entities has a couple drawbacks itself. It doesn’t seem to capture select statements that use `OUTPUT INSERTED.*` for example.
In practice though, I found the query that Tom suggested works a bit better. In the product I work most closely with, dm_sql_referenced_entities only missed 3 procedures that dm_exec_describe_first_result_set_for_object caught. But dm_exec_describe_first_result_set_for_object missed 49 procedures that dm_sql_referenced_entities caught!
Aaron Bertrand has a post called Use Caution with SQL Server’s MERGE Statement. It’s a pretty thorough compilation of all the problems and defects associated with the MERGE statement that folks have reported in the past. But it’s been a few years since that post and in the spirit of giving Microsoft the benefit of the doubt, I revisited each of the issues Aaron brought up.
Some of the items can be dismissed based on circumstances. I noticed that:
Some of the issues are fixed in recent versions (2016+).
Some of the issues that have been marked as won’t fix have been fixed anyway (the repro script associated with the issue no longer fails).
Some of the items are complaints about confusing documentation.
Some are complaints are about issues that are not limited to the MERGE statement (e.g. concurrency and constraint checks).
So what about the rest? In what circumstances might I decide to use a MERGE statement? What do I still need to avoid? In 2019 and later, if I’m using MERGE, I want to avoid:
It’s a shorter list than Aaron’s but there’s another gotcha. The same way that some of the items get addressed with time, new issues continue to pop up. For example, temporal tables are a relatively new feature that weren’t a thing when Aaron’s first posted his list. And so I also want to avoid:
If MERGE has trouble with old and new features , then it becomes clear that MERGE is a very complicated beast to implement. It’s not an isolated feature and it multiplies the number of defects that are possible.
Severity of Issues
There’s a large number of issues with a large variety of severity. Some of the issues are minor annoyances, or easily avoidable. Some of them are serious performance issues that are harder to deal with. But a few of the issues can be worse than that! If I ask SQL Server to UPDATE something, and SQL Server responds with (1 row affected) , then it better have affected that row! If it didn’t, then that’s a whole new level of severity.
That leads me to what I think is the worst, unfixed bug. To be safe from it, avoid MERGEs with:
The defect is called Merge statement Delete does not update indexed view in all cases. It’s still active as of SQL Server 2019 CU9 and it leaves your indexed view inconsistent with the underlying tables. For my case, I can’t predict what indexed views would be created in the future, so I would shorten my advice to say avoid MERGEs with:
When I first started writing this post, I thought the gist was going to be “MERGE isn’t so bad if you just avoid these things”. But it still is bad. Aaron’s list is a little out of date but his advice is not. If Aaron updated that post today, the list of defects and issues with MERGE would be different and just as concerning to me as the day he wrote it.
Takeaway: For most use cases, using sp_releaseapplock is unnecessary. Especially when using @LockOwner = 'Transaction (which is the default).
The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.
But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).
But there’s a problem with this pattern, especially when using RCSI. After sp_releaseapplock is called, but before the COMMIT completes, another process running the same code can read the previous state. In the example above, both processes will think a time slot is available and will try to make the same reservation.
What I really want is to release the applock after the commit. But because I specified the lock owner is 'Transaction'. That gets done automatically when the transaction ends! So really what I want is this:
BEGINTRANexecsp_getapplock
@Resource = @LockResourceName,
@LockMode ='Exclusive',
@LockOwner ='Transaction';
/* read stuff (e.g. "is time slot available?") *//* change stuff (e.g. "make reservation") */COMMIT-- all locks are freed after this commit
BEGIN TRAN
exec sp_getapplock
@Resource = @LockResourceName,
@LockMode = 'Exclusive',
@LockOwner = 'Transaction';
/* read stuff (e.g. "is time slot available?") */
/* change stuff (e.g. "make reservation") */
COMMIT -- all locks are freed after this commit
I sometimes get frustrated with SQL Server. It’s a tricky and challenging product to work with and some days I feel like I’ve only scratched the surface.
But sometimes I’m really impressed with what it can do. It really is a good (if expensive) product.
With the increase in online learning, last week we broke a record for a single SQL Server. 254,000 batch requests per second! That’s the first time we’ve pushed it above the quarter million mark.
(The screen shot was from a day earlier, but you get the idea)
I’d like to hear from others that have reached these kinds of numbers in prod to talk about their experiences. Find me on twitter (@MJSwart).
Find Out More About Our Experience
I’ll be presenting to the Ohio North User Group this February 2nd called How High Can We Scale SQL Server? Feel free to sign up!
I have fond memories of presenting to folks in Cleveland. Why they host their SQL Saturday’s in February is still a mystery. And so I’m glad to present virtually this February to keep up my streak.
