December 17, 2007 at 2:21 pm
I've got a user running a report. I've broken this down and run the stored proc behind the report with the same parameters and the query completes in under 10 seconds. My report, however, will run for 1 - 2 hours and then eventualy timeout or error out. The query itself returns ~1000 records. This server has 4GB of ram and dual 2.4GHz core 2 dup processors. Not sure what might be off. Any insight would be greatly appreciated!
December 17, 2007 at 2:33 pm
There are a lot of possibilities. The most likely one is that the report is being blocked by some other process. If this is the case, then you can look at having the report automatically generated at a period of low use by an agent job or you can consider using transaction isolation level read uncommitted or the query hint with (nolock). Of course, using transaction isolation level read uncommitted has several dangers in terms of maintaining consistency so be certain that you are willing to accept those risks before you consider using it. This was discussed nicely at http://www.sqlservercentral.com/articles/Performance+Tuning/2764/ [/url] by Wayne Fillis.
Another possibility to look at is if the network rather than sql server is bogging down. Again, a possible work around is to have it scheduled for off peak times or look at increasing available bandwidth on the network.
Also, if it is not a dedicated sql server, the other computer resources could be slowing down under demands from other applications.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply