none
performance difference between clients in SQL query

    Question

  •  

    Hi,

    I have an architecture of one DB server and a few other computers running SQL management studio.

    I am running the same exact heavy duty query from different computers. One works fine - the other gets bogged down in something and eventually I get a timeout message.

    Using task manager, the deadbeat computer doesn’t show any process using high cpu, high storage, high IO or even high networking

    short simple queries work fine.

    The problem recurs also whern the query is ran from a C# application - so I don't think it is related to management studio.

    This phenomenon is intermittent with the bad computer sometimes working fine. But its always the same computer that is malfunctioning.

    What could be the problem?

    Sunday, April 29, 2012 11:16 AM

Answers

  • I  wanted to clear the data cache  by running this command (possible bad cached plan was removed) and SQL Server optimizer now is able to create more efficient one.

    DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
    be recompiled

    if you want a stored procedure to be compiled you will need to use the WITH
    RECOMPILE option

    if you want to clear the data cache you will need to use DBCC
    DROPCLEANBUFFERS

    DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, May 03, 2012 8:49 AM
    Moderator

All replies

  • See Erland's article for subject  http://www.sommarskog.se/query-plan-mysteries.html

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, April 29, 2012 11:20 AM
    Moderator
  • Thanks Uri

    I read the article, but all the things it recommends to do - I tried - to no avail.

    The execution plan that i show for calling the sp is identical in both computers (as seen by invoking "Display estimated execution plan")

    The permissions issue is not relevant as it tried to run DBCC SHOWSTATISTICS in both computers on the main clustered index of the main table in the query and i get the same results.

    I tried comparing for Option Settings - and I get the same options being set in both computers.

    I'm still at a loss.... 

    Sunday, April 29, 2012 12:49 PM
  • Ok, so running this SP in SSMS or C# on the 'dead' computer results bad performance , am I right? Can you show us an execution plan (actual) from both computers. What does the procedure do? Does it take a parameters? Take a look into http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, April 29, 2012 12:54 PM
    Moderator
  • Thanks again Uri

    This is very elusive

    I rewrote the sp as a batch with hardcoded assignment of values to the params and reran it again from two computers simultanously via SSMS

    the actual execution plan with percentages, actual rows read etc was identical. the client statistics was similar.

    but one finished in 7 minutes, the other in 2 hours.

    this time the "bad" computer was the faster one.

    Anyway I have in the batch and have always had in the sp Option(Recompile) on the main join.

    I don't think it is the content of the query which is the problem. Its something to do with priorities or locks (although nothing else is working at the time) or some identification of the client computer... DBA stuff... and I am not...

    Monday, April 30, 2012 7:36 AM
  • Run DBCC DROPCLEANBUFFER  on both computers and re-ran the queries ,same result?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 30, 2012 7:41 AM
    Moderator
  • Thanks yet again Uri.

    I ran  DBCC DROPCLEANBUFFER  on all computers. they now run OK - but the problem, had been intermittent - so i don't know if I have a solution - or just a lull in the fighting....

    Thursday, May 03, 2012 8:11 AM
  • I  wanted to clear the data cache  by running this command (possible bad cached plan was removed) and SQL Server optimizer now is able to create more efficient one.

    DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
    be recompiled

    if you want a stored procedure to be compiled you will need to use the WITH
    RECOMPILE option

    if you want to clear the data cache you will need to use DBCC
    DROPCLEANBUFFERS

    DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, May 03, 2012 8:49 AM
    Moderator