C# DB query app causes the DB server to stop responding: What tools can I use on the DB server to identify and study the choke point?

Question

I wrote a program modeled after the accepted answer for Throttling asynchronous tasks. See code below. My program is essentially an ad-hoc DB "text search" tool. It runs on a Win 7 client machine. The program composes a large collection of SELECT statements (hundreds to thousands of them) and sends them to a remote DB server (The Win 7 client and the DB server are on the same A.D. domain. The DB server searched is only ever the read-only secondary server in an Always On Availability Group). Each invocation of the Download method creates 1-to-many SELECT queries (one SELECT per table column being searched) for one DB table. The Download method is invoked 1-to-thousands of times (once per table being searched).

TPL Dataflow works good so long as I limit the search to no more that 60-70 tables. More than that and it chokes (I think) the SQL Server Database and/or the DB server machine. I've played with various MaxDegreeOfParallelism and BoundedCapacity values in attempt to control the client. With MaxDegreeOfParallelism = 8 (the number of processors on my client machine, I can see that CPU and DISK peg on the DB server machine. With MaxDegreeOfParallelism = 1 and BoundedCapacity = 1, The CPU and DISK are ok on the DB server machine, but in the act of submitting the queries, my database read code:

SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync();
dataTable_TableDataFromFieldQuery.Load(sqlDataReader);

eventually throws an exception "server has become unresponsive"

What tools can I use on the DB server to identify the choke point? Even assuming that I need to improves my queries, what do I look for and where?

Code from the other S.O. question modified for my use

var downloader = new TransformBlock<string, DataTable>(tableName => Download(tableName), new ExecutionDataflowBlockOptions {MaxDegreeOfParallelism={various values 1-5, BoundedCapacity={various values 1-5} } );

var buffer = new BufferBlock<DataTable>();
downloader.LinkTo(buffer);

foreach(var tableName in tableNames)
    await downloader.SendAsync(tableName);

downloader.Complete();
await downloader.Completion;

IList<DataTable> responses;

if (buffer.TryReceiveAll(out responses))
{
    //process all DataTables
}

as @TheGeneral have suggested, you could use SQL Profiler to tap on what are the failing queries. However, i'd go with the necessity doing `foreach` and throwing that many queries to the server... yes, that `await downloader.SendAsync(tableName);`.. it might be light on the app, but you kill the db.. — Bagus Tesa, Mar 30 '18 at 01:17

TheGeneral · Accepted Answer · 2018-03-30T01:04:41.940

This sounds like a bit of a mess, I'm not sure if you know about these tools but i think you should become very familiar with them.

The first is the SQL Server Profiler

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly

And the Database Engine Tuning Advisor

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server

The Profiler, if used strategically should be able to help narrow down the problem. The Tuning Adivsor should be able to loosely aid in writing better queries.

Though without knowing exactly what you are doing and why it would be hard to give you a better answer. However, my gut feeling this is probably a locking/blocking, memory, or excessive threads. Truthfully a well planned and designed database should be able to eat this workload and not skip a beat.

Lastly, (and not knowing your level of expertise here) i would do a lot of research into indexing, query performance, table formalization, and Locking Hints

I'll be learning and using the tools you suggest. Just so I might elicit more advice from you: Any changes I make will need to be restricted to my app because it is an ad-hoc search tool designed to search any DB. As such, I cannot expect to have the luxury of modifying any DB to improve the performance of my search program. Thanks again for your help — VA systems engineer, Mar 30 '18 at 11:37
Other than that, my program uses a 2-block TPL dataflow as shown and submits the SQL select statements as described above. Really a pretty brute-force approach. I use "using" statements to connect and query for each table field. I'm modifying my code now to submit one query for all of a table's fields to see if reducing the raw number of connect+query operations will help — VA systems engineer, Mar 30 '18 at 11:43
@NovaSysEng maybe you also want to try in https://dba.stackexchange.com/ — TheGeneral, Mar 30 '18 at 13:34

C# DB query app causes the DB server to stop responding: What tools can I use on the DB server to identify and study the choke point?

1 Answers1