Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Have you considered looking into your indexes? Maybe you can gain performance that way. –  jinsungy Jul 2 '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 2 '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 2 '09 at 15:53

1 Answer 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 BOL[1]

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.