SELECT times out from program but works from SQL Server Management Studio

Question

I am getting a timeout when executing an SQL SELECT.

I connect to the database thus:

using (SQLConnection conn = SQLConnection(@"Data Source=mydbServer;Initial Catalog=mydb;Integrated Security=true;Timeout=180");
{
    conn.Open();

and that successfully connects.

Inside the using{} I set:

string query = @"SELECT a.field, a.timestamp " +
               "FROM mydb.dbo.myTable1 a WITH(NOLOCK) " +
               "LEFT JOIN [myOtherdbServer].myOtherdb.dbo.MyTable2 b WITH(NOLOCK) " +
               "ON a.field = b.field " +
               "WHERE b.field is NULL " +
               "AND a.timestamp >= '2015-05-01 00:00:00.000' " +
               "AND a.timestamp < '2015-06-01 00:00.00.000'";

and execute the command thus:

using (SQLCommand queryCmd = new SQLCommand(query, conn)
{
    queryCmd.CommandTimeout = 300;

    using (SQLDataReader rdr = queryCmd.ExecuteReader())
    { 

The SQLCommand throws a timeout exception: "The timeout period elapsed prior to the completion of the operation or the server is not responding".

If I use SQL Server Management Studio on the same system my program is running on, and as the same user under which my program is running, and execute the same SELECT, it completes in under 1 second and returns several thousand rows.

This is happening consistently now. It was working a couple of days ago. What should I be looking for? I'm baffled because the same SELECT works from SQL Server Management Studio. Does SQL SMS use a different connection?

Answer 1

"If I use SQL Server Management Studio on the same system my program is running on, and as the same user under which my program is running, and execute the same SELECT, it completes in under 1 second and returns several thousand rows."

1. There can be several reasons to why the execution time in SQL server management studio is better. For once, results are Cached, it may also very well be that you are lacking indexes on the timestamp column
2. In addition, is your application server located on the same server as you sql server? If not, this may increase latency and cause timeouts.
3. Linked servers may be an issue, except for latency considerations, I'm not sure the NOLOCK statement is sufficient to ensure what youre trying to achieve on the remote server. There's a good chance you may need to create a view on the remote server that contains the NOLOCK statement.

This is happening consistently now. It was working a couple of days ago. What should I be looking for? I'm baffled because the same SELECT works from SQL Server Management Studio. Does SQL SMS use a different connection?

Usually when something was working and now it has stopped working, then something was changed. Start troubleshooting until you find what it is, look for index changes, code architecture modifications, even windows updates, anything which may give you a lead, until you are able to restore it.

Additional advice, try limiting the select statement to TOP 10, just to see you are able to get back results, this may indicate the issue is in the object's size / query execution time, and not with your server / application configuration.