none
Package performance on different servers head scratcher

    Question

  • Hi there ... can anyone offer any thoughts on what could be the cause of this issue, as I've been bashing my head on it all week ?

    Situation is ...
    - package is meant to load raw data into initial tables, then process through some staging tables to a final master table
    - on my local dev environment, which is two virtuals, one with Visual Studio, the other with SQL Server, package works fine, loads test file of 35K records in about 5 minutes
    - pushed package up to the team test environment, again two virtuals, try DTExec run with same test data and after over an hour, package is just sat there ...
    - the initial load and staging steps have worked, but the master update isn't happening
    - someone suggested network latency, so I moved everything to the single virtual with SQL ... no change
    - tried a binary chop on the data and found it works with 1044 records, but blocks with 1045 records !!
    - ran the package in Visual Studio and the 1045 worked
    - ran full test data in VS and the master update sits at 9947 records ... and when I came in this morning, it was still sat there blocked !!

    If I run a query on the various tables whilst the package is blocked, most run a count, but one table block SSMS
    - but as soon as I stop Visual Studio debug, the SSMS query returns

    Ok I get there is something like a dead lock occurring in the database, but my SQL understanding isn't good enough ...
    - how can I track down what and where the lock is ?
    - and why does the package work fine on my dev environment, but lock on the test environment, which is a bigger box ??
    Thursday, July 09, 2009 9:07 AM

Answers

  • There is resource contention somewhere. To check the locks you can use the SSMS. Go to:

    Management -> Activity Monitor

    right-click and try "View Locks by Object" or "View Locks by Process"

    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    • Marked as answer by steveculshaw Friday, July 10, 2009 8:25 AM
    Thursday, July 09, 2009 7:51 PM

All replies


  • - how can I track down what and where the lock is ?

    Profiler is a great place to start. Here is an article for you: http://msdn.microsoft.com/en-us/library/ms188246.aspx

    - and why does the package work fine on my dev environment, but lock on the test environment, which is a bigger box ??

    Well... you have two different environments, with different loads and presumably more users. Just because the test server is  bigger - it does not mean it will perform better than your dev environment.

    My guess is if you get your deadlock issue fixed, you will be on the way to a solution.

    JP

    Please click the Mark as Answer button if a post solves your problem!
    Thursday, July 09, 2009 1:09 PM
  • There is resource contention somewhere. To check the locks you can use the SSMS. Go to:

    Management -> Activity Monitor

    right-click and try "View Locks by Object" or "View Locks by Process"

    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    • Marked as answer by steveculshaw Friday, July 10, 2009 8:25 AM
    Thursday, July 09, 2009 7:51 PM
  • Many thanks, that did it ...

    Looked at the Activity Monitor, spotted lots of TRANSACTION against my database .. which got me thinking
    Read up on the OLE DB connector, unselected the TABLE LOCK and WHAM ... data loads straight through   :-)

    Why did it work on my laptop d/b, but not on the test box ... which at the time only had me working on it ?

    Again many thanks
    Cheers, SteveC.
    Friday, July 10, 2009 8:28 AM