Grant Fritchey (7/29/2014)
The issue, whatever is causing the slowdown, could very well be within SQL Server. Absolutely. But the timeout itself is controlled at the application connection setting.So, to troubleshoot this, I wouldn't focus on the timeout. Instead, I'd set up monitoring on the server and start capturing long running queries, say ones that exceed 25 seconds, or long running transactions, same setting, in order to identify where the problem is coming from. You're on 2014, so the very best way to get this done is using extended events.
That is what I began to do; however, I noticed that the queries, when run directly from SQL, are running in less than a second. This is what has me confused. Am I missing some small detail in how SQL sends the data back?