Format Alignment: article

Showing posts with label article. 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
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Monday, March 12, 2012

filegroup question

I apologize if this shows up twice as the first time I sent it said there was
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?
There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?
|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan
|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)

>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?
SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/...615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavil
y
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article...0615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

filegroup question

I apologize if this shows up twice as the first time I sent it said there wa
s
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavil
y
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)

>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/ArticleID/40615/40615.html
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

filegroup question

I apologize if this shows up twice as the first time I sent it said there was
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Friday, March 9, 2012

Filegroup

I am looking for a Microsoft article to explain how to convert from primary
file group to multi file group. I have a SQL Server 2000 database with
numerous tables, stored procedures, and views. Additional articles would be
helpful to explain the benefit of using file groups and how they function.
Thank You,
Hi
Visually everything that needs to be said is on BOL under the heading
"filegroups".
What else do you need to know?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:16CF3E05-AB29-4B41-90F8-8CF2FDE48D80@.microsoft.com...
> I am looking for a Microsoft article to explain how to convert from
primary
> file group to multi file group. I have a SQL Server 2000 database with
> numerous tables, stored procedures, and views. Additional articles would
be
> helpful to explain the benefit of using file groups and how they function.
> Thank You,
>
>

Filegroup

I am looking for a Microsoft article to explain how to convert from primary
file group to multi file group. I have a SQL Server 2000 database with
numerous tables, stored procedures, and views. Additional articles would be
helpful to explain the benefit of using file groups and how they function.
Thank You,Hi
Visually everything that needs to be said is on BOL under the heading
"filegroups".
What else do you need to know?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:16CF3E05-AB29-4B41-90F8-8CF2FDE48D80@.microsoft.com...
> I am looking for a Microsoft article to explain how to convert from
primary
> file group to multi file group. I have a SQL Server 2000 database with
> numerous tables, stored procedures, and views. Additional articles would
be
> helpful to explain the benefit of using file groups and how they function.
> Thank You,
>
>

Friday, February 24, 2012

File output

I have read an Oracle article about the content management system they implemented, and was wondering whether a similar thing was possible with SQL Server.

The system basically stored the content of articles etc. in the database, as well as revisions etc., a stored procedure then generated an html page from the database content, my guess is that this then updated any necessary pages containing links etc.

My question is basically whether SQL Server (or T-SQL to be more precise) includes the ability to output the contents of fields into a file, so that when an SP was called it would generate the HTML for a page and output it to a file on the server. Thus avoiding the need to generate a page every time a request is received.

Thanks,
PaulI can think of a few ways to do this. I don't know of anything built in to SQL Server to handle this.

Do you want to call the sp from within the SQL server or from the command line?

Do you want the ability to (re)build only one file at a time or rebuild all files?

Do you have any metadata on the file names and locations?

Post back or "E" me directly.|||The idea would be that the stored procedure could be called when a record is inserted, thus when someone writes content it then creates the HTML page.

My guess is that there would also be the need to then update any linked pages, so if you wanted a link on the home page, the home page would also be re-written.

Anyway, it was nothing more than a thought, and was curious as to how SQL Server might support this kind of functionality.

Paul

File or assembly name Interop.DTS, or one of its dependencies, was not found.

I am trying to execute a DTS via COM according to the KB article
http://support.microsoft.com/?kbid=321525
but I am getting the exception "File or assembly name Interop.DTS, or one of
its dependencies, was not found." which is obviously a file not found
exception. I have sql server 2000 installed locally and I can run the
package from enterprise manager. Anyone got any ideas why I am getting when
I have the correct reference in the c# .net project?
--
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.Is the Interop.DTS.dll in your application bin folder? This should get
generated when you build your project.
You might also check out the examples at
http://sqldev.net/dts/ExecutePackage.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:OLP4UckJFHA.588@.TK2MSFTNGP15.phx.gbl...
>I am trying to execute a DTS via COM according to the KB article
> http://support.microsoft.com/?kbid=321525
> but I am getting the exception "File or assembly name Interop.DTS, or one
> of
> its dependencies, was not found." which is obviously a file not found
> exception. I have sql server 2000 installed locally and I can run the
> package from enterprise manager. Anyone got any ideas why I am getting
> when
> I have the correct reference in the c# .net project?
> --
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>|||yeap it was noob error, no dll in the bin directory :)
Ollie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:#$jJFpkJFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Is the Interop.DTS.dll in your application bin folder? This should get
> generated when you build your project.
> You might also check out the examples at
> http://sqldev.net/dts/ExecutePackage.htm
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
> news:OLP4UckJFHA.588@.TK2MSFTNGP15.phx.gbl...
> >I am trying to execute a DTS via COM according to the KB article
> >
> > http://support.microsoft.com/?kbid=321525
> >
> > but I am getting the exception "File or assembly name Interop.DTS, or
one
> > of
> > its dependencies, was not found." which is obviously a file not found
> > exception. I have sql server 2000 installed locally and I can run the
> > package from enterprise manager. Anyone got any ideas why I am getting
> > when
> > I have the correct reference in the c# .net project?
> >
> > --
> > Cheers
> >
> > Ollie Riches
> > http://www.phoneanalyser.net
> >
> > Disclaimer: Opinions expressed in this forum are my own, and not
> > representative of my employer.
> > I do not answer questions on behalf of my employer. I'm just a
programmer
> > helping programmers.
> >
> >
>

Monday, March 19, 2012

Filegroups and Joins

Filegroups and Joins

Filegroups and Joins

Monday, March 12, 2012

filegroup question

Filegroup question

Filegroup question

filegroup question

Filegroup question

filegroup question

Friday, March 9, 2012

Filegroup

Filegroup

Friday, February 24, 2012

File output

File or assembly name Interop.DTS, or one of its dependencies, was not found.

Format Alignment

Blog Archive

About Me