C# SQL query blocks server memory

Question

I'm a bit newbie still and I have been assigned with the task of maintaining previosuly done code. I have a web that simulates SQL Management Studio, limitating deleting options for example, so basic users don't screw our servers. Well, we have a function that expects a query or queries, it works fine, but our server RAM gets blown up with complex queries, maybe it's not that much data, but its casting xml and all that stuff that I still don't even understand in SQL.

This is the actual function:

public DataSet ExecuteMultipleQueries(string queries)
    {
        var results = new DataSet();
        using (var myConnection = new SqlConnection(_connectionString))
        {
            myConnection.Open();
            var sqlCommand = myConnection.CreateCommand();
            sqlCommand.Transaction = myConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
            sqlCommand.CommandTimeout = AppSettings.SqlTimeout;
            sqlCommand.CommandText = queries.Trim();
            var dataAdapter = new SqlDataAdapter { SelectCommand = sqlCommand };
            dataAdapter.Fill(results);
            return results;
        }
    }

I'm a bit lost, I've read many different answers but either I don't understand them properly or they don't solve my problems in any way.

I know I could use Linq-toSql- or Entity, I tried them but I really don't know how to use them with an "unknown" query, I could try to research more anyway so if you think they will help me approaching a solution, by any means, I will try to learn it.

So to the point:

The function seems to stop at dataAdapter.Fill(results) when debugging, at that point is where the server tries to answer the query and just consume all its RAM and blocks itself. How can I solve this? I thought maybe by making SQL return a certain amount of data, store it in a certain collection, then continue returning data, and keep going until there is no more data to return from SQL, but I really don't know how to detect if there is any data left to return from SQL.

Also I thought about reading and storing in two different threads, but I don't know how the data that is in one thread can be stored in other thread async (and even less if it solves the issue).

So, yes, I don't have anything clear at all, so any guidance or tip would be highly appreciated.

Thanks in advance and sorry for the long post.

Answer 1

In order to investigate, try the following:

• Start SQL profiler (it is usually installed along with SSMS and can be started from Management Studio, Tools menu)
• Make sure you fill up some filters (either NT username or at least the database you are profiling). This is to catch as specific (i.e. only your) queries as possible
• Include starting events to see when your query starts (e.g. RPC:Starting).
• Start your application
• Start the profiler before issuing the query (fill the adapter)
• Issue the query -> you should see the query start in the profiler
• Stop the profiler not to catch other queries (it puts overhead on SQL Server)

• Stop the application (no reason to mess with server until the analysis is done)

• Take the query within SQL Management Studio. I expect a SELECT that returns a lot of data. Do not run as it is, but put a TOP to limit its results. E.g. SELECT TOP 1000 <some columns> from ....

If the TOPed select runs slowly, you are returning too much data.

This may be due to returning some large fields such as N/VARCHAR(MAX) or VARBINARY(MAX). One possible solution is to exclude these fields from the initial SELECT and lazy-load this data (as needed).

Check these steps and come back with your actual query, if needed.

Answer 2

You can use pagination to fetch only part of the data.

Your code will be like this:

dataAdapter.Fill(results, 0, pageSize);

pageSize can be at size you want (100 or 250 for example).

You can get more information in this msdn article.