CODEWITHSUNDEEP
c#.netsql-serverasp.net-mvcnopcommerce
Zeeshan Adil
Zeeshan Adil

Reputation: 2125

Figuring out what process is running on my SQL that is being called by my c# code

I am working on a .NET nop commerce application where I have around 5 million+ results in the database and I need to query all of that data for extraction. But the data from SQL is never returned to my code while my GC keeps on growing (it goes beyond 1gb) but when I run the same stored procedure in SQL after providing the respective parameters, it takes less than 2 minutes. I need to somehow figure out why call from my code is taking so much time.

NopCommerce uses entity framework libraries to call the databases stored procedure but that is not async so I am just trying to call the stored procedure in an async way using this function:

await dbcontext.Database.SqlQuery<TEntity>(commandText, parameters).ToListAsync();

as of my research from another SO post ToListAsync(); turns this call into an async when so the task is sent back to the task library.

now I need to figure out 3 things that currently I'm unable to do:

1) I need to figure out if that thread is running in the background? I assume it is as GC keeps growing but I'm just not sure, below is a pic of how I tried that using Diagnostics tool in Visual Studio:

enter image description here

2) I need to make sure if SQL processes are giving enough time to the database calls from my code, I tried following queries but they don't show me any value for the process running for that particular data export initiated by my code

I tried this query:

select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc

also tried this one:

SELECT r.session_id ,st.TEXT AS batch_text ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, ( ( CASE WHEN r.statement_end_offset = - 1 THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2) ELSE r.statement_end_offset END ) - r.statement_start_offset ) / 2 + 1) AS statement_text ,qp.query_plan AS 'XML Plan' ,r.* FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp ORDER BY cpu_time DESC

also when I use sp_who or sp_who2 the statuses for the processes for my database stay in the 'runnable' form like this, also those CPU and DISKIO:

enter image description here

3) I need to know, what if my DB call has been completed successfully but mapping them to the relevant list is taking a lot of time?

I would very much appreciate someone pointing me in the right direction, maybe help me with a query that can help me see the right results, or help me with viewing the running background threads and their status or maybe helping me with learning more about viewing the GC or threads and CPU utilization in a better way.

Any help will be highly appreciated. Thanks

Upvotes: 0

Views: 1666

Answers (1)

McKay
McKay

Reputation: 12604

A couple of diagnostic things to try:

  1. Try adding a top 100 clause to the select statement, to see if there's a problem in the communication layer, or in the data mapper.
  2. How much data is being returned by the stored procedure? If the procedure is returning more than a million rows, you may not be querying the data you mean.
  3. Have you tried running it both synchronously and asynchronously?

Upvotes: 1

Related Questions