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?