multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple backslashes in physical file name

Accidentally, I've managed to have this as part of a Create Database:

... FILENAME = N'C:\MSSQL\Data\\\testdb_Log.LDF' ...

, notice the triple backslash. The Create Database statement works fine,
and sp_helpdb says the log file name is:

C:\MSSQL\Data\\\testdb_Log.LDF

I noticed the MSDOS command prompt also allows multiple backslashes,
they're reduced to one when performing the command and I guess
SQL Server does the same thing, so no problem so far really.

But is it supposed to work this way? Quite confusing, isn't it?Not sure if it supposed to work that way, but it would get quite confusing after a while. It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.|||This sounds like a question for Old Man Phelan.|||It gets better... This actually goes back to the Unix days, and has to do with how pathing is logically based. Just for jolly factors, try to explain the difference between:dir c:\windows\\system32
dir c:\\windows\system32Why does one work, and one fail? What causes the time lag? What did you really do?

Answers will follow, but I'd love to hear folks try to talk this fiasco out a bit first.

-PatP|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.|||Hello Pat I can proof both of your lines correct ;)

check this
for
dir c:\windows\\system32

c:\windows\ md (alt+092)system32

dir c:\\windows\system32

c:\md (alt+092)windows

Now both are valid folders and path too... ;)
Put your Numlock on and type numbers from there|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP|||It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.

That's how I noticed it, I was trying to attach a database
file when I got an error message because the referred Log file didn't exist. Then I happened
to notice the double backslash in the Log file name. This wasn't the cause of the error though, the file
was simply missing. But, no idea how the double backslash got there!|||But, no idea how the double backslash got there!My first guess would be a typo (fat fingers, flying furiously, flubbing fiendishly). My next guess would be a keyboard problem (key bounce). Next would be a numeric directory name typed with Num Lock turned off... After that, I'd give up!

-PatP|||Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP

. refers to the current directory you are in (i.e. c:\windows\system32\.\ is the c:\windows\system32 directory.)

.. refers to the parent directory of the one you are in (i.e c:\windows\system32\.. refers to c:\windows directory.)

so the ..\..\ would put you at the c:\WINDOWS folder.
[EDIT ... I typed too fast] ... ..\..\ puts you at the root of the C drive!|||Hmm. I am still a touch confused about the empty reference. When you type in

dir \

or

cd \

You end up referring to the root of the current drive. dir \\ gives you

C:\Documents and Settings\mcrowley>dir \\
The filename, directory name, or volume label syntax is incorrect.

Hmm...It may be time to dust off the Linux test machine...|||\\ on some systems means referring to a network
computer.|||My first guess ...

It's a SP who makes up the filename I think.
I'll have to trace myself through the code. Funny it
hasn't occurred when we've been using the same SP
before.