January 19, 2005 at 1:35 am
Hi All,
What are the steps required to create multiple data files/log files and [create/move] tables/indexes to data/log files?
Thanks
Jag
January 19, 2005 at 4:41 am
You would first create the filegroup and then add a new datafile to the filegroup. You can do this through EM or through QA thus...
USE master
GO
ALTER DATABASE DB1
ADD FILEGROUP DB1FG1
GO
ALTER DATABASE DB1
ADD FILE
( NAME = DB1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP DB1FG1
GO
Then when you create an index, or a table you can specify the filegroup on which to place it, ie...
CREATE TABLE TestTable (TestColumn varchar(25))
ON DB1FG1
GO
CREATE NONCLUSTERED INDEX NC_Test ON TestTable(TestColumn) ON DB1FG1
GO
January 19, 2005 at 5:01 am
Great Nicholas. Good examples
Thanks a Lot
January 19, 2005 at 7:29 am
Deleted
January 19, 2005 at 7:31 am
Yuo can also set the new filegroup as default, thereby cause all new objects to be placed there (unless specified differently in the create statement).
January 21, 2005 at 9:24 am
You should benchmark performance before and after making these changes.
If you have your data split into multiple filegroups and files, SQL will start an i-o stream for each file. In my experience, most servers have limited i-o capacity, and increasing the number of i-o streams just ends up with queueing on the disk controller rather than increased performance.
If you have multiple disk controllers, or your server is SAN-attached, you may be able to use multiple filegroups (i.e. multiple i-o streams) and see improved performence. Otherwise you will more likely see a slowdown and it is better to keep to just 1 file.
If you have experience of Unix or Mainframe, where multiple i-o streams are a standard way of improving performance, this may seem perverse, but many people have found that a single .mdf file gives best performance for SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 22, 2005 at 2:18 pm
Why should this be "perverse"? When you have a single mdf file and spread this over multiple disks, all table and indexes will benefit equally from. However when place a single table or indexes on separate filegroups only these will benefit. Sure, this might also improve performance, but it doesn't necessarily boost it. On the other side it makes maintenance more difficult.
But what I would like to know from Jag is, why do you think you will benefit from having multiple log files? What is your reasoning here?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply