Data files location.

Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000).
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs are
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge Shakhov
Hi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so the
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:

> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performance.
> Now we're planning to use cluster server with external storage (HP MSA 1000).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution across
> physical discs?
> Thanks.
> Serge Shakhov
|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:

Data files location.

Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000)
.
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs ar
e
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge ShakhovHi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10
.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so th
e
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:

> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performanc
e.
> Now we're planning to use cluster server with external storage (HP MSA 100
0).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs
are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution acros
s
> physical discs?
> Thanks.
> Serge Shakhov|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.

> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:

> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:

> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> > The production database of one of our clients is set up with both the data
> > and log files on the same disk. We suggested that they should separate
> > them
> > out to eliminate disk contention and to eliminate I/O problems. The DBA at
> > the client site came back saying that he does not believe this is
> > necessary.
> > Are we wrong in suggesting that the data and log files should be on
> > separate
> > disks?
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
> > The client database is about 20 GB.
> > Thanks in advance.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
>> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
>> > The production database of one of our clients is set up with both the
>> > data
>> > and log files on the same disk. We suggested that they should separate
>> > them
>> > out to eliminate disk contention and to eliminate I/O problems. The DBA
>> > at
>> > the client site came back saying that he does not believe this is
>> > necessary.
>> > Are we wrong in suggesting that the data and log files should be on
>> > separate
>> > disks?
>> No, you're not.
>> However, whether it actually helps their performance is a good question.
>> If it's small enough and low volume enough, it won't help there.
>> If say the machine can only support 2 disks in a RAID 1 config, this
>> solution is "ok" given the machine.
>> If the machine can support more RAIDs (say 4 disks) then they're probably
>> better off separating them just because then if two disks fail, they're
>> less
>> likely to actually suffer data loss.
>>
>> > The client database is about 20 GB.
>> > Thanks in advance.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> > Thanks for the reply. As I mentioned the database is about 20GB and there is
> > plenty of activity during office hours and during other scheduled task runs.
> > Do you still believe separating the data and log files will improve some of
> > the bottlenecks.
> > Thanks
> >
> >
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.

> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:

> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're le
ss
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there
is
> plenty of activity during office hours and during other scheduled task run
s.
> Do you still believe separating the data and log files will improve some o
f
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:

> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>