Managing Job Priority with Report Cost
In my previous post, Managing Job Prioritization, I discussed different methods and reasons for handling multiple requests with limited resources. Specifically, scenarios where you wouldn’t want to run reports “First In First Out”, but in a more intelligent manor (such as, Prompts go first, Subscriptions go last, everyone else in the middle). There’s another option that can be used to control the order of things, and that’s the Report Cost. This arbitrary setting can be applied to any Report and then referenced in terms of setting priority when the Job executes. Leveraging this setting can allow you to do some pretty neat tricks.
Setting the Report Cost
If you right click on a Report and choose Properties, the last tab is called Priority. Here, you can set a value between 0 and 999. This value doesn’t directly do anything until we configure it later, so come up with your own numbering scheme. For reasons that will become clear later, 0 is “light” and 999 is “heavy”, so it’s easier to use that scale for your purposes. Reports by default are 500, so if you want to ensure that a particular report runs before others, set a lower value, and if you want to ensure it runs later, set a higher value. In this case, I’ll set 100 for light reports and 900 for heavy reports.
Open the Job Prioritization Wizard by going to Desktop -> Administration -> Configuration Objects -> Database Instances, right click on your primary Database Instance and select the Job Prioritization tab and then click New.
In the wizard that pops up, click Next on the first screen, then choose the Cost checkbox and Next again. You now have a screen that looks like a threshold editor that allows you to configure the range of Report Cost values that will be assigned to Light, Medium and Heavy. On the next tab, you can then assign the Light, Medium and Heavy groups to each of the 3 prioritization slots, Low, Medium and High.
My favorite trick to use this for is to configure specific reports to queue up, or only run 1 at a time. Some times a report just conflicts with another report, or a Dashboard has too much going on and it impacts the rest of the system. Using this trick, you can set those reports or data sets to Heavy/Low priority, then in Job Prioritization set the Low to only 1 thread. Despite having more threads available in the Medium/High range for other user requests, only 1 of these Heavy reports will run at a time.
Cube Dependency Scheduling
You can also use this trick if you have a schedule that builds an Intelligent Cube, and then you want some reports based on that Cube to execute when it’s complete. By setting the Cube and it’s reports to Low, and only having 1 thread for it, you can ensure that the reports are only run once the Cube is complete. This works for schedules, though you may want to use two separate schedules offset by about a minute to ensure the Cube goes first. The affinity of which reports runs first in a schedule is pretty loose.