We are experiencing an issue involving a stored procedure run multiple times on 2005 on two different servers. Some Background: Both servers are running 2005, sane service packs, os etc. Both machines are identical, one is used for UAT one PROD. The UAT server is smaller (12 CPU, 64 GB RAM), PROD is (24 CPU, 96GB RAM). The issue we are seeing that a certain transaction consisting of approx 150 procedure calls operates approximate 8 seconds faster on the smaller server than the Production server. The transaction is run from an ASP.NET connection from a Web server separated by only a firewall and two ethernet cables. The Connection is using MS Enterprise library to handle connecting and disconnecting.
The ASP.NET code, stored procedures, tables, indexes, stats are all the same. When the transaction is run, there is point where the same procedure is called 80 times in a row. Each time the procedure is executed, the connection connects, executes the procedure, then disconnects (I will list profiler stuff in a minute). What we are noticing is that on the production server, a single procedure call takes ~100 ms from login to logout. The same sequence on the smaller UAT server takes 10-15 ms. SO the difference of the 80-90 ms on each call, being called 80 times, is effectively the difference in the two queries, we have compared the traces from end to end and they are almost identical exempt for this sequence. I will show some profiler entries in next post.