SQL - Select Statement taking time to execute - No Blocking/No High Utilization

Question

SQL - Select Statement taking time to execute - No Blocking/No High Utilization

Yogesh Bhadauriya 26

I am having Dynamic CRM product, where one select statement is always taking time to execute from the application.

select    new_OrderNo, new_LineNumber, new_ProductNumber   
from    [CRM Sales History Table]
where CreatedOn >= DATEADD(Day, -30, getdate())   
and OwnerId = '92e58b27e70143519c80347562ea7111'

I validated the all SQL resourecs & didn't find anything unusal. Note that whenever I manualy run this query, it is executing within a second.

Attached Execution plan of the query

Execution Plan

Any guidance why this happend ? Note : No blocking, No high CPU/Memory utilization

Viorel 127K Reputation points

2023-08-25T13:57:08.83+00:00

How did you determine that Dynamics executes this query?
Yogesh Bhadauriya 26 Reputation points

2023-08-25T13:59:25.81+00:00

Only one application configured to use this database.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-25T21:01:52.76+00:00

How did you capture that statement? It is not unheard of queries running fast in SSMS but slow in application. In fact I have an article about that on my web site. But this query is a little too simple to fall into that general pattern. On the other hand, if the original query was parameterised and you replaced it with a fixed GUID, it's a different matter.
Yogesh Bhadauriya 26 Reputation points

2023-08-26T10:10:08.02+00:00

Hi @Erland Sommarskog

I captured the index by running the DMV (Currently running query in DB).
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-26T14:33:33.28+00:00

Hum, I wondering if you have captured the correct statement. If I had access to your server I would run my beta_lockinfo to collect information about the query and the current execution plan. You should probably run it multiple times to verify that this is the query that the application is stuck on. Also pay attention to the column last_batch_time. I could be that the application seems to be stuck at this statement, but is actually executing it many times for some reason.
Anonymous

2023-08-30T09:21:32.3766667+00:00

Hi,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Yogesh Bhadauriya 26 Reputation points

2023-08-31T04:38:43.62+00:00

I checked all the options, somehow this query is stuck in "ASYNC_NETWORK_IO" wait type.

Answer accepted by question author

Erland Sommarskog 134.7K MVP Volunteer Moderator

I checked all the options, somehow this query is stuck in "ASYNC_NETWORK_IO" wait type.

That explains everything. It is most likely an application issue. This wait type means that SQL Server is waiting for the client to fetch the data from the output buffer. There are be two reasons for this error:

Network issue. In your case it would happen if the application server for Dynamics CRM has a modem connection of 9600 baud to SQL Server. Well maybe not that bad, but definitely a connection below par.
The application itself is misbehaving. Rather than reading all rows in one fell swoop, it reads a row, performs some calculations or whatever, then reads the next row etc.

Yogesh Bhadauriya 26 Reputation points

2023-09-01T13:10:40.9033333+00:00

This issue happened on random basis. The application is working fine most of the times, except the few cases.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-01T21:20:51.6066667+00:00

Yeah, that could be fluctuations in the network capacity. Or some random variations in CRM. How many rows does the query return?
Yogesh Bhadauriya 26 Reputation points

2023-09-02T03:39:03.9866667+00:00

Query return approx : 1400 rows.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-02T08:25:34.1566667+00:00

Which is not extortionate, but certainly big enough to have potential to cause issues.
Yogesh Bhadauriya 26 Reputation points

2023-09-02T09:46:53.0066667+00:00

Correct @Erland Sommarskog , Can you please help me to undestand this random behaviour ?
Something is happening, which I am not able to find.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-02T10:35:01.79+00:00

As I said, this is either the network or the application. It it would be a network thing, you might also see it in SSMS if you run the query often enough there. Then again, I don't know how you are running CRM. Maybe there is a different network path if you are using the application. One could also argue that if it is a network issue, you would see this with random operations. It seems from your post that you only have this problem with this specific operation, so that points to the application. However, I only know as much as you have told me, so I don't like to jump to conclusion. What I can say is that from the realm of SQL Server, there is not much to do.

(I should hasten to add that this is under the assumption that your observation about ASYNCH_NETWORK_IO is correct. Again, I rely on the information you share, and if that is inaccurate, so will my advice be.)

1 additional answer

Your answer

Viorel 127K Reputation points

2023-08-25T13:57:08.83+00:00

How did you determine that Dynamics executes this query?
Yogesh Bhadauriya 26 Reputation points

2023-08-25T13:59:25.81+00:00

Only one application configured to use this database.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-25T21:01:52.76+00:00

How did you capture that statement? It is not unheard of queries running fast in SSMS but slow in application. In fact I have an article about that on my web site. But this query is a little too simple to fall into that general pattern. On the other hand, if the original query was parameterised and you replaced it with a fixed GUID, it's a different matter.
Yogesh Bhadauriya 26 Reputation points

2023-08-26T10:10:08.02+00:00

Hi @Erland Sommarskog

I captured the index by running the DMV (Currently running query in DB).
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-26T14:33:33.28+00:00

Hum, I wondering if you have captured the correct statement. If I had access to your server I would run my beta_lockinfo to collect information about the query and the current execution plan. You should probably run it multiple times to verify that this is the query that the application is stuck on. Also pay attention to the column last_batch_time. I could be that the application seems to be stuck at this statement, but is actually executing it many times for some reason.
Anonymous

2023-08-30T09:21:32.3766667+00:00

Hi,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Yogesh Bhadauriya 26 Reputation points

2023-08-31T04:38:43.62+00:00

I checked all the options, somehow this query is stuck in "ASYNC_NETWORK_IO" wait type.
Yogesh Bhadauriya 26 Reputation points

2023-09-01T13:10:40.9033333+00:00

This issue happened on random basis. The application is working fine most of the times, except the few cases.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-01T21:20:51.6066667+00:00

Yeah, that could be fluctuations in the network capacity. Or some random variations in CRM. How many rows does the query return?
Yogesh Bhadauriya 26 Reputation points

2023-09-02T03:39:03.9866667+00:00

Query return approx : 1400 rows.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-02T08:25:34.1566667+00:00

Which is not extortionate, but certainly big enough to have potential to cause issues.
Yogesh Bhadauriya 26 Reputation points

2023-09-02T09:46:53.0066667+00:00

Correct @Erland Sommarskog , Can you please help me to undestand this random behaviour ?
Something is happening, which I am not able to find.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-02T10:35:01.79+00:00

As I said, this is either the network or the application. It it would be a network thing, you might also see it in SSMS if you run the query often enough there. Then again, I don't know how you are running CRM. Maybe there is a different network path if you are using the application. One could also argue that if it is a network issue, you would see this with random operations. It seems from your post that you only have this problem with this specific operation, so that points to the application. However, I only know as much as you have told me, so I don't like to jump to conclusion. What I can say is that from the realm of SQL Server, there is not much to do.

(I should hasten to add that this is under the assumption that your observation about ASYNCH_NETWORK_IO is correct. Again, I rely on the information you share, and if that is inaccurate, so will my advice be.)

Answer 1

Javier Villegas 905 MVP

I will suggest to use sp_whoisactive from Adam Machanic to identify the statement when is running from the application . Using the option to retireve the execution plan so you can checki is i the same as in SSMS

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

0 comments

Share via

SQL - Select Statement taking time to execute - No Blocking/No High Utilization

1 additional answer

Your answer