When I change any SQL Settings (e.g. increasing the network packet size) I want to make a performance test before and after making this changes
Is there any easy way to do that
Thank
OlaWell... you need to decide what it is you're testing? Are you worried about
response time from an end user perspective? CPU utiilizatioin, disk
utilizatioin?
Between SQL Profiler and Performance Monitor you have the ability to capture
almost any performance metric that would be helpful. You just need to decide
what you want to know...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Olaf" <anonymous@.discussions.microsoft.com> wrote in message
news:92B5FDB4-D0DA-4D5F-8269-4130EEBB04D7@.microsoft.com...
> When I change any SQL Settings (e.g. increasing the network packet size) I
want to make a performance test before and after making this changes.
> Is there any easy way to do that?
>
> Thanks
> Olaf
>
Showing posts with label network. Show all posts
Showing posts with label network. Show all posts
Wednesday, March 28, 2012
Performance Test
When I change any SQL Settings (e.g. increasing the network packet size) I w
ant to make a performance test before and after making this changes.
Is there any easy way to do that?
Thanks
OlafWell... you need to decide what it is you're testing? Are you worried about
response time from an end user perspective? CPU utiilizatioin, disk
utilizatioin?
Between SQL Profiler and Performance Monitor you have the ability to capture
almost any performance metric that would be helpful. You just need to decide
what you want to know...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Olaf" <anonymous@.discussions.microsoft.com> wrote in message
news:92B5FDB4-D0DA-4D5F-8269-4130EEBB04D7@.microsoft.com...
want to make a performance test before and after making this changes.
ant to make a performance test before and after making this changes.
Is there any easy way to do that?
Thanks
OlafWell... you need to decide what it is you're testing? Are you worried about
response time from an end user perspective? CPU utiilizatioin, disk
utilizatioin?
Between SQL Profiler and Performance Monitor you have the ability to capture
almost any performance metric that would be helpful. You just need to decide
what you want to know...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Olaf" <anonymous@.discussions.microsoft.com> wrote in message
news:92B5FDB4-D0DA-4D5F-8269-4130EEBB04D7@.microsoft.com...
quote:
> When I change any SQL Settings (e.g. increasing the network packet size) I
want to make a performance test before and after making this changes.
quote:
> Is there any easy way to do that?
>
> Thanks
> Olaf
>
Monday, March 12, 2012
Performance outside private network
Can SQL run more than one request at the same time from the same IP address?
I've noticed that if I open two instances of my site in IE they tend to ru
n sluggish, and it seems like the second instance does not start to respond
to a request until the firs
t request is completed on my first instance. I'm trying to run both request
s simultaneously. I'm running SQL Server 2000 Developer's Edition, by the w
ay. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
I've noticed that if I open two instances of my site in IE they tend to ru
n sluggish, and it seems like the second instance does not start to respond
to a request until the firs
t request is completed on my first instance. I'm trying to run both request
s simultaneously. I'm running SQL Server 2000 Developer's Edition, by the w
ay. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Performance outside private network
Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the firs
t request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?
>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
t request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?
>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Performance outside private network
Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the first request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Wednesday, March 7, 2012
Performance of backup across network
I am hoping someone with experience can help me with this.
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
--
Scott NicholHi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
--
Scott NicholHi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
Performance of backup across network
I am hoping someone with experience can help me with this.
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
Scott Nichol
Hi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
Scott Nichol
Hi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
Performance of backup across network
I am hoping someone with experience can help me with this.
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
--
Scott NicholHi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
--
Scott NicholHi Scott
Have you looked at SQL Litespeed? It's all about optimising backup
performance.
http://www.imceda.com/LiteSpeed_Description.htm
Regards,
Greg Linwood
SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:u%23NuPnBaEHA.3596@.tk2msftngp13.phx.gbl...
> I am hoping someone with experience can help me with this.
> I have been doing differential and log backups across a network to a
device
> specified by a UNC path for some time. The backup sizes have been small
> enough that performance was no issue. Full backups, which total about 250
> GB, I have been doing to a RAID set on the local machine, then doing a
copy
> (literally the command line copy from a batch script) to the same UNC path
> as the diff and logs. The backup itself takes about 2.5 hours, and the
copy
> another 4 hours. The network is dedicated gigabit. The target machine
uses
> RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
> sustain 30 MB/s with an app that has a clue about the network. xcopy can
> sometimes hit that pace, but it and copy more typically do 17 MB/s.
> Because I want to use the disk space on the SQL Server box for something
> other than backups, I now want to do full backups directly across the
> network, using the UNC path as for diff and log. My problem is that this
is
> dog slow (no insult to dogs, most of which run much faster than I,
> intended). I am getting about 7 MB/s, which means the full backup is
about
> 10 hours! Considering that the performance from copy (17 MB/s) is hardly
> exemplary, I find SQL Server performance pretty embarassing.
> The only useful tip I found on the net was to backup to multiple devices.
> When I do this, my network throughput actually *drops* to about 6.5 MB/s
> (for 2, 3 or 4 devices). The same tip suggested that multiple devices
might
> not help if max worker threads is not bumped up. I have not had a chance
to
> restart SQL Server (this is a 24x7 public database), but we typically have
> 100-120 connections, so I am not sure that bumping this up will do
anything,
> anyway.
> Does anyone have experience with network backups and is getting better
> throughput? I have considered forcing opportunistic locking in the
> redirector, but this seems like blind hope.
> Do I need to use something with a clue about networks, like the Veritas
SQL
> Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2
years
> ago when we could not get it to work.
> Help with backing up directly over the network would be much appreciated.
> Comments from SQL Agent users are also welcome.
> TIA
> --
> Scott Nichol
>
Subscribe to:
Posts (Atom)