We are having a strange problem where a rather simple query times out from time to time on our corporate intranet. We have a an IIS web server which issues a query against our SQL server. The query is a simple one (1 table join, indexes are properly used) and usually it completes processing in well under a second. I have performed a query analysis on the statment, and cannot find any problems. Yet the the same query can return in under a second one time, and timeout (after 15 seconds) another time. I have two theories:
Theory 1. When analyzing the query, 65% of cpu time occurs on the bookmark lookup. My theory is that the server is not tuned correctly, and the tables being queried are sometimes not in the buffer space. Thus SQL server has to load the tables back into memory from time to time thus causing slow query execution.
Theory 2. One curious thing is that the timeouts usually occur when a user is accessing our web server via a slow VPN connection. However, the timeout is occurring between our IIS and our SQL server. If line speeds were an issue, I would think we would get a timeout between the browser and our web server. However, one theory is that a slow connection between our web server and the user's browser could somehow affect the query speed between our SQL server and IIS web server.
Any thoughts, theories, wild guesses, or magic spells would be appreciated.