CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
Asdfg
Asdfg

Reputation: 12213

SQL query takes too long to render/return the data

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?

Upvotes: 1

Views: 2561

Answers (1)

GreatSUN
GreatSUN

Reputation: 50

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

  1. Configuration of the Database instance
  2. Setup of queried database/tables
  3. Overall process performance
  4. System memory (is it using HDD to extend RAM when the lag occurs?)
  5. HDD setup/status (slow/defect/fragmented HDD can cause problems like this, too)
  6. Try command "ALTER DATABASE SET PARAMETERIZATION FORCED GO"
  7. 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.

Upvotes: 1

Related Questions