0

I'm investigating some performance issues for our BI team. They have a package which runs extremely slow and will sometimes run indefinitely on one instance but finishes normally on another instance. Both instances reside on the same server and both are using the same package. I've tried collecting PerfMon counters which show Page Life Expectency well bellow 300 and eventually reaching 0.

I'm also seeing ASYNC_NETWORK_IO wait types when I use Activity Monitor to see what is occurring. The ASYNC_NETWORK_IO occurs when it tries to execute a stored proc which I've scripted out and am able to run to completion in under 30 seconds.

I'm not quite sure where I should look for what is causing the Page Life Expectency to be so low or if I should be tracking down the ASYNC_NETWORK_IO waits.

These instances are running SQL Server 2008 R2 on a W2K8 R2 VM with 32 GB of ram.

What is the ideal troubleshooting path for two instances on the same server performing differently?

Improve this question
11
  • Are the configurations identical on both VMs? PLE dropping to zero indicates that memory pressure may be an issue as pages that are in memory are being flushed and not staying there.
    – Kris Gruttemeyer
    Oct 3, 2014 at 17:21
  • 1
    Could blocking be an issue?
    – Hannah Vernon ♦
    Oct 3, 2014 at 17:24
  • Sounds like it could be. ASYNC_NETWORK_IO means that SQL server is waiting for an application to pick up what it's placed in the output buffer and for the application to send acknowledgement back to SQL. That in and of itself could cause blocking.
    – Kris Gruttemeyer
    Oct 3, 2014 at 17:26
  • Is your package accesses data outside the box? It could be network issue. But most likely your package accesses a lot of rows in batch and can not utilize them fast. So the SQL Server waits for SSIS to read the actual recordset. This could be connected to available resources like CPU, memory or IO. How do you configure memory for both instances? I would check memory consumption on the server, using "Memory - Available bytes" and "Paging File % usage". I would also check what is going on at the time your package is running with sp_whoisactive or use dmv sys.dm_exec_requests, sys.dm_exec_session
    – yahor
    Oct 3, 2014 at 17:35
  • 2
    I'm inclined to believe you're waiting on the data to land in the destination table. Perhaps there is activity going on which is keeping the package from locking the target table. They could also be doing lots of dumb things so try posting a screenshot of the Control Flow and your data flows. Between the destination and the penultimate component, double click on the connector line and get a screenshot of your MetaData tab. Finally, double click the destination and give us a peek into that. Punch all that into the question if you would
    – billinkc
    Oct 3, 2014 at 20:06

1 Answer 1

Reset to default
0

It turns out this issue was due to a restore of a database from an incorrect media set. Since the databases were not exact copies, the package was performing differently on the second instance. After performing a backup with INIT and then restoring that database to the second instance, the package now performs the same on both instances.

Improve this answer

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.