7

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. InsertUpdateDelete

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. Executionbeginning

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.

4
  • 1
    It is definitely bad practice. OLAP and reporting generate large transient load spikes on the server. You would also tune the storage very differently. Feb 15, 2013 at 16:51
  • @ConcernedOfTunbridgeWells it certainly makes my life more difficult.
    – Zane
    Feb 15, 2013 at 16:53
  • Running packages from within BIDS/SSDT adds an additional layer of slow to allow the debugger to function. Safe to assume the slow start is similar to packages from from the command line/SQL Agent? dtexec.exe /file mypackage /reporting V should provide some verbose logging
    – billinkc
    Feb 15, 2013 at 19:28
  • @billinkc I am actually running these packages as part of a scheduled job that runs regularly during the day and have the same problem.
    – Zane
    Feb 15, 2013 at 19:35

2 Answers 2

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.

0

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

1
  • No it is not. Also the problem is strictly in validation. The actual package as I said runs in seconds.
    – Zane
    Feb 25, 2013 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.