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?
Best Answer
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.