SQL Server: Remote queries on views

I spent most of Friday testing out different methods for retrieving data using remote queries with SQLserver.

Remote queries are of the form

select ... from databaseServer2.databaseName.TableOwner.TableName JOIN ...

One of the limitations of remote queries is you can't use TABLE HINTS like (NOLOCK) on remote queries. Given that this query call was for a report which doesn't require uncommitted data that is a bit of a bummer. The report aggregates some log data on a table which is getting constant inserts, so the inability to read the data whilst not effecting the constant writes was causing LOCK contention.

The first thought was the wrap the table in a view on the remote server something like this

create view uv_logtable as select from logtable (NOLOCK)

Unfortunately it would seem that remote queries and views are not a good option if you want the SQLserver execution plan to be any good. The execution plan when there is a view involved was very ordinary. Queries which had a 4 sec response time blew out to more than 4 mins, in some causes much longer. I am going to test some more and determine what the rules are.

The final thought was to take the stored procedure which runs the report and port to the remote server i.e. adding remote queries for the other data. This worked well, especially considering that most of the work was being down on the remote server in that report.
The added benefit was that I could add NOLOCK hints to the now local tables and amount of lock requests (tracked from perfmon) was stable compared to the current setup.

I will update after we go through our Monday peak period as to whether this was a success.

Have Fun

Paul

Update: Monday was fine, Tuesday was back to 100% CPU!!! Unfortunately I had missed adding a NOHINT to the local table for the report stored procedure. At one point we had 500,000+ lock requests per sec. Once I fixed the stored procedure, it took 2-3 minutes for the CPU to go back to 4-5%!
That massive CPU load caused by lock contention is a demostration of the problem SQLserver has with providing the ACID transactional model, by using shared locks to enable consistent reads. More in this post.