DBStats & You: A Tale of Database Woes and How to Diagnose Them (Part 2 Top Executed Business Processes)

Body

Why is my database so big? Understanding the DBStats report and what it can do for you. (Part 2 Top Executed Business Processes)

The first section of the DBStats report is the Top Executed Business Processes. The title is a bit of a misnomer. It doesn't just show the top executed business processes, it shows all of the executed business processes in the database sorted by number of executions.

I use the pdf format for the DBStats report. For this part of the report the HTML version isn't bad. The xls format is very difficult to read. My examples are from the pdf format.

The Top Executed Business Processes section can give information about where we are using space in the database, which can help us find the best place to reduce. It can also give us clues about processes that may be stuck and taking up space.

Finding space wasters - Total Persisted Steps

Take a look for the processes with the highest number of persisted steps. These are a good place to start to reduce database size.

Here are a few examples of what we know from the report and how we can use that to reduce the size of the database.

Schedule_JPMPickup.

This process ran 10159 times over about 7 days. That averages about 173 steps per run. (Total Persisted Steps / Total Executions)

It looks like the lifespan is set to 7 days. Start by seeing if this can be reduced. The default lifespan may be 7 days; Does this process need to be stored that long? If we reduce the lifespan to 2 days, that will save 70% of the space being used for this process right there.

Check to see if this really needs to run once a minute. From the name it is checking to see if there is something to pick up. Can the interval could be extended? Is once every 5 minutes enough? Once every 10? Consider putting an exclusion in your schedule. If you know that the file you are picking up is only going to come in between 3pm-9pm, consider putting an exclusion on the schedule to not run between 10pm and 2pm. Reducing the number of times this runs will reduce the amount of data that is persisted to the database.

This process runs an average of 173 steps. Check to see if you can reduce the persistence level of the process. Are there any steps that you can remove from the process?

JPM_ProcessInboundABC and JPM_ProcessInboundABC_NoEDI,

These also have high Total Persisted Step counts. It doesn't look like these are scheduled processes though, so there is probably no schedule to change. The rest of the suggestions apply to these. Try reducing the lifespan and persistance.

The Total Persisted Steps is a good way of looking at what processes are using the most space in the database. One thing to keep in mind though is steps are not always the same size between processes. A process that takes a 128K file that is posted through https is likely going to be storing less data per step than one that is handling a 50MB file that you are storing in the database. Even so, this is a good place to start looking for places you are storing extra data that you don't need.

Next week: What do the Minimum and Maximum State Dates tell us? And why do I have a process from three year ago here?