Sql-server – Multiple SQL queries fired asynchronously progressively slow down

sql server

We are using .NET Core Web API 2. We have a vue client that makes calls to this Web API 2 API method. We have a report dashboard page. On this page we have 13 charts which means 13 calls are made to an API endpoint to get the data for the charts.

If we run the 13 queries in SQL individually all of them run within a second or less. I created a console app in which I ran the 13 queries in a loop and captured some timings. Every query ran in less than 2 seconds.

I then captured some timings in the API method. I captured the start time, end time and duration. All the 13 calls were made within a second of each other. The bottle neck seemed to be around the the code that ran the SQL query. The query timings ranged from less than a second to over 11 seconds:

Please note these are times in ms and is only capturing the time the query takes in the API method. I also used SQL Profiler and saw the timings there and they were similar.

It has something to do with making multiple API calls asynchronously. If I made an API call one at a time for each query it will give times of less than a couple of seconds.

I am running the code in C# in such a way that it does not lock the table / database for example I am using isolation level of snapshot. I have also tried to use isolation level or readuncommitted and the problem is still there.

We are using Windows Server 2019 with 2 processors and 10 GB Memory. SQL Server version is 2017.

I have done a few days of research and tried a few things but nothing seems to over come this problem. I needed some advice on what could be the cause of this issue and how to overcome it.

Just to add each query returns a single row of data that is consumed by the chart. I also ran the combined 13 queries in sql management studio and it took 8 seconds for the entire query to process.

I also noticed that when I access the dashboard the server cpu usage goes through the roof hitting 100% for a few seconds.

Update:

Execution plan when I run all 13 directly in SQL Management Studio:

https://www.brentozar.com/pastetheplan/?id=S1TFqgnL8

Execution plan for one of the queries that was triggered via the api code i.e. when I access the dashboard It took 12+ seconds to finish:

https://www.brentozar.com/pastetheplan/?id=HyIjKx3L8

Execution plan for the same query if I run it alone via sql it takes 2 seconds:

https://www.brentozar.com/pastetheplan/?id=ByRCcxnUU

Best Answer

Those plans are pretty gnarly. And utterly inappropriate for such a small SQL Server. To be effective at that scale, you have to be efficient. You can do a lot with a little SQL Server, but you have to do things right.

It appears that you are storing your data in single-element JSON arrays instead of properly typed columns. You shouldn't do that. It will cause you no end of pain. It's good to store JSON docs in a column when you have a legitimate need to, but you are misusing the feature, and not using it for its designed purpose.

EG don't do this:

drop table if exists foo
create table foo(id int primary key, sometableOrgLevel1Lookup nvarchar(max))
insert into foo(id,sometableOrgLevel1Lookup) values (1, N'[1]')

select t.*, j1.id
from foo t
OUTER APPLY OPENJSON(t.sometableOrgLevel1Lookup) WITH (id int '$') j1

Instead declare columns with proper data types, with Foreign Key consraints, and add an index supporting each Foreign Key:

drop table if exists foo

create table foo
(
  id int primary key, 
  sometableOrgLevel1Lookup int, 
  index ix_sometableOrgLevel1Lookup(sometableOrgLevel1Lookup),
  constraint fk_sometableOrgLevel1Lookup foreign key (sometableOrgLevel1Lookup) references sometableOrgLevel1(Id)
)
insert into foo(id,sometableOrgLevel1Lookup) values (1,1)

select * from foo

At the very least, get rid of all the APPLY operators that are parsing the JSON. SQL Server is guessing about how many rows they will return, leading to poor cardinality estimation, and probably excessive memory grants. Instead write the JOINs like this:

LEFT JOIN [sometableOrgLevel1] AS [org1] ON ([org1].[Id] =  cast(json_value(t.sometableOrgLevel1Lookup,  '$[0]') as int))

I'm not sure if that the the only problem. But the first thing you should do is fix the table definitions and data storage formats, and then test the queries against the modified tables.

Related Solutions

Sql-server – Windows SQL 2008 R2 performance issue

The fact that you are getting different numbers of reads suggests the two servers are using different execution plans.

If you post the plans from both new and old servers ,I'd suggest using PasteThePlan: https://www.brentozar.com/pastetheplan/ (the site includes instructions for how to get the plans) and post the links so we can see what's going on.

The differences could be down to the differences in the SQL versions but I'd guess it's down to parameter sniffing causing different execution plans to be compiled.

Sql-server – Three update query vs single update query performance

The first single update reads and writes every row from the table. The second and third then re-read and re-write a sub-set of those rows. Look at the Actual Number of Rows. When the three statements are combined into one, the optimizer figures that if it has to read everything to satisfy the first change then it can piggy-back off that for the second and third change.

Have a look at the XML version of the query plans, specifically the <ComputeScalar> operators and <ScalarOperator ScalarString=""> parts. In the original plan you'll see each is relatively simple and maps very closely to the SQL. For the all-in-one plan it's a monster. This is the optimizer re-writing the SQL into a logically equivalent form. A plan works¹ by passing each row through the operators once. The optimizer's doing all the work it has to do to satisfy all three changes as that row passes through one time.

I'd expect the second query to be faster because the data is only read and written once whereas it is touched three times in the first.

As the second query has no predicates (no WHERE clause) the optimizer has no choice but read every single row and process it. I'm surprised the second form takes longer than the first. Are both starting from clean buffers? Is there other work happening on the system? Since it's reading and writing to a temp table the IO is happening in tempdb. Is there file growth or somesuch happening?

By one measure you have achieved your desired outcome. You say you want to make changes "so that the IO can be reduced." The all-in-one does less IO than the three separate statements do in total. I suspect what you really want, however, is reduced elapsed time, and this is obviously not happening.

_{¹ more or less, lots of details omitted.}

I ran your routine to generate test data then ran the three single-update statements and the all-in-one statement. Although there are some differences (no clustered index, no parallelism) I get more-or-less the same results. Specifically, the plans are about the same shape and the three individual queries complete in about two seconds and the one big query in about thirty to thirty five seconds.

I set

set nocount off;
set statistics io on;
set statistics time on;

With the plan in cache and the data in memory I get:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 1422 ms,  elapsed time = 1417 ms.

(242906 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 337 ms.

(0 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 734 ms,  elapsed time = 747 ms.

(0 row(s) affected)

I've removed some bits that aren't relevant. Since physical reads is zero for all three the table fits in memory. logical reads is the same for all three which makes sense. As there are no indexes the only approach is to scan every row of the table. The second and third query affect zero rows because I'd run them a few times already. CPU time and elapsed work out as 2500ms.

For the bigger query it is

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223

 SQL Server Execution Times:
   CPU time = 33093 ms,  elapsed time = 33137 ms.

(242906 row(s) affected)

The same number of pages are read, the same number of rows are updated. The huge differences is the CPU time. This is reflected in casual observation of Task Manager which shows 30% utilisation for the duration of query execution. The question is, why does it take so much?

The individual queries separately have simple calculations and two of the statements have predicates that greatly reduce the number of rows touched. The optimizer has good heuristics for processing these and finds a quick plan. The all-in-one query applies the monster Compute Scalar against every single row. My suggestion is that, for whatever reason, the optimizer cannot unravel the logic into a plan that's quick to run and ends up using a lot of CPU. The optimizer has to work with what its given, which in the second case is complex, nested SQL. Perhaps by refactoring the SQL the optimizer will follow different heuristics and achieve a better outcome? Perhaps some (filtered) indexes or (filtered) statistics will convince it to write a different plan. Maybe persisted computed columns would help it along. Perhaps you just need to give the optimizer what it needs and your first attempt really is the best that can be achieved and you need to find a way to run those three in parallel. Sorry I can't be more scientific.

Best Answer

Related Solutions

Sql-server – Windows SQL 2008 R2 performance issue

Sql-server – Three update query vs single update query performance

Related Question