question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked CarrinWu-MSFT commented

SQL Server round trip issue between server and client pc

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TZacks-2728, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DBCC USEROPTIONS reports an isolation level of 'read committed snapshot' when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to 'read committed'. The actual isolation level is read committed.

How DBCC USEROPTIONS help us to diagnosis the problem ?

0 Votes 0 ·

How DBCC USEROPTIONS help us to diagnosis the problem ?

If they are different, that could be part of the puzzle. If they are identical, it is not. As for what matters when they are different, that depends on what is different, and I am not going type a novel, just in case. But if they are different, share the output.

My post had one more question, which you did not answer. You are certainly under no obligation to answer the questions we ask you, but if you don't share enough information, it is unlikely that you will get help with your problem.



0 Votes 0 ·

after issuing DBCC USEROPTIONS i got below output which not clear to me.

 textsize    2147483647
 language    us_english
 dateformat    mdy
 datefirst    7
 lock_timeout    -1
 quoted_identifier    SET
 arithabort    SET
 ansi_null_dflt_on    SET
 ansi_warnings    SET
 ansi_padding    SET
 ansi_nulls    SET
 concat_null_yields_null    SET
 isolation level    read committed snapshot
0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @TZacks-2728,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.