Muller Jannie

About myself and everyone else.

Gantt Chart report – revisited.

I made a post about 1 year ago about building a Gantt chart to have a look at SQL jobs or anything with a start date and duration to see how times overlap.

The previous version I did have some funky parts like retries, prerequisites etc.

I’ve done a simpler one simply because instead of using a lot of hard work and bad code I’m using a recurring CTE for the date bits in order to be able to do the same thing in less code.

Below is a picture of what the output looks like.

That’s basically an indication on when my restores run and how I can optimize the server load by filling in the blanks.

It’s done with reporting services.

Below is the code.

The recursion can be lowered, this depends on your grain, if you use minutes you have recursion of 1440 for 1 day. So depending on your requirement you can modify this.

This is easier since you only need a start date, end date and that’s it. It means if you have those two dates available you can pretty much stick anything into this Gantt and use the visuals to get a better understanding of what is going on.

If you are a DBA you can use it to make sure your backups, maintenance jobs overlap or don’t overlap depending on their purpose. If you are in the BI space you could probably use it to get an idea of login \ logoffs on your site. You could use anything with a start and end date.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on October 29, 2012 by in SQL Server and tagged , , .