One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
It looks like this was the result of a corrupted ODBC entry for the production server. How it got corrupted on 3 separate test boxes so that they all had the same problem is beyond me.
I got to this by deductive hunch, and can't really say that any tool helped me definitively find the problem. I did use Process Monitor to see some differing activity between the 2 tests, but I couldn't figure out what the logs were trying to tell me.