Package not using shared memory

SSIS and Import/Export
1

I'm debugging a SSIS package running on SQL Server 2008 R2. It reads from a staging table and writes to one of two tables. New rows get written directly to a destination table; modified rows get written to an update table that is used later on to update the destination table.

While monitoring this package in action on a large staging table, I noticed that there are two active connections:

  1. to the staging table, which lives in a staging database on the same instance as the destination table.
  2. to the destination table, which is on the same instance as the staging databse but in a different database

I was surprised to see that the connections are using TCP instead of Shared Memory. Any clues as to why this might be and how to change it?

2

Is Shared Memory disabled on the server? Is the package running directly on the server via a job or similar?

From BOL:

The following names will connect to the local computer with the shared memory protocol if it is enabled:
servername
servername\instancename
(local)
localhost

Is it using one of those?

Verify the settings in the connection manager too.

3

Actually it's a FQDN like server.domain.com

4

That's probably why. Check this: https://technet.microsoft.com/en-us/library/ms187662(v=sql.105).aspx

5

Yes! That solved that bit! Now on to the next. With your fix in place (Thanks!!), shared memory is active. However I have a strange undetected deadlock.

The first connection, session id 71 (to the staging db) is in wait state:

PREEMPTIVE_OS_WAITFORSINGLEOBJEC

The second, session id 72 (writing to the target) is in:
LCK_M_X

and is awaiting the first task (waiting for 71)

Do you know how to determine what 71 is waiting for?

6

I don't, but that wait seems to be an external object and not within SQL. Check this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf2c509-568e-47fd-8413-67770ecda025/wait-stats?forum=sqldatabaseengine

7

Seems to be, yes, but the query is pretty simple:

SELECT * FROM Source_Staging.rivermine.vw_request_changes WHERE datestamp = '1/3/2016 7:05:50 PM';

Leaving aside for the moment that it shouldn't be 'SELECT *' (on my TODO list), there are no external references of any kind.

Like you, I found that article, but it was ultimately no help.

Fun Fact: It's still waiting, 20 minutes later. Nothing else of consequence running on the server at all

8

Is the object in that query a view or the actual table? I only ask because I have seen "vw" used as a prefix to indicate the object is a view. If that is a view the query may not be as simple as you think.

9

Actually I solved it. As you said, it had to do with a view. Basically the source is a view, but the view also joins on the destination table.

that's not an issue if the source is smaller than the OLEDB destination commit size, since SSIS drains the view (releasing any locks) before it gets there. When the source is larger than the commit size, SSIS buffers enough rows for the commit and tries to do the Inserts. However, it is still holding a Select lock on the source view (with the join on the destination table) while it tries to get an Exclusive lock on the destination table.

It's not detectable by SQL since the source task is waiting on completion of the thread doing the inserts, which is waiting to get an exclusive lock!

I solved it simply by writing the view results to a temp table first and using the temp table as my source for the data flow

1 Like