none
Is there a way to track or measure latency from Application server to DB Server RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    Tuesday, June 25, 2019 5:53 PM
    |

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    Wednesday, June 26, 2019 6:44 AM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Tuesday, June 25, 2019 7:31 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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

    Tuesday, June 25, 2019 10:05 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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.

    Wednesday, June 26, 2019 3:09 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Wednesday, June 26, 2019 3:50 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Will for sharing the link. Its nice.
    Wednesday, June 26, 2019 3:53 AM
    |
  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    Wednesday, June 26, 2019 6:44 AM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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

    Wednesday, June 26, 2019 9:39 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Will and Erland.
    Thursday, June 27, 2019 11:17 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.

    Monday, July 1, 2019 8:15 AM
    |
    Moderator