Fill(DataTable) succeeds in testing, hangs in Production

Question

I have a console batch application which includes a process that uses SqlDataAdapter.Fill(DataTable) to perform a simple SELECT on a table.

private DataTable getMyTable(string conStr)
    {
        DataTable tb = new DataTable();
        StringBuilder bSql = new StringBuilder();
        bSql.AppendLine("SELECT * FROM MyDB.dbo.MyTable");
        bSql.AppendLine("WHERE LEN(IdString) > 0");                 
        try
        {
            string connStr = ConfigurationManager.ConnectionStrings[conStr].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlDataAdapter adpt = new SqlDataAdapter(bSql.ToString(), conn))
                {
                    adpt.Fill(tb);
                }

            }
            return tb;
        }          
        catch (SqlException sx)
        {
            throw sx;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

This method is executed synchronously, and was run successfully in several test environments over many months of testing -- both when started from the command-line or started under control of an AutoSys job.

When moved into production, however, the process hung up -- at the Fill method as nearly as we can tell. Worse, instead of timing out, it apparently started spawning new request threads, and after a couple hours, had consumed more than 5 GB of memory on the application server. This affected other active applications, making me very unpopular. There was no exception thrown.

The Connection String is about as plain-vanilla as they come.

"data source=SERVER\INSTANCE;initial catalog=MyDB;integrated security=True;"

Apologies if I use the wrong terms regarding what the SQL DBA reported below, but when we had a trace put on the SQL Server, it showed the Application ID (under which the AutoSys job was running) being accepted as a valid login. The server then appeared to process the SELECT query. However, it never returned a response. Instead, it went into an "awaiting command" status. The request thread appeared to remain open for a few minutes, then disappeared.

The DBA said there was no sign of a deadlock, but that he would need to monitor in real time to determine whether there was blocking.

This only occurs in the production environment; in test environments, the SQL Servers always responded in under a second.

The AutoSys Application ID is not a new one -- it's been used for several years with other SQL Servers and had no issues. The DBA even ran the SELECT query manually on the production SQL server logged in as that ID, and it responded normally.

We've been unable to reproduce the problem in any non-production environment, and hesitate to run it in production without a server admin standing by to kill the process. Our security requirements limit my access to view server logs and processes, and I usually have to engage another specialist to look at them for me.

We need to solve this problem sooner or later. The amount of data we're looking at is currently only a few rows, but will increase over the next few months. From what's happening, my best guess is that it involves communication and/or security between the application server and the SQL server.

Any additional ideas or items to investigate are welcome. Thanks everyone.

Answer 1

Though it may be caused by some strange permissions/ADO.NET issues as mentioned by @user1895086, I'd nonetheless would recommend to recheck a few things one more time:

1. Ensure that queries run manually by DBA and executed in your App are the same - either hardcode it or at least log just before running. It is better to be safe than sorry.
2. Try to select only few rows - it is always a good idea to not select the entire table if you can avoid it, and in our case SELECT TOP 1(or 100) query may not exhibit such problems. Perhaps there is just much more data than you think and ADO.Net just dutifully tries to load all those rows. Or perhaps not.
3. Try SqlDataReader to be sure that SqlDataAdapter does not cause any issues - yes, it uses the same DataAdapter internally, but we would at least exclude those additional operations from a list of suspects.
4. Try to get a hand on the dump with those 5 GB of memory - analyzing memory dumps is not a trivial task, but it won't be too difficult to understand what is eating those hefty chunks of memory. Because I somehow doubt that ADO.NET will just spawn a lot of additional objects for no reason.

Answer 2

This may be tied to permissions. SQL Server does some odd things instead of giving a proper error message sometimes.

My suggestion, and this might improve performance anyway, is to write a stored procedure on the server side that executes the select, and call the stored procedure. This way, the DBA can ensure you have proper access to the stored procedure without allowing direct access to the table if for some reason that's being blocked, plus you should see a slight performance boost.