SSIS Validation slow. OLTP OLAP running on same server

Question

I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse and our OLTP database. The less powerful box is an 8 core 98 gigs of ram box that we use for ETL and our datastore. The problem I am having is on the production servers integration services takes a large amount of time to perform package validation. For a query that returns in 6 seconds it takes 40 seconds to complete package validation. As you can see it's a fairly simple package.

Keep in mind this is just the package I'm using as an example all of my packages have the same problem although some to a lesser degree. When I run this the validation portion takes 40 seconds on average.

In order to better test this I ran the exact same package on the sister box which runs all of the same SQL jobs as this box. Only to have the pre-execution validation take 0 seconds and the query take between 4 and 6 seconds.

To further test this I ran the query against both instances of SQL server.

SELECT '{' + CAST(Store_Id AS CHAR(36)) + '}' AS Store_Id
      ,'{' + CAST(ItemGroupDetail_Id AS CHAR(36)) + '}' AS ItemGroupDetail_Id
      ,ItemGroupType_Id
      ,'{' + CAST(Concept_Id AS CHAR(36)) + '}' AS Concept_Id
      ,'{' + CAST(Company_Id AS CHAR(36)) + '}' AS Company_Id
      ,'{' + CAST(ItemGroup_Id AS CHAR(36)) + '}' AS ItemGroup_Id
      ,GroupName
      ,Sort
      ,NameOverride
      ,'{' + CAST(ParentGroup_Id AS CHAR(36)) + '}' AS ParentGroup_Id
      ,Active
      ,'{' + CAST(UserCreated AS CHAR(36)) + '}'  AS UserCreated
      ,'{' + CAST(UserModified AS CHAR(36)) + '}'  AS UserModified
  FROM bos_vAllItemGroups

Execution on the main server with live up to date date returns 63326 rows in 5 seconds on average and the 50527 rows in 3 seconds. Which is to be expected this server has much less activity with the same specs I would expect it to be slightly faster. What I wouldn't expect is for package validation time to increase so much more than actual query run time.

After much digging yesterday I think I may have discovered the problem but am not sure how to prove it. The production OLTP database manages content for digital menu boards across the country that are calling home constantly to check for new content. Most of the time there is nothing to change and the queries to find out finish in milliseconds. However they do send about this process ends up running on average 4500 queries per second. Could this be the cause of my problem? The large volume of transactions coming from our web-server. If so how do I prove it? I have already been trying to get these databases running on separate machines as this OLTP database is growing very quickly as is our datawarehouse and from all my reading keeping your OLAP and OLTP databases on the same server is considered bad practice.

Answer 1

With some offline conversations, Zane was able to determine that if they could keep a connection open, it would hasten the validation.

To that end, I suggested they change the property on the Connection Manager to flip the RetainSameConnection property to True from its default.

Answer 2

Merge joins and sort are notoriously slow. Is it possible to write a query for the OLE Db source instead that has joins?