Sql-server – Database defragmentation and autogrowth settings

maintenanceshrinksql serversql-server-2008-r2

We do have some maintenance plan for our SQL Server 2008 r2 express.
Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation more than 20.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.
If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.

And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.

This is what we are doing till now.But we found that autogrowth events are resource intensive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.
Also there is another problem if I do monthly de-fragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?
Please tell me a solution.

Best Answer

if any table has page count more [than] 50 [pages]

That is still extremely small. I typically don't take fragmentation into account for any index with less than 1000 pages.

and avg_fragmentation_in_percent > 30 then the index is REBUILD

That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5% to 30%.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL

Why are you doing this? Why is it a problem if a log file is greater than 2 MB? That's extremely small. Not to mention, when you switch to SIMPLE, shrink, and then back to FULL recovery you are breaking the log chain. You could get yourself into serious trouble for point in time recovery. Size your log files appropriately, and allow for autogrowth only in the case of emergency. Shrinking a database file should only be done in extremely corner cases, and by no means part of a routine maintenance schedule.

But we found that autogrowth events are resource incentive and it should not happen repeatedly

Correct. That is where sizing comes into play. Monitor and get alerted when a certain percentage of space is being used. That way you can plan your manual file growth operations for a maintenance window to minimize end-user impact.

and 10% for ldf file which is default value while creating new database

Even though a percentage file growth is default, I recommend against it. You don't want to have a variable amount of file growth. If you must fall back on autogrowth, then you want to know exactly how much the file will grow by. Not to mention, with growth of a log file you will have a variable amount of virtual log files (VLF) with the additional space. You want to smartly plan this, and this can be done through a fixed amount of file growth.

Should i set autogroth so that it happens only once a day,week or month etc

Set autogrowth for a fixed amount of space (not percentage), and only you can determine what is a good amount. Too small, you will have frequent autogrowth with minimal end user impact, but happening often. Autogrowth set too high, then you'll have less frequent growths but when it does happen the impact will be for a longer duration.

My recommendation? Monitor the space used in your database files. When it hits a certain percentage (say, 80% space used) you should be alerted. And then schedule a manual growth during a maintenance window.

Related Solutions

Sql-server – SQL Server query performance affected by rebuilding an un-fragmented index

It sounds like the rebuild of the index is a red herring, especially given it was non-clustered. The rebuild is more likely to be invalidating a bad query plan in cache, and on re-run the good query plan is generated and used again.

Captue the query plan when the performance is bad and then after the rebuild and compare them, I suspect you will see a different plan.

That would be my first step - e.g. demonstrate what changes to the plan due to the index rebuild, or prove there is no change on the plan.

Sql-server – SQL Server ‘Create Database’ statement. How to inherit autogrowth settings

For SQL Server 2005+ You can use the following:

SELECT FILE_ID, type, type_desc, 
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE, 
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END  AS MAXSIZE,
CASE is_percent_growth
    WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
    WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO

This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.

SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:

CREATE DATABASE [aaa] ON  PRIMARY 
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:

ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO

This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).

I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.

Good luck,
Roi

Best Answer

Related Solutions

Sql-server – SQL Server query performance affected by rebuilding an un-fragmented index

Sql-server – SQL Server ‘Create Database’ statement. How to inherit autogrowth settings

Related Question