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.


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


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:


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


  • 2
    Upload the actual execution plan here and add the link to your question. I suspect the query is CPU bound and you are limited by the 2 cores.
    – Dan Guzman
    Mar 27, 2020 at 12:56
  • Execution plan for all 13 contains only 7 queries. One of them has excessive memory grant (it requries 600 Mb of RAM to start but uses only 11 Mb). What about other 6 which are not presented in attached execution plan? Was actual execution plan button enabled or disabled when you measured timings in SSMS? Mar 29, 2020 at 11:50

2 Answers 2


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.

  • 1
    Thanks for your suggestion. We are making these changes. We always knew this would bite us at some point. I will update once we have made the changes.
    – jasear
    Mar 31, 2020 at 11:11

In order to understand what is happening, I would gather more metrics. First, don't use Profiler, it adds it's own overhead. Assuming you're on 2012 or better use Extended Events. Further, using Extended Events create a session that not only captures rpc_completed and batch_completed for your queries, but also add wait_info_external and wait_completed. With all this add causality tracking. Here you'll be able to see exactly what the query is waiting on as well as how long it takes to run.

On top of this, get the execution plans for when it runs well and when it runs slowly. Compare the two to find any differences.

All this should show you where the problem lies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.