One of the most important features to control in tuning your MicroStrategy environment is controlling Job Prioritization in the Intelligence Server. In the event that you have more concurrent jobs running than open slots on the database, queuing will occur. When a Job is in the Waiting status, that could be giving a very poor user experience. Fortunately, you do have some control to tell MicroStrategy which Jobs are more important than others. Today, I’ll talk about how the Job Prioritization works, what options you have, and the Job Prioritization strategy that I use in my environments.
Job Prioritization occurs at the Database Instance and is a Governing setting for sending queries to the database. While there are Governing settings for the Intelligence Server to control concurrent jobs, those are system wide settings and are generally set very high (~100). The reason is that the Intelligence Server doesn’t really have to do a lot of processing per job, at least compared to the amount the database has to do to retrieve the data.
Prioritization works by managing three slots: High, Medium and Low. By default, a job has Low priority. Jobs include not only Reports but also Prompt requests, and each Report on a Document counts as a separate job.
You can classify the priority of any given job in a number of ways, but the most direct is to setup the classification rules in the Job Prioritization Wizard. This can be found by opening the Database Instance for your Warehouse (Desktop -> Administration -> Configuration Objects -> Database Instances) and selecting the Job Prioritization tab, then clicking New. The wizard allows you to set global rules based on the type of job, the project it belongs to or the user requesting it.
The way the slots work is that a Low priority job can only use the Low slots, but a Medium can use the Medium+Low slots, and a High can use all 3 slots. In this way, if you have 2 High slots defined, but a 3rd High job comes in, it will take over one of the Medium or Low slots.
The very first thing I do is set Element Requests to High and open a healthy amount of slots, 10+ at least. Users are generally pretty tolerable when it comes to a report that takes 30s vs 2min, but a prompt that takes 1s vs 10s can be a disastrous user experience. I never, ever want a prompt to wait for anything, and it should always run immediately. These are generally very lightweight queries anyway, and even if the server is stressed with heavy jobs, it should still be able to handle a prompt.
Next, I set All Web / Desktop requests to Medium. These are my interactive jobs that users are running, so this is where the bulk of the work will be. This is truly where you’ll consult with your DBA for a healthy amount of slots here. In my environment, I have 1, which I’ll explain in a minute.
Finally, I set all Narrowcast/Scheduler requests to Low. These are subscriptions that no one is waiting on, and if the user receives that email at 8:00am or 8:30am, it’s usually all the same to them. Keep in mind that Low slots will be cannibalized by higher priority requests, so in my environment I have 9 here.
So why do I do the 1/9 approach? First, my DBA concluded that 10 concurrent jobs was a fair amount. If I have 10 users running simultaneous reports, even though all 10 are Medium jobs and I only have 1 Medium slot, the other 9 will still run using the Low slots. The difference is that I want subscriptions to have lots of bandwidth, but if a user comes along and runs a report, I don’t want them to have to wait in a huge subscription line, so I leave one slot open. If two users come through, then they may have a short wait, but will cut in front of the next Low job in the Low queue. Leaving only 1 Medium slot allows Subscriptions to run at nearly full throttle, but gives an interactive job by a user who is waiting a fast pass to the front of the line. In my environment, subscriptions are complete at least an hour before any users arrive at work, so that’s why I’m comfortable with the 1/9 approach. If my users were early birds and consistently arrived closer to my subscription window, I’d probably go to a 3/7 approach. This would allow more users to cut the line, and it would also throttle the number of subscriptions running at once. Because even if a user has their job execute immediately, the performance will be a lot worse when the database is running 9 other reports vs 7 other reports.
It’s important to note that projects that share the same Database Instance will share these same queues. So if you have your Dev and Prod projects on the same IServer with 5 open slots, then if Dev has 5 reports running, Prod won’t be able to run any reports.