When do my jobs run? How long are my jobs taking to complete? It’s often helpful to visualize what’s going on with your scheduled SQL Agent jobs to answer these types of questions. SQLjobvis from SQLsoft is a tool I like to keep handy to help me answer some of these questions.
SQLjobvis is a TINY (We’re talking under 400KB here!) application that can help you to visualize your job schedules. You connect to one of your SQL Server instances, and your SQL agent jobs are presented in a nice Gantt-style chart. You can sort your jobs in various ways, and filter what jobs are shown. You can choose your desired time ranges and display time in various increments.
Using SQLjobvis for Optimization
When looking for SQL agent job candidates for runtime optimization, a good place to start is to find out what jobs take the longest to run. Color coding in SQLjobvis can help to more easily identify conditions like this that you are interested in. In the example image you will see color codings of green for a normal job run, light blue for a ‘long’ job run – a run that took longer than my user-defined setting of one hour, and red for a job that failed.
Jobs that run simultaneously can also be an area of concern. I’ve seen the time it takes a job to complete on a resource-limited system increase tenfold when running alongside another job compared to when it’s run individually. To identify these situations in SQLjobvis you will notice a light coloring of the time bar up on top, where green indicates a single job and yellow indicates multiple jobs overlapping at that time period.
Having a nice graphical view of your SQL agent jobs makes it easier to tighten up your schedules as well. Say you’ve recently done some optimization on several of your ETL jobs and cut a couple of hours off of your total daily runtime. With SQLjobvis you can easily identify your time gaps and make plans to reschedule to make the best use of that open time. Being able to intelligently reschedule to get your jobs to complete earlier is a good thing, for as we all know, the sooner you can make your data available to your customers the better!
Try it out
If you’re looking for something to help you visualize your SQL agent job schedules, try out SQLjobvis. It’s small, it’s fast, and it’s free – three attributes I always like to see in an application. And if you use any other similar tools, feel free to share and tell us what’s good about them.