This site is currently using a generated translation

Performance - Check how much your databases are growing

According to Gartner, about 80-85% of all performance problems are due to increasing data volumes according to various surveys, so it might be a good idea to see how much data volumes increase if we have performance problems. If we don't have a central repository or a fancy monitoring/configuration system, we can easily get this information from every single instance.

We'll look at how we can easily get a report on how much a database has grown by looking at MSDB's backup history and more specifically dbo.backupset. To make it easy to get the data and display in a good way we will create an SSRS report file .rdl that we can add to the Reports menu in SSMS, I have previously written how we can use the standard reports to quickly get important information about an instance and database.

We start by making an appropriate query against msdb.dbo.backupset. SSMS sends in some parameters we can use, including @ServerName and @DatabaseName. We use @DatabaseName to filter so that we only show the database we have selected. We also pick the compressed_backup_size column and display it and calculate the compression factor. If we don't use compressed backups, compressed_backup_size will show the same as backup_size and we will get a compression factor of 1. We take the last 1000 backups, if we do daily backups we get about 3 years of backups.

SELECT TOP 1000 database_name, backup_start_date,(backup_size / 1000000) as [Backup Size MB], (compressed_backup_size / 1000000) as [Compressed Backup Size MB],

(backup_size/compressed_backup_size) AS [Compression Ratio]

FROM msdb.dbo.backupset

WHERE type='D' and database_name= @DatabaseName

ORDER BY backup_start_date DESC

Now it's time to launch Data Tools (or BIDS as it was called before 2012) and create an SSRS report. We select New Project and the Report Server Project Wizard. We choose to point to the msdb database and create a tabular report. We call the report "Data Growth"

To get a better graphical presentation, we click on the Toolbox on the left side and drag out a Chart object and choose to create a line chart which we connect to the date and backup size/compressed backup size columns. We save down as a .rdl file and then we start SSMS and right click on a database, select Reports and Custom Repors. We browse to our report file, we get the usual security warning that there may be malicious issues out there in the real world, tick not to ask about this anymore and click Run. Our report now shows up under Reports regardless of which database we right clicked on, it also shows up when we connect to other instances so we now have a report we can use throughout our SQL environment.

If we right-click on a database, click Reports and select the "Data Growth" report, we can now see how the database has grown lately.

At the bottom we have chosen to keep the table of data so that we can also see the compression factor. If we have a fairly high compression factor it might be a good idea to use ROW or PAGE compression if we are running SQL Enterprise edition, we will come back to this later when we look more at performance optimization.

If you are using SQL 2005...

Backup compression was only introduced in 2008 so the above question will not work on a SQL 2005 because the compressed_backup_size column does not exist in the backup set. If you need a report for 2005 you can either remove the column or make a specific report for 2005.

Delete a custom report from the Report menu

Delete or move your .rdl file, click on the report again and SSMS will not find the report and ask you to remove it from the Reports menu

/Fredrik