Sql server DB freezes

Sql server DB freezes

Page 1 of 1

1 Replies - 2647 Views - Last Post: 30 November 2010 - 08:48 AM

#1 arunsand   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 20-August 07

Sql server DB freezes

Posted 30 November 2010 - 07:44 AM

Hi,

I need a expertise suggestions here....

for one of our client, the database is Sql Server 2005. and we have few SSIS reports (i guess 6 in total) which would query the database and generate .xls reports. this is a daily scheduled task.

as the generation of the reports takes little time (I guess 5-6 mins) so in total 30 mins for all the 6 reports. during this time, it almost blocks the db traffic and any other users who has connected the DB from the .net application would be real slower (almost freezing).

what is the best way to handle this situation. for the next 6 months the DB size would be almost double the size than what we have today.

is it recommendable to restore the main db on new db (daily before the first reports kicks off) and reports to connect to this new db? in this way we can free the main DB for the application users.

OR if any better suggestions please....

thanks
Arun

Is This A Good Question/Topic? 0
  • +

Replies To: Sql server DB freezes

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: Sql server DB freezes

Posted 30 November 2010 - 08:48 AM

I'd really want to look at the entire process. Over 5 minutes for a report is a long time.

Dumping to .xls can also be seriously messy, depending on how you do it. If you're opening an instance of an excel application and creating the worksheet with a COM object, that's the worst way possible.

If you are dumping to excel, why SSIS?

Step one, what does it take to simply process the data? Can the result set be placed in a warehouse table or something? Analyze those queries, can find the bottlenecks, fix them.

Look at how your SQL Server is setup. By default, the database server will take all available resources. If you have multiple CPUs and tell it to ignore one, then the server won't take over your box when it gets busy.

I'm actually not found of Reporting Services on the server. Letting your report server chew on the same box as the database is not ideal, particularly if those reports are intensive. I'd move the work to a .NET application on another server. That application would only hit the database server for the data and then crunch the results on it's side.

It really depends on where your bottleneck is.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1