SQL Server round trip issue between server and client pc

T.Zacks 3,926 Reputation points
2021-05-01T16:33:43.28+00:00

For example i have two pc in a LAN. pc1 has sql server installed and management studio also but pc has only management studio. when i execute a SP by management studio in pc1 then output comes after 4 minutes but when i execute the same SP by management studio in pc2 then output comes after 11 minutes. both pc in same LAN then why pc2 takes long time to show data.

please suggest some guide line to diagnosis this problem. how to figure out where the problem lies?
how to capture is it sql server issue or network issue ?

please help in such a way as a result i should be able to capture where the problem is. thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,502 questions
{count} votes

2 answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,791 Reputation points Microsoft Employee
    2021-05-03T03:18:25.927+00:00

    Hi @T.Zacks ,

    Welcome to Microsoft Q&A!

    You could follow this blog to troubleshoot this problem:

    The first thing to verify is that you are running the same query with the same parameters. Depending on the query parameters it is very possible to get different plans, either by using sargable vs nonsargble or even having a different data type which causes an implicit conversion.
    Secondly, you will want to make sure your “set options” are configured the same as your application. To do this, you can verify your set options in your execution plans by right clicking on your select operator and choosing properties. In the window on the left you will see a section labeled Set options, once expanded you will see all the options that were used during the execution of your query.

    Best regards,
    Carrin


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

  2. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-05-01T17:37:45.357+00:00

    What does the stored procedure look? Is it a single SELECT? Is it procedural code that runs a cursor? Something else?

    If you run DBCC USEROPTIONS from SSMS on the two machines, is the output identical?