sql server best practices: database creation

when you create a new database in with the default options, sql server creates two files, the data file and the log file, in a single filegroup called PRIMARY. the files are created in the DATA sub-folder of the directory where you installed sql server unless you override the defaults (as you should). the state of the PRIMARY filegroup determines the state of the database

why this is bad: by only having one data file, user databases are stored in the same location as system databases. the majority of data changes occur in the the user databases. if the drive fails in the middle of a write operation, your data file becomes corrupt and all databases within become unusable. while having separate data files for your system and user databases avoids the previous situation, corruption to any file in the PRIMARY filegroup makes the database unusable.

what to do instead: minimally, all new databases should be created with two filegroups, two data files and a log file. the filegroups and the log file should all ideally be located on separate drives (which are hopefully RAID drives but more on best practices for RAID configurations for data log files in a future post). place one data file in the PRIMARY filegroup. this data file will contain the system databases and has a file extension of .mdf by convention. place the other data file in the second file group. this data file will contain user databases and has a file extension of .ndf by convention. change the default filegroup from PRIMARY to the second filegroup so new objects are created in the .ndf data file.

how to make soap

disclaimer/warning - this soap is for clothing. i have no idea what would happen if you put it on your person but i know what the individual ingredients do so i don't recommend it

granny's homemade soap

1 3/4 quart lard

1 quart water

1 can lye

1 cup ammonia

3 tablespoons borax

cooking pan (2 inch sides)

dissolve borax in 1 cup boiling water

put lye in granite kettle and cover with water

allow to cool, then add the dissolved borax, lard and ammonia. stir for five minutes

pour mixture into pan with two inch sides and set in cool, dark place to harden

cut into bars and let sit for at least three weeks before using

sql server best practices: tempdb

despite all of my lovely certifications and ten years of experience as the company dba, i am constantly faced with sql server performance issues (deadlocking more than anything else) that challenge or completely go against what i thought i knew about best practices. today, i learned that the default settings for the tembdb system database are far from adequate; the settings need to be changed based on the server environment. this is something that i've known and preached about with user databases (optimal hard disk RAID configurations, separation of physical and log files, etc. - i will cover this in another post), but i only really knew that the tempdb is recreated whenever the server is started and that the default size of the database files should be set large enough such that auto-growth never occurs. the following are some things i didn't know:

• the number of tempdb physical files should be equal to the number of processors or cores on the server. as the name implies, the tempdb stores temporary information required by sql server to complete some operation such as intermediate result sets and temporary tables created in queries. if there is only one tempdb data file, only one processor will be used to read and write information. to make use of all of the available processors, create one physical file per processor and set them all to be the same size.
• since the tempdb is recreated every time the server starts, redundancy is pointless. put the tempdb files on the fastest drives available
• auto-growth of the tempdb is bad for the same reasons that auto-growth is bad for user databases (file fragmentation, processing halts while the file grows, etc.) but, since the tempdb is constantly used for operations, the heinousness of auto-growth of the files grows exponentially with server load. don't be stingy with the default size - allocate enough disk space to ensure that there will always be enough available without depending on growth
• edit: divide the maximum amount of memory allocated to sql server (server properties -> memory) by the number of logical processors to get the recommended size of each tempdb physical file

edit: great reference