August 26, 2008 at 11:51 pm
Comments posted to this topic are about the item Creating Databases
August 27, 2008 at 12:26 am
Hi. Nice to have someone to go around the basics for a while.
One thing struck my eye though.
Space needed for database.
10 % and autogrow is easy but 10 % of 100 MB is 10 MB, 10 % of 100 GB is 10 GB meaning that a new database is growing fastest in it's early days and while you, as a dba, must monitor database growth, you should take a more careful look at space requirements when creating a new database, maybe the database doesn't need so much growth after one year or so.
My opinion is that a fixed growth is more useful and you should always reserve space enough to survive at least a year without having to allocate more space from disks. I prefer not to leave space allocation to Sql Server's job while it causes overhead specially with disk i-o consuming applications.
At firs I prefer to double the space if db is growing faster than estimated, which is mostly because the space calculatons are not accurate and something is missed.
Getting more disk space may also be tricky afterwards even if you've got a SAN or some other network disk array system while you may have to allocate a totally new volume and transfer databases to it.
So you should always have decent space calculations made with application and customer business specialists before taking any action creating databases for them.
Disk is cheap, downtime is not.
PS. While this is a basic presentation with a lot of defaults, data and logs should never be on the same disk and if you have only one disk use at least different folders and in the case that was presented in the article tell you customers that the reliability and maybe performance also is low.
Disk is cheap, downtime is not.
August 27, 2008 at 12:59 am
One thing I would change almost always now that I've learned a bit is to create straight from the start a second filegroup. Call it Data. Then set Data as default.
The reasoning behind is that my sys tables are in the Primary filegroup together with their indices. As all other (production) tables and indices go into Data there is a good chance to be able to recover the master section of the database (aka Primary) on its own in case of serious corruption.
Another reason is that with the backup operation I can do a file backup (1 logical filegroup = 1 physical file). For a data warehouse environment this can tremendously recude the backup time if the backup only works on the file that is currently written too while the others are archives.
The downside to this approach is that a restore operation takes longer but this in a data warehouse environment can be acceptable (ymmv).
August 27, 2008 at 4:28 am
Good article. Really after a long time we saw an article on basics. Great work Andy!!!!
August 27, 2008 at 6:36 am
I'm going to voice a me too that percentage based resizes are bad IMO... A good DBA should be monitoring every database and be aware of not only size but also growth rates and trends.
just setting to 10% is really a poor way to manage. 10% of 10gig is a lot different than 10% of 500gigs. based on knowledge of growth rates that 10% of 500 might not be needed.
the other issue i wanted to point out was that atleast for us we always set every database to use Forced Parameterization. We have seen a pretty good performance boost from every db by having this checked.
thanks!
August 27, 2008 at 6:40 am
Its good to go over the basics. Its the old 80/20 rule, and most problems come from failed or misunderstood basics.
The more you are prepared, the less you need it.
August 27, 2008 at 7:07 am
I don't know if its just me or not, but the picture's aren't there. Good article by the way.
August 27, 2008 at 8:51 am
Knut Boehnert (8/27/2008)
One thing I would change almost always now that I've learned a bit is to create straight from the start a second filegroup. Call it Data. Then set Data as default.The reasoning behind is that my sys tables are in the Primary filegroup together with their indices. As all other (production) tables and indices go into Data there is a good chance to be able to recover the master section of the database (aka Primary) on its own in case of serious corruption.
Another reason is that with the backup operation I can do a file backup (1 logical filegroup = 1 physical file). For a data warehouse environment this can tremendously recude the backup time if the backup only works on the file that is currently written too while the others are archives.
The downside to this approach is that a restore operation takes longer but this in a data warehouse environment can be acceptable (ymmv).
That, Knut, is a very interesting concept. I'm definitely going to have to do some thinking and experimenting with that.
Thanks for the post!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 27, 2008 at 8:55 am
Good article, Andy. Myself, I'm very explicit in telling my developers that putting spaces in database or object names on any of my SQL Servers may be punishable by death, or at least the inconvenience of having to update their code. 😀
Another problem that I've encountered is if a hyphen is used in an object name. I've had the system consider it a subtraction operation if you don't remember to set it off with braces, and fun ensues.
For object names, I stick with letters, numbers, and CamelCase. If you need to use an underscore, fine.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 27, 2008 at 11:53 am
Good point on the hyphen. I've found that object names with a hyphen need to be in brackets.
The more you are prepared, the less you need it.
August 28, 2008 at 6:32 pm
This article was really well written for what was there, but I was disappointed that it did not get into at least a little bit more depth on things like best practices for allocating file locations, etc,
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 29, 2008 at 6:51 am
Timothy, I may do a follow up article, but my intent was to really do a beginner level approach and I don't recommend use of multiple filegroups to beginners, they can add that complexity later.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply