Tag: administration

I wrote a post recently on capacity planning, and then thought of one more thing. In addition to planning for the future for new systems, you also need to regularly monitor your existing ones.

Planning for the future, IMHO, isn’t something you want to do when you have a crisis, like running out of disk space. A good DBA is proactive, and looking to monitor resource usage and extrapolate out regularly to get an idea of

1. when you’re overwhelm your current hardware
2. how big to go on the next upgrade.

Nothing worse than your boss complaining about performance, you recommending an upgrade and then having no idea what to ask for. You also don’t want to get caught in emergency situations when nothing has changes in your workload, other than you weren’t paying attention.

I used to do this in a few ways, and there are numerous ones today. You can use a product like SQL Monitor, you can use the MDW from Microsoft if you have EE, you can use other products or roll your own. I have done all of these, except MDW, and they have all worked well.

In terms of memory, I haven’t done this in some time, so I’ll let you get details from another source on how to monitor the usage over time on the modern versions. Here’s a good thread to get you started.

In terms of CPU, I tend to monitor the raw CPU percentage over time, using a broad average across a whole day, watching the peaks. I would trend this out for 3-4 months, and see if it is increasing. Since CPUs can be hard to upgrade, you want to know this well in advance.

Disk Space

This is the big one, and quite embarrassing when you can’t capacity plan here. There are numerous ways to do this, but here’s what I’ve done.

I monitor backup size every day and store it in a small administrative database. If I move the database, I move the backup information to the new instance as well. You can query the msdb tables for the sizes.

SELECT DATEPART( yyyy, backup_finish_date) 'backup year' , DATEPART( mm, backup_finish_date) 'backup month' , DATEPART( dd, backup_finish_date) 'backup day' , database_name
, SUM(backup_size) 'backup_size' , type FROM msdb.dbo.backupset
 GROUP BY database_name
 , type , DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) ORDER BY DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) , database_name

There’s a quick query, and you can perform some summaries of sizes by types of backup, convert to GB, etc. I used to run a similar query every day and do three things:

• store the results
• compare the last backup to the one before it.
• extrapolate out the growth of backups to reach the disk size

Obviously I wanted to track the results, and I would monthly create an old average of the sizes from last year so I wasn’t storing crazy amount of data.

I compared the backup each day with the previous one to catch weird things. If the backups differed in size by more than 20%, I raised a flag. This was to catch changes in process, or potential errors from data loads or imports. Anything that changes 20% a day can be a problem, so this allowed me to find problems quickly.

Lastly, I aggregated all backups on the instance, extrapolating out the growth across weeks, and estimating when I’d fill the disk. When this was inside of 2 months, I knew I better start asking for more space.

I always enable autogrow on my databases. However, it’s there for emergencies, not as a space management tool. I monitor disk space, and I grow my files manually as space runs low. I want to control the growth, but in the event of a runaway process or some unexpected event, I want autogrow enabled to hopefully prevent a database crash.

What level of autogrow do you enable? That was a question I saw recently and it made me stop and think a bit. I asked the question on Twitter, but got very few responses and no real guidance from others.

In my mind, you want to enable a specific level of autogrow that will be likely to handle something unexpected in a single, or maybe a couple growths.

The two options for autogrowth are:

• percentage
• fixed size growth

In general I think a fixed size growth is the setting to choose. This offers more control and as your database size grows, it’s unlikely you want to grow at a percentage. A 10% growth of a 100MB database is 10MB, almost a rounding error on many of today’s drives.

However a 10% growth on a 2TB database is 200GB, which could easily exceed the free space on the drives that make up a file,which is where you set the autogrow specifications:

As the file size increases, the disk space goes down, but the percentage growth goes up as well, exacerbating the problem.

So what are the guidelines?

I think that you have to look at the data growth and each database and what is possible for data growth in that database. Some some database that does ETL work I think it’s more likely you could have an unexpected large or duplicate import in a process that would cause larger growth.

However the question I was asked talked about setting a guideline for hundreds of databases, where in the short term it’s impractical to review every database. For those, I offer up these guidelines, though I’m happy to have someone give better guidance.

These are guesses on my part, based on some experience, but the largest database I ever managed was a 600GB one, so I’m guessing on the TB scale.

I’d welcome your comments and experience in this area.