Format Alignment: placing

Showing posts with label placing. Show all posts

Monday, March 19, 2012

Filegroups and Joins

Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
LeilaThat's correct. You cannot direct a table or index to a particular file -
only to a filegroup. What the author probably meant to say was to place the
tables into separate filegroups on different drives.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
Leila|||Leila
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I read this tip in a article:
> "For very large joins, consider placing the tables to be joined in
> separate physical files in the same filegroup"
> How can I force SQL Server to create a table in filegroup on a particular
> file? As far as I know, I can tell SQL Server to create table on desired
> filegroup, but if that filegroup contains more than one file, is it
possible
> to put table on a particular one?
> Thanks in advance.
> Leila
>|||Thanks Uri,
But you have placed two tables on two different filegroups. The author
mentioned that the tables can be placed in two different files, on the SAME
filegroup!
Leila
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Leila
> CREATE DATABASE test
> GO
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
>
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I read this tip in a article:
> > "For very large joins, consider placing the tables to be joined in
> > separate physical files in the same filegroup"
> >
> > How can I force SQL Server to create a table in filegroup on a
particular
> > file? As far as I know, I can tell SQL Server to create table on desired
> > filegroup, but if that filegroup contains more than one file, is it
> possible
> > to put table on a particular one?
> >
> > Thanks in advance.
> > Leila
> >
> >
>|||The author was wrong. You cannot place an object on a particular file, only
a particular filegroup.
The space for the object is then allocated more or less evenly across all
files in that filegroup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> Thanks Uri,
> But you have placed two tables on two different filegroups. The author
> mentioned that the tables can be placed in two different files, on the
> SAME
> filegroup!
> Leila
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> Leila
>> CREATE DATABASE test
>> GO
>> ALTER DATABASE test
>> ADD FILEGROUP ww_Group
>> GO
>> ALTER DATABASE test
>> ADD FILE
>> ( NAME = ww,
>> FILENAME = 'D:\wwdat1.ndf',
>> SIZE = 5MB,
>> MAXSIZE = 100MB,
>> FILEGROWTH = 5MB)
>> TO FILEGROUP ww_Group
>> create table test..test(id int identity) on [primary]
>> create table test..test_GR(id int identity) on ww_Group
>>
>>
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I read this tip in a article:
>> > "For very large joins, consider placing the tables to be joined in
>> > separate physical files in the same filegroup"
>> >
>> > How can I force SQL Server to create a table in filegroup on a
> particular
>> > file? As far as I know, I can tell SQL Server to create table on
>> > desired
>> > filegroup, but if that filegroup contains more than one file, is it
>> possible
>> > to put table on a particular one?
>> >
>> > Thanks in advance.
>> > Leila
>> >
>> >
>>
>|||Thanks Kalen,
What about log files? If I have more than one log file for my DB, are the
logged information written in multiple files or the log files are filled one
by one?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> The author was wrong. You cannot place an object on a particular file,
only
> a particular filegroup.
> The space for the object is then allocated more or less evenly across all
> files in that filegroup.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > Thanks Uri,
> > But you have placed two tables on two different filegroups. The author
> > mentioned that the tables can be placed in two different files, on the
> > SAME
> > filegroup!
> >
> > Leila
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> Leila
> >> CREATE DATABASE test
> >> GO
> >> ALTER DATABASE test
> >> ADD FILEGROUP ww_Group
> >> GO
> >> ALTER DATABASE test
> >> ADD FILE
> >> ( NAME = ww,
> >> FILENAME = 'D:\wwdat1.ndf',
> >> SIZE = 5MB,
> >> MAXSIZE = 100MB,
> >> FILEGROWTH = 5MB)
> >> TO FILEGROUP ww_Group
> >>
> >> create table test..test(id int identity) on [primary]
> >> create table test..test_GR(id int identity) on ww_Group
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> > Hi,
> >> > I read this tip in a article:
> >> > "For very large joins, consider placing the tables to be joined in
> >> > separate physical files in the same filegroup"
> >> >
> >> > How can I force SQL Server to create a table in filegroup on a
> > particular
> >> > file? As far as I know, I can tell SQL Server to create table on
> >> > desired
> >> > filegroup, but if that filegroup contains more than one file, is it
> >> possible
> >> > to put table on a particular one?
> >> >
> >> > Thanks in advance.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||One by one. SQL Server "rattles through the log files like a snake", where the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Love the analogy, Tibor! ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23vZOx3tEHA.1356@.TK2MSFTNGP11.phx.gbl...
One by one. SQL Server "rattles through the log files like a snake", where
the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message
news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled
one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across
all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Friday, March 9, 2012

FileGroup

Hi,
There is a table which is about 50GB is size.
I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server.
Is it possible to place this particular table into a searate drive to increase the performance even more?
Basically my question is: is it possible to place tables into separate .ndf files?
ThanksYes, Microsoft did a great whitepaper about exactly this thought a few years ago. I'm not sure if they made it public or if it was "restricted publication" due to containing customer specific details.

The general gist of the idea is to create specific filegroups for specific devices, then put the SQL objects on those filegroups.

One area that often helps more than moving specific tables to a given drive is to segregate data pages (either the heap or the clustered index) onto one set of spindles (aka drive) and the indicies onto a different filegroup. This allows parallel IO to occur for even single table changes (assuming you have enough CPUs and IO channels)!

-PatP|||I think I am now confused.
Could you please elaborate further and do you know of a link regarding this issue?
Thanks|||We can create two filegroups or more while creating database. The following SQL Statements maybe help you:

CREATE DATABASE database_name
[ ON
[ PRIMARY ] [ <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <filespec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]

Here in the block <filespec>,you have to give the file path,then you can locate the files into different disk.

Then referencing the following SQL statements:

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]

So you store datum in different disk.

Good luck!|||Yes, this is what I was after.
This seems to be a very powerfull stuff.
Thank you

Monday, March 19, 2012

Filegroups and Joins

Filegroups and Joins

Filegroups and Joins

Friday, March 9, 2012

FileGroup

Format Alignment

Blog Archive

About Me