I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server. There are a few stored procedures that execute a pass-through against the linked server. Once in a while, a few sessions hang at the very first DML statement of the entire procedure (SELECT) against the linked server, never get any result back, and hang indefinitely with the OLEDB wait type.
Upon some testing, I found that executing a very simple statement in multiple sessions against it causes a lock up of one or more sessions. A simple statement being anything that uses linked server name like...
SELECT OrderNo FROM OPENQUERY(Linked_Server, 'SELECT OrderNo FROM Orders WHERE OrderNo IS NULL');
EXEC ('SELECT OrderNo FROM Orders WHERE OrderNo IS NULL') AT Linked_Server
The strange things are that...
- One of the sessions always finishes
- The hang sessions usually successfully finish some random number of batches (usually below 10) before it freezes
- With the same connection strings, executing the same SQL statement (without OPENQUERY because it's directly against the linked server over ODBC) in a different environment, such as VBScript using ADO, has no issues, even when the number of concurrent sessions is increased (e.g. 10 sessions w/300 executions each instead of 2 sessions w/100 executions).
- Profiler shows no error. All hang sessions just stop at one same method IDBInitialize::Initialize with no further processing.
- Sage side logs also show no errors or anything out of ordinary. They look normal until the point of freezing, after which the logs look as if the Sage's ODBC driver never received the query from the hung sessions.
- I can replicate lock ups at will
I researched and found this page SQL – Openquery (1) Use It Or Not Use It. Because ADO has no issue, and for what's explained in the linked page, I have to deduce that this must be caused by some limitations of linked server. But I can't find any documentations on that at all.
So, here are the questions.
- Are there unofficial limitations to Linked Server, like the number of connections or the total size of ALL requests/result and such?
- If not, what could be the potential causes of it?
- Lastly, is this a bug in 2008, and not in the newer versions?