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?

Answer 1

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/

Answer 2

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.... 

Answer 3

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/

Answer 4

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...

Answer 5

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/

Answer 6

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....

Answer 7

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/