BorderLayoutBoxedLayoutOpenLayoutMaximum textMedium textSmall text


Register
Monday, March 15, 2010
MyStreamMinimize
Print  

The Worst Default Setting in SQL Server

Posted by Jason on Saturday, January 17, 2009 to
1325 Views | 9 Comments | Article Rating

image What is your guess? The install path? The default database location? The default fill factor.  I would say a 1MB default autogrowth if you create a database with the GUI. I don’t know how many times I have seen 20, 50, 100GB with 1MB autogrowth when I know they started out at the other bad default of 2MB initial size. This can cause hundreds or thousands of file fragments.

Linchi has already shown that file fragmentation does matter too much on enterprise SAN’s but a lot of installations do not have that luxury.

Tonight, on this festive Friday night I decided to party so I have created a policy that you can run against your servers to see if they are in violation of this bad practice.

 

Here is the code:

 

DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set 
@object_set_name=N'AutoGrow Setting_ObjectSet_1'
, @facet=N'DataFile'
, @object_set_id=@object_set_id OUTPUT
SELECT @object_set_id

DECLARE @target_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_target_set 
@object_set_name=N'AutoGrow Setting_ObjectSet_1'
, @type_skeleton=N'Server/Database/FileGroup/File'
, @type=N'FILE'
, @enabled=True
, @target_set_id=@target_set_id OUTPUT
SELECT @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level 
@target_set_id=@target_set_id
, @type_skeleton=N'Server/Database/FileGroup/File'
, @level_name=N'File'
, @condition_name=N''
, @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level 
@target_set_id=@target_set_id
, @type_skeleton=N'Server/DATABASE/FileGroup'
, @level_name=N'FileGroup'
, @condition_name=N''
, @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level 
@target_set_id=@target_set_id
, @type_skeleton=N'Server/DATABASE'
, @level_name=N'DATABASE'
, @condition_name=N''
, @target_set_level_id=0


GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy 
@name=N'AutoGrow Setting'
, @condition_name=N'dbfile'
, @policy_category=N''
, @description=N''
, @help_text=N''
, @help_link=N''
, @schedule_uid=N'00000000-0000-0000-0000-000000000000'
, @execution_mode=0
, @is_enabled=False
, @policy_id=@policy_id OUTPUT
, @root_condition_name=N''
, @object_set=N'AutoGrow Setting_ObjectSet_1'
Select @policy_id


GO

Edit: As Chris pointed out, the condition is needed as well. Here it is:

 

 

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition 
@name=N'dbfile'
, @description=N''
, @facet=N'DataFile'
, @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>GT</OpType>
  <COUNT>2</COUNT>
  <Attribute>
    <TypeClass>Numeric</TypeClass>
    <Name>Growth</Name>
  </Attribute>
  <Constant>
    <TypeClass>Numeric</TypeClass>
    <ObjType>System.DOUBLE</ObjType>
    <Value>1024</Value>
  </Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

'

email it! |   |   |   |  | 
Permalink     9 Comments  

Rate this Post:
COMMENTS:

u r a party NimL!

posted @ Saturday, January 17, 2009 2:10 AM by scott herbert

> I have created a policy that you can run against your servers to see if they are in violation of this bad practice.

Wouldn't you want to make sure you WERE in violation of a bad practice? :O)

Very good idea ... thanks for continuing to post!

-Chris

posted @ Saturday, January 17, 2009 4:25 AM by Chris Leonard

Great idea for a policy. I hate the 10% autogrowth of the log file. I see a lot of folks think the 1MB is a bad idea so change the data file growth to 10% as well.

Huge fan of setting the database the proper size (or close to it, for the medium to long term) and trying to avoid growths and then having "smart" growths.

This was even more horrid in 2000 without instant file initialization :)\

posted @ Saturday, January 17, 2009 4:55 AM by Mike Walsh

Another one is the default recovery model being set to Full. A lot of DBs don't need transaction log backups.

posted @ Saturday, January 17, 2009 8:06 AM by K. Brian Kelley

Chris, you are right. I pulled the ExecuteSQL() facet while troubleshooting. I'll update after some house cleaning.

posted @ Saturday, January 17, 2009 9:00 AM by JasonMassie

Fixed!

posted @ Saturday, January 17, 2009 9:45 AM by JasonMassie

Auto-Shrink and Auto-close. Hands down.

posted @ Monday, January 19, 2009 12:04 PM by Kevin3NF

So we can resume this way: Heavy file fragmentation with high speed SANs does not affect the I/O subsystem: the throughput is quite constant because the SAN splits the data itself into small chunks distributed all over the RAID.
However I wish to know if heavy fragmentation introduce or not at least some overhead in the filesystem subsystem and if CPU consumption in kernel space is constant. Perhaps this vary between different filesystem types.

posted @ Wednesday, January 21, 2009 2:04 AM by Ludovico Caldara

Comments from the following blog entry: http://www.brentozar.com/archive/2009/01/sql-server-and-cloud-links-for-the-week-9/

posted @ Friday, January 23, 2009 6:36 AM

Name (required)

Email (required)

Website


Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]

Home|Upcoming Events|Feeds | Privacy Statement | Terms Of Use
Copyright 2006 by Statistics IO, My SQL Server Blog