0

So we have a small number of SQL Server 2005 Servers running a lot of SSRS reports, which is running into issues where the servers cannot keep up with the total number of reports, that users are scheduling on shared schedules, running at once.

Has anybody seen or know of a good way to implment some sort of job queues or lists?

For instance a '6 am' job list that will contain all of the reports that run at 6 am, and then one agent job will start running the reports in the list one after the other. This would keep the reports from running thier mini-DDOS attack against thier own reporting servers.

We would prefer that we not have to remove the scheduling functionalty that is already built into SSRS, we just want the agent to not kick off 3000+ reports all at the same time, without having to manually schedule each one to run 1-2 seconds from the previous one.

uzbones
  • 215
  • 1
  • 3
  • Have you considered looking into your indexes? Maybe you can gain performance that way. – jinsungy Jul 02 '09 at 15:36
  • I don't think the issue is the performance of any given report itself, they are mostly very fast (only takes 1-2 seconds to run a single report), it's just that so many of them start at once that the server gets bogged down with all the rendering and processing that happens at once. – uzbones Jul 02 '09 at 15:50
  • Basiclly I'm looking to have a smaller number of concurrent SQL Agent jobs running at once running some kind of queue of the reports, but I haven't been able to find anything on google (I'm probably just searching for the wrong thing)... – uzbones Jul 02 '09 at 15:53

1 Answers1

1

I have similar problem. I created "banks" of shared schedules to handle this.

For example

I have 1 "bank" setup to run at 6am another to run at at 6:05 etc. Then I make sure that the reports are balanced across each "bank".

Another option to look into is Resource Governor (Requires sql server 2008). It won't queue up the jobs; but it will allow you to ensure that they don't bog down the server:

[Resource Governor on BOL1

Jason Horner
  • 612
  • 2
  • 6
  • 13