SQL query takes too long to render/return the data

Question

I have two servers running the same SQL query. Query is running fine on one server but it takes too long (37 seconds) on other server.

I turned the statistics on and i got these results:

server 1 (This works fine and result is returned in 2 seconds):

SQL Server parse and compile time: 
   CPU time = 193 ms, elapsed time = 193 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Jan 30 2014 10:24PM

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Feb  6 2014 10:24PM

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(6 row(s) affected)

 SQL Server Execution Times:
   CPU time = 172 ms,  elapsed time = 182 ms.

Server 2: (this takes 37 seconds)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Jan 30 2014 10:24PM

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Feb  6 2014 10:24PM

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(6 row(s) affected)

 SQL Server Execution Times:
   CPU time = 34255 ms,  elapsed time = 34087 ms.

As you can see the problem is rendering the query and for some reason when i call the query from my SSRS report, it does the same thing. The report is rendered in no time on Server 1 but takes forever on Server 2.

Can someone please point me in the right direction as what could be wrong?

Are the query plans the same or different? Do the tables have the same rowcounts? — Nick.Mc, Feb 07 '14 at 06:51

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Please check and watch carefully onto these points according to your two servers and check both against each other:

Configuration of the Database instance
Setup of queried database/tables
Overall process performance
System memory (is it using HDD to extend RAM when the lag occurs?)
HDD setup/status (slow/defect/fragmented HDD can cause problems like this, too)
Try command "ALTER DATABASE SET PARAMETERIZATION FORCED GO"
Use DBCC MEMORYSTATUS for deep analysis

My preferred documentation for possible deep analysis to be used as an overview: http://sqlserverplanet.com/troubleshooting/sql-server-slowness

You can also have a look here: How to analyze 'dbcc memorystatus' result in SQL Server 2008

Otherwise, you can search for information (cause I am not allowed to post more links) at mssqltips dot com about how to identify sql server memory/cpu bottlenecks. They also have nice documentations onto that.

Hope this helps you.

Thank you for the answer. I think the problem is with my query. I will accept your answer anyways because it has good pointers. — Asdfg, Feb 07 '14 at 04:59
I don't think it can be your query, besides if the servers are very different in memory/cpu setup. If you think, that your query is the problem, you should post it, so that we can have a look onto it ;-) — GreatSUN, Feb 07 '14 at 05:03

SQL query takes too long to render/return the data

1 Answers1