Does SQL Server 2k5 preserve locking hints over linked servers?

Question

I've been nosing around--and I'm no DBA myself,--but became curious when trying to troubleshoot a particularly expensive (and infrequent) query that performs an outer join on a linked server (2k5 to 2k5, different dbs on different physical machines). The issue doesn't generally seem to be execution time, but rather locking, as the outer join is against a pretty massively read from/written to db, and was wondering if adding a NOLOCK hint to the join statement would be a futile gesture.

I've already found this MSDN Social link (and checked for existing SO questions), but the moderators guess one thing (that hints are not passed over the wire), and the asker later empirically states that he's seeing the hints preserved. I figured SO would help me find a more definitive answer!

Answer 1

Good question, and I am going to go ahead and not actually answer it and say that you should consider setting the READ_COMMITTED_SNAPSHOT setting to ON in your databases. This will prevent transactions which use the READ_COMMITTED isolation level from blocking by default. You'll want to read up on the setting before you set it, though, as you need to understand what your load profile is like, and your DBA will have to make sure tempdb is sized accordingly.

Answer 2

I just did a test on this in the scenario you describe (2k5 to 2k5, different dbs on different physical machines) using a NOLOCK hint, with the SQL Profiler running on the machine to which the linked server points. The trace shows that the hint is preserved in the query executed on the linked server.

I ran

select 1 from server.master.dbo.sysobjects with (nolock)

and found the following in the trace:

declare @p1 int
set @p1=4
exec sp_prepexec @p1 output,NULL,N'SELECT (1) "Expr1003" FROM "master"."dbo"."sysobjects" "Tbl1002" WITH (NOLOCK)'
select @p1

I suspect this is a feature of the SQLNCLI driver, new with SQL 2005, since hints certainly weren't preserved in linked server queries in SQL 2000.

It may be worth trying a NOLOCK hint in your query, although you need to be aware that you are likely be getting dirty data from rows which are being updated as the query executes. This may or may not be a problem, depending on how your data is used.