Answered by:
Is there a way to track or measure latency from Application server to DB Server

Question
-
Hi All,
We have SQL Server instance running on a VM. Now we wanted to move the instance to sql azure managed instance.
Just want to find out different to measure Latency between App and DB VM’s. This will help us to determine the latency to sql azure db's post migration.
Also, please let me know if there is any native of doing this without any 3rd party tool intervention. just want to know if there anything from windows side or application side which has the capability of logging the latency?
Thank you,
Sam
Answers
-
Yes. Erland. I am looking for app to DB. Sir, have you came across such requirement in your experience. Is normally done by the network monitoring team or is it can be done using sql as well.
No, it cannot be done in SQL Server. SQL Server does not know when the application sent the request, or when then application received the response.
From app side, how can they do that. Can you give me an example? so that we can provide suggestions to app team.
I guess they send a query, record when they sent and when they received the response. For the test to be meaningful, this should be measured both before and after they view. The query in this case, should be a very simple one like "SELECT 1", I think. Since the time may be very short, maybe they should send 100 such queries and measure the time for the loop.
Then again, they prefer to measure something that reflects the actual behaviour of the application.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Proposed as answer by Will_KongMicrosoft contingent staff, Moderator Thursday, June 27, 2019 7:38 AM
- Marked as answer by Samantha v Tuesday, July 2, 2019 12:03 PM
All replies
-
Hi Samantha v,
Please try the following SQL to get latency estimates in your environments. Also, out of curiosity, please share its outcome in both environments, i.e. VM on prem. vs Azure.
SQL:
SELECT LEFT(physical_name, 1) AS drive , CAST(SUM(io_stall_read_ms) / ( 1.0 + SUM(num_of_reads) ) AS NUMERIC(10, 1)) AS 'avg_read_disk_latency_ms' , CAST(SUM(io_stall_write_ms) / ( 1.0 + SUM(num_of_writes) ) AS NUMERIC(10, 1)) AS 'avg_write_disk_latency_ms' , CAST(( SUM(io_stall) ) / ( 1.0 + SUM(num_of_reads + num_of_writes) ) AS NUMERIC(10, 1)) AS 'avg_disk_latency_ms' FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id GROUP BY LEFT(physical_name, 1) ORDER BY avg_disk_latency_ms DESC;
Output on my local machine, SQL Server on SSD drive:
drive avg_read_disk_latency_ms avg_write_disk_latency_ms avg_disk_latency_ms C 1.3 0.5 1.1
- Edited by Yitzhak Khabinsky Tuesday, June 25, 2019 7:33 PM
-
Yitzhak, I don't think that what Sam is asking for. She is asking for latency between application and database, and your query gives latency from database to disk.
I think latency from app to DB would have to be measured from the application.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
Hi Sam,
Per your post, the question more likely belongs to network problem. As far as I know, latency is one of factors that lead to network performance. For how to measure latency, you could refer to this article to start your task.
How to monitor and detect SQL Server round-trip performance issues using custom designed metrics
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. -
Yes. Erland. I am looking for app to DB. Sir, have you came across such requirement in your experience. Is normally done by the network monitoring team or is it can be done using sql as well.
From app side, how can they do that. Can you give me an example? so that we can provide suggestions to app team.
- Edited by Samantha v Wednesday, June 26, 2019 3:55 AM typo
-
-
Yes. Erland. I am looking for app to DB. Sir, have you came across such requirement in your experience. Is normally done by the network monitoring team or is it can be done using sql as well.
No, it cannot be done in SQL Server. SQL Server does not know when the application sent the request, or when then application received the response.
From app side, how can they do that. Can you give me an example? so that we can provide suggestions to app team.
I guess they send a query, record when they sent and when they received the response. For the test to be meaningful, this should be measured both before and after they view. The query in this case, should be a very simple one like "SELECT 1", I think. Since the time may be very short, maybe they should send 100 such queries and measure the time for the loop.
Then again, they prefer to measure something that reflects the actual behaviour of the application.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Proposed as answer by Will_KongMicrosoft contingent staff, Moderator Thursday, June 27, 2019 7:38 AM
- Marked as answer by Samantha v Tuesday, July 2, 2019 12:03 PM
-
By the way, for what it is worth, in the query menu in SSMS, there is an option Include Client Statistics. I have used it very little, so I cannot really say how useful it is, but you may want to check it out.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
-
Thanks Will and Erland.
Thanks for your feedback. If you have no other doubts, please kindly close the thread by marking useful reply as answer.
Thanks for your contribution.
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.