SQL 2008 Best practice : log files for databases
rcamarda
create one data file for each CPU. In addition, it states to
create .x25 - 1.0 data files for each CPU.
I am wondering if I should also create multiple log files for each
database I create.
I have a 4 CPU Windows 2008 running SQL Server 2008. I've created my
databases with 4 data files and one log file, and one data file for
PRIMARY. (I've also read its a good practice to seperate PRIMARY from
user data. I also split out my indexes from user data. this way I have
more control to where I can move my files to appropriate SAN storage
vs a monlithic data file.
Here is the DLL that I am developing:
USE [master]
GO
/****** Object: Database [NEW_DS_V6_TARGET] Script Date:
05/12/2009 07:13:30 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name =
N'NEW_DS_V6_TARGET')
BEGIN
CREATE DATABASE [NEW_DS_V6_TARGET] ON PRIMARY
( NAME = N'DS_V6_TARGET', FILENAME = N'F:\SQL Data\DS_V6_TARGET.mdf' ,
SIZE = 5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP [INDEXES]
( NAME = N'DS_V6_TARGET_indexes1', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_indexes1.ndf' , SIZE = 4GB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1GB%),
( NAME = N'DS_V6_TARGET_indexes2', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_indexes2.ndf' , SIZE = 4GB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1GB%),
( NAME = N'DS_V6_TARGET_indexes3', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_indexes3.ndf' , SIZE = 4GB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1GB%),
( NAME = N'DS_V6_TARGET_indexes4', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_indexes4.ndf' , SIZE = 4GB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1GB%),
FILEGROUP [USER_DATA]
( NAME = N'DS_V6_TARGET_user_data1', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_user_data1.ndf' , SIZE = 13GB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1GB),
( NAME = N'DS_V6_TARGET_user_data2', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_user_data2.ndf' , SIZE = 13GB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1GB),
( NAME = N'DS_V6_TARGET_user_data3', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_user_data3.ndf' , SIZE = 13GB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1GB),
( NAME = N'DS_V6_TARGET_user_data4', FILENAME = N'F:\SQL New Database
\Data\DS_V6_TARGET_user_data4.ndf' , SIZE = 13GB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1GB)
LOG ON
( NAME = N'DS_V6_TARGET_log', FILENAME = N'G:\SQL Logs
\DS_V6_TARGET_log.ldf' , SIZE = 5GB , MAXSIZE = 2048GB , FILEGROWTH =
1GB)
END
GO
--ALTER DATABASE [NEW_DS_V6_TARGET] SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE [NEW_DS_V6_TARGET] MODIFY FILEGROUP [USER_DATA]
DEFAULT
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NEW_DS_V6_TARGET].[dbo].[sp_fulltext_database] @action =
'disable'
end
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ARITHABORT OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ENABLE_BROKER
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET AUTO_UPDATE_STATISTICS_ASYNC
OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET DATE_CORRELATION_OPTIMIZATION
OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET READ_WRITE
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET RECOVERY SIMPLE
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET MULTI_USER
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [NEW_DS_V6_TARGET] SET DB_CHAINING OFF
GO
Erland Sommarskog
> I've read in the BOL best practice creating the tempdb; it states
> create one data file for each CPU. In addition, it states to
> create .x25 - 1.0 data files for each CPU.
> I am wondering if I should also create multiple log files for each
> database I create.
If you are looking for load balancing, there is little point, since
SQL Server writes to the log in a circular fashion. The about only
reason you would have a second log file is when you need more log
space, but there is no more space available on the disk where you
currently have your log file.
For more tips on how to configure your database, I recommend that you
check out SQL Server MVP Paul Randall's blog at
http://www.sqlskills.com/blogs/paul/. There is a wealth of information.
To put it mildly.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
rcamarda
> rcamarda (robert.a.cama...@gmail.com) writes:
> > I've read in the BOL best practice creating the tempdb; it states
> > create one data file for each CPU. In addition, it states to
> > create .x25 - 1.0 data files for each CPU.
> > I am wondering if I should also create multiple log files for each
> > database I create.
>
> If you are looking for load balancing, there is little point, since
> SQL Server writes to the log in a circular fashion. The about only
> reason you would have a second log file is when you need more log
> space, but there is no more space available on the disk where you
> currently have your log file.
>
> For more tips on how to configure your database, I recommend that you
Thanks Erland. Quiet a wealth of information on the blog site.
I checked out his site before posting a follow up question:
I read that splitting out the indexes from user data is also a good
Idea. In my data warehouse, my indexes slightly larger in storage than
my data.
Are indexes and data accessed in a similar way? IE if I create 4 data
files for a group called INDEXES then create all my indexes in that
new group instead of my user_data or primary, would this be a good
idea?
TIA
Erland Sommarskog
> I read that splitting out the indexes from user data is also a good
> Idea. In my data warehouse, my indexes slightly larger in storage than
> my data.
> Are indexes and data accessed in a similar way? IE if I create 4 data
> files for a group called INDEXES then create all my indexes in that
> new group instead of my user_data or primary, would this be a good
> idea?
It might. What is the very best answer is difficult to tell. You could
probably spend an entire year benchmarking various configurations.
Non-clustered indexes are in many ways just a materialised view of a
subset of the columns in the table. If a query can be computed by using
an index along, SQL Server will not read the data pages at all.
This leads to the observation that if your database is full of covering
indexes, and there are no queries that access the data pages, all queries
will be on the INDEX filegroup.
Then again, it depends on what you are optimising for: reads or loads?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online: