Showing posts with label log. Show all posts
Showing posts with label log. Show all posts

Friday, March 30, 2012

RAISERROR dilema

Hello,

I am tring to use the RAISERROR command to log an error when it happens and pass a parameter into it. This is what I am looking for in a nutshell. I know this syntax is incorrect.

RAISEERROR('The following process has encountered an error' +space(1) + @.var, 16,1, @.with_log='TRUE')

Any help would be appreciated...

Thanks,

Dave

To make this work, you need to create your message before calling RAISERROR.

DECLARE @.ErrMessage varchar(250)

SET @.ErrMessage = ( 'The following process has encountered an error' + ' ' + @.var )

And then call RAISERROR using the @.ErrMessage, e.g.,

RAISEERROR( @.ErrMessage, 16, 1, @.with_log='TRUE' )

Of course, if @.Var is numeric,it will have to be cast as varchar to concatenate.

sql

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advance
Raid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>
|||I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:

>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>
|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>
sql

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advanceRaid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>|||I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:

>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>
>

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advanceRaid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>|||This is a multi-part message in MIME format.
--060704050306020201010502
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:
>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>
--060704050306020201010502
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've always found this website to be a good RAID level overview
(even though it's a vendor website):<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a><br>">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a><br>
<br>
It's not very detailed, just brief pros & cons and how the RAID
level is constructed, but it's good info nonetheless.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
TJT wrote:
<blockquote cite="miduDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advance
</pre>
</blockquote>
</body>
</html>
--060704050306020201010502--|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>>I was hoping that someone could point me to some good documentation on
>>selecting an optimum RAID configuration. While performing heavy updates
>>during upgrades - I've noticed dramatic performance difference between
>>RAID
>>1 and RAID 5 for the transaction log.
>>It seems that it is less important for the data files themselves (RAID 5
>>doesn't seem to hurt that much).
>>Does this make sense where the RAID Configuration for the T-Log is more
>>important than the Data?
>>If someone could shed some light on this or point me to some good
>>documantation - I would greatly appreciate it.
>>Thanks in advance
>>
>>
>

RAID System

What is the best RAID system to use in a SAN environment.
Is it ok to use RAID 5 on everything like data, log, etc
when using a SAN shared storage.In certain cases it may make sense to use other
configuration or use different raid levels for tempdb
(raid 0), log (raid 1+0), databases (raid 5) etc. Here you
have to be sure that you will be getting excellent
cooperation from the network/hardare support departments,
or if you are responsible for SAN and sql server.
Generally I would say it is ok to use raid 5 for
everything, because thats what I decided to use when we
configured SAN for sql server.
>--Original Message--
>What is the best RAID system to use in a SAN environment.
>Is it ok to use RAID 5 on everything like data, log, etc
>when using a SAN shared storage.
>.
>|||Every system has different requirements, but one is generally true - using
RAID 5 for logs is usually a bad idea because the parity calculation
requires additional overhead to read bits (disk i/o), parity calculation
(cpu, hba etc) & then the parity writes (more disk i/o).
Generally speaking, logs are far better off on a mirror, perhaps striped as
well, but not raid 5.
Regards,
Greg Linwood
SQL Server MVP
"Aboki" <waco361@.hotmail.com> wrote in message
news:13ac01c381e7$58753290$a401280a@.phx.gbl...
> What is the best RAID system to use in a SAN environment.
> Is it ok to use RAID 5 on everything like data, log, etc
> when using a SAN shared storage.

Wednesday, March 28, 2012

RAID and Transaction Logs Involved in Replication

I began documenting recommended RAID configurations for our environment. Typically I encourage using RAID 1 for T-Logs since log writes are sequential. But then I started to wonder about how replication impacts T-Logs. According to BOL:

"The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database."

This being the case, doesn't that indicate the disk spindles will be repositioned when the Log Reader Agent reads the logs and copies the data to the Distribution database? If so, is RAID 1 still the best option?

Please let me know your thoughts.

Thanks, DaveThe impact of whatever RAID you use is very little as far as the efficency of transaction log, as long as you keep it simple and small enough. In a replication environment, transaction logs will not get dumped until the Log Reader loads the marked transactions onto the Distribution database. When you have large numbers of IOs, like over 35 millions commands within 5 hours, in our company. The Log Reader works extremely well in RAID 5. But the bottleneck is in Distribution database because all transactions get in there in a big hurry and cannot be distributed fast enough.

Wish this helps! Good luck.|||Someone in another forum pointed out that typically the Log Reader runs continuously. As a result the impact on the disk spindles should be small since only a couple of data pages are involved in the I/O. The only potential issue I see is when "Sync with Backup" is turned on. When on, the Log Reader Agent only sends T-Log data to the distribution database after a T-Log backup has been performed. In our environment this occurs every two hours. Depending upon the amount of transactions being processed, this has the potential to impact performance during the time the Log Reader is copying data from the T-Log and inserting it into the Distribution database. I suspect in most environments having the Log on a RAID 1, where the database is involved in replication, would not noticeably impact performance.

Thanks, Dave

Raid 10 with lots of disks. Where to place datafiles

I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
Paul
On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?

>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.

> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.
|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:

>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
sql

Raid 10 with lots of disks. Where to place datafiles

I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>Why so many disks? Is the database humongous?
>
>>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>>indexes and data or even by tables that are hit heavy (not easy to get right
>>when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>>on each raid 10 set.
>>I know this is application specific
>>
>Right.
>
>> but when you have a complex system you
>>could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
>><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>>Why so many disks? Is the database humongous?
>>
>>Create 2 or more raid 10 sets and split the data in a controlled manner.
>>Eg indexes and data or even by tables that are hit heavy (not easy to get
>>right when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a
>>file on each raid 10 set.
>>I know this is application specific
>>Right.
>>
>> but when you have a complex system
>> you could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>>One big, fat RAID 10 will probably get you 80% of peak performance
>>with 20% of the bother. You can use SQLServer filegroups to optimize
>>wihin, if you like. Two more more smaller RAIDS might help, depending
>>on the app, depending on whether you NEED more help!
>>J.
>>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.

Raid 10 with lots of disks. Where to place datafiles

I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?

>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get righ
t
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.

> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:

>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.

Monday, March 26, 2012

RAID 1 vs RAID 10

Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)? We
are wondering what to use for the log files.RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
should be faster than local disks, but that depends on what other systems
are also using the SAN.
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)?
> We are wondering what to use for the log files.
>|||> The SAN should be faster than local disks, but that depends on
> what other systems are also using the SAN.
SAN is not necessarily faster than local disks, and it doesn't depend on
what other systems are using the SAN. Whether you get better performance with
SAN or local disks depends on how they are configured.
Conceivably, you can >almost< always configure local disks to outperform
disks presented from SAN.
Linchi
"Michael D'Angelo" wrote:
> RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
> should be faster than local disks, but that depends on what other systems
> are also using the SAN.
> "Shiva" <arbitsquare@.hotmail.com> wrote in message
> news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
> > Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)?
> > We are wondering what to use for the log files.
> >
>
>|||This is a multi-part message in MIME format.
--000104000206050106050406
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Additionally, RAID 10 is typically only faster than RAID 1 in terms of
read performance. For writes, which transactions logs consist of nearly
100%, both are pretty close in performance. Also, since transaction
logs are sequential and the heads usually stay over the same spot on the
platters, you don't get the random I/O benefit that RAID 10 normally
gives with the striping. So a RAID 10 volume would, under most
circumstances, be no better than a RAID 1 volume for logs.
Kalen Delaney wrote a good section on RAID volumes in Inside SQL Server
2000 - worth a read. Also you can get some good ideas about disk
configuration from reading the data & log layout sections in the TCP-C
benchmark full disclosure reports on www.tcp.org (section 4.2
"Distribution of Tables and Logs" in the IBM full disclosure reports or
section 4.1 "Database Layout" in the HP full disclosure reports).
Microsoft used to use RAID1 for logs from memory but I noticed the most
recent one they had (SQL 2005 1.2M benchmark) used RAID1+0 physical and
then NT striping across those logical RAID volumes. The DB2 3.2M
benchmark and the Oracle's 1.6M benchmark both used RAID5+0 as far as I
can tell (DB2 - stripes across 10 separate RAID5 volumes, each volume
consisting of 14 spindles; Oracle - stripes across 8 RAID5 volumes, each
volume consisting of 12 spindles). So all the top benchmarks tend to
use striping of some sorts, which is interesting since for write
performance it shouldn't be any faster really than straight mirroring.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Linchi Shea wrote:
>>The SAN should be faster than local disks, but that depends on
>>what other systems are also using the SAN.
>>
>SAN is not necessarily faster than local disks, and it doesn't depend on
>what other systems are using the SAN. Whether you get better performance with
>SAN or local disks depends on how they are configured.
>Conceivably, you can >almost< always configure local disks to outperform
>disks presented from SAN.
>Linchi
>"Michael D'Angelo" wrote:
>
>>RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
>>should be faster than local disks, but that depends on what other systems
>>are also using the SAN.
>>"Shiva" <arbitsquare@.hotmail.com> wrote in message
>>news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
>>
>>Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)?
>>We are wondering what to use for the log files.
>>
>>
>>
--000104000206050106050406
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Additionally, RAID 10 is typically only faster than RAID 1 in terms
of read performance. For writes, which transactions logs consist of
nearly 100%, both are pretty close in performance. Also, since
transaction logs are sequential and the heads usually stay over the
same spot on the platters, you don't get the random I/O benefit that
RAID 10 normally gives with the striping. So a RAID 10 volume would,
under most circumstances, be no better than a RAID 1 volume for logs.<br>
<br>
Kalen Delaney wrote a good section on RAID volumes in Inside SQL Server
2000 - worth a read. Also you can get some good ideas about disk
configuration from reading the data & log layout sections in the
TCP-C benchmark full disclosure reports on <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.tcp.org</a>">http://www.tcp.org">www.tcp.org</a> (section 4.2
"Distribution of Tables and Logs" in the IBM full disclosure reports or
section 4.1 "Database Layout" in the HP full disclosure reports).Â
Microsoft used to use RAID1 for logs from memory but I noticed the most
recent one they had (SQL 2005 1.2M benchmark) used RAID1+0 physical and
then NT striping across those logical RAID volumes. The DB2 3.2M
benchmark and the </tt><tt>Oracle's 1.6M benchmark both </tt><tt>used
RAID5+0 as far as I can tell (DB2 - stripes across 10 separate RAID5
volumes, each volume consisting of 14 spindles; Oracle - stripes across
8 RAID5 volumes, each volume consisting of 12 spindles). So all the
top benchmarks tend to use striping of some sorts, which is interesting
since for write performance it shouldn't be any faster really than
straight mirroring.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Linchi Shea wrote:
<blockquote cite="mid3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com"
type="cite">
<blockquote type="cite">
<pre wrap="">The SAN should be faster than local disks, but that depends on
what other systems are also using the SAN.
</pre>
</blockquote>
<pre wrap=""><!-->
SAN is not necessarily faster than local disks, and it doesn't depend on
what other systems are using the SAN. Whether you get better performance with
SAN or local disks depends on how they are configured.
Conceivably, you can >almost< always configure local disks to outperform
disks presented from SAN.
Linchi
"Michael D'Angelo" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
should be faster than local disks, but that depends on what other systems
are also using the SAN.
"Shiva" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:arbitsquare@.hotmail.com"><arbitsquare@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl">news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)?
We are wondering what to use for the log files.
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--000104000206050106050406--|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com...
>> The SAN should be faster than local disks, but that depends on
>> what other systems are also using the SAN.
> SAN is not necessarily faster than local disks, and it doesn't depend on
> what other systems are using the SAN. Whether you get better performance
> with
> SAN or local disks depends on how they are configured.
> Conceivably, you can >almost< always configure local disks to outperform
> disks presented from SAN.
> Linchi
I'd be interested to see when that would be the case. If both cases have
the same disk configuration, but your local raid controller has, say, 128mb
of cache, but the SAN has a 2gb cache, wouldn't the SAN conceivable
outperform local disk?
Of course I can see if local disks or the SAN can provide more physical
disks than the other, then that would make a difference.
> "Michael D'Angelo" wrote:
>> RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
>> should be faster than local disks, but that depends on what other systems
>> are also using the SAN.
>> "Shiva" <arbitsquare@.hotmail.com> wrote in message
>> news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
>> > Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a
>> > SAN)?
>> > We are wondering what to use for the log files.
>> >
>>|||This is a multi-part message in MIME format.
--050308030100090101020702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The most likely factor that would influence SAN disk performance would
be load applied by other hosts (mostly on the disks themselves, but also
the SAN cache & controllers). With the local disks they're always 100%
dedicated to the SQL host, but often (not always but often) the RAID
group on the SAN that you get your LUNs for SQL from will also be used
by other systems like email, other SQL hosts, file servers, VMs, etc.
Also, depending on how the cache on the SAN is configured it may not be
very useful for what SQL will use the LUN for (so that 2GB cache may be
useless). For example, if the cache is configured mostly for read, then
that's not going to help transaction log LUNs, which are almost
exclusively write only.
Most of the time I would think SAN LUNs ought to give better
performance, all things being equal, but I can see cases (and have
experienced them myself - sharing with Exchange can be a dog) where
local DAS disks can yield better performance.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Michael D'Angelo wrote:
>"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com...
>
>>The SAN should be faster than local disks, but that depends on
>>what other systems are also using the SAN.
>>
>>SAN is not necessarily faster than local disks, and it doesn't depend on
>>what other systems are using the SAN. Whether you get better performance
>>with
>>SAN or local disks depends on how they are configured.
>>Conceivably, you can >almost< always configure local disks to outperform
>>disks presented from SAN.
>>Linchi
>>
>I'd be interested to see when that would be the case. If both cases have
>the same disk configuration, but your local raid controller has, say, 128mb
>of cache, but the SAN has a 2gb cache, wouldn't the SAN conceivable
>outperform local disk?
>Of course I can see if local disks or the SAN can provide more physical
>disks than the other, then that would make a difference.
>
>>"Michael D'Angelo" wrote:
>>
>>RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
>>should be faster than local disks, but that depends on what other systems
>>are also using the SAN.
>>"Shiva" <arbitsquare@.hotmail.com> wrote in message
>>news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
>>
>>Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a
>>SAN)?
>>We are wondering what to use for the log files.
>>
>>
>>
>
>
--050308030100090101020702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The most likely factor that would influence SAN disk performance
would be load applied by other hosts (mostly on the disks themselves,
but also the SAN cache & controllers). With the local disks
they're always 100% dedicated to the SQL host, but often (not always
but often) the RAID group on the SAN that you get your LUNs for SQL
from will also be used by other systems like email, other SQL hosts,
file servers, VMs, etc.<br>
<br>
Also, depending on how the cache on the SAN is configured it may not be
very useful for what SQL will use the LUN for (so that 2GB cache may be
useless). For example, if the cache is configured mostly for read,
then that's not going to help transaction log LUNs, which are almost
exclusively write only.<br>
<br>
Most of the time I would think SAN LUNs ought to give better
performance, all things being equal, but I can see cases (and have
experienced them myself - sharing with Exchange can be a dog) where
local DAS disks can yield better performance.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Michael D'Angelo wrote:
<blockquote cite="miduHUeVXHdGHA.1208@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">"Linchi Shea" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:LinchiShea@.discussions.microsoft.com"><LinchiShea@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com">news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com</a>...
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">The SAN should be faster than local disks, but that depends on
what other systems are also using the SAN.
</pre>
</blockquote>
<pre wrap="">SAN is not necessarily faster than local disks, and it doesn't depend on
what other systems are using the SAN. Whether you get better performance
with
SAN or local disks depends on how they are configured.
Conceivably, you can >almost< always configure local disks to outperform
disks presented from SAN.
Linchi
</pre>
</blockquote>
<pre wrap=""><!-->
I'd be interested to see when that would be the case. If both cases have
the same disk configuration, but your local raid controller has, say, 128mb
of cache, but the SAN has a 2gb cache, wouldn't the SAN conceivable
outperform local disk?
Of course I can see if local disks or the SAN can provide more physical
disks than the other, then that would make a difference.
</pre>
<blockquote type="cite">
<pre wrap="">"Michael D'Angelo" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
should be faster than local disks, but that depends on what other systems
are also using the SAN.
"Shiva" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:arbitsquare@.hotmail.com"><arbitsquare@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl">news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a
SAN)?
We are wondering what to use for the log files.
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050308030100090101020702--

RAID 1 vs RAID 10

Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)? We
are wondering what to use for the log files.RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
should be faster than local disks, but that depends on what other systems
are also using the SAN.
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Which one is faster RAID 1 (on local disk drives) or RAID 10 (on a SAN)?
> We are wondering what to use for the log files.
>|||> The SAN should be faster than local disks, but that depends on
> what other systems are also using the SAN.
SAN is not necessarily faster than local disks, and it doesn't depend on
what other systems are using the SAN. Whether you get better performance wit
h
SAN or local disks depends on how they are configured.
Conceivably, you can >almost< always configure local disks to outperform
disks presented from SAN.
Linchi
"Michael D'Angelo" wrote:

> RAID10 will be faster than RAID1 whether it's on the SAN or not. The SAN
> should be faster than local disks, but that depends on what other systems
> are also using the SAN.
> "Shiva" <arbitsquare@.hotmail.com> wrote in message
> news:Oj9AKs5cGHA.1208@.TK2MSFTNGP04.phx.gbl...
>
>|||Additionally, RAID 10 is typically only faster than RAID 1 in terms of
read performance. For writes, which transactions logs consist of nearly
100%, both are pretty close in performance. Also, since transaction
logs are sequential and the heads usually stay over the same spot on the
platters, you don't get the random I/O benefit that RAID 10 normally
gives with the striping. So a RAID 10 volume would, under most
circumstances, be no better than a RAID 1 volume for logs.
Kalen Delaney wrote a good section on RAID volumes in Inside SQL Server
2000 - worth a read. Also you can get some good ideas about disk
configuration from reading the data & log layout sections in the TCP-C
benchmark full disclosure reports on www.tcp.org (section 4.2
"Distribution of Tables and Logs" in the IBM full disclosure reports or
section 4.1 "Database Layout" in the HP full disclosure reports).
Microsoft used to use RAID1 for logs from memory but I noticed the most
recent one they had (SQL 2005 1.2M benchmark) used RAID1+0 physical and
then NT striping across those logical RAID volumes. The DB2 3.2M
benchmark and the Oracle's 1.6M benchmark both used RAID5+0 as far as I
can tell (DB2 - stripes across 10 separate RAID5 volumes, each volume
consisting of 14 spindles; Oracle - stripes across 8 RAID5 volumes, each
volume consisting of 12 spindles). So all the top benchmarks tend to
use striping of some sorts, which is interesting since for write
performance it shouldn't be any faster really than straight mirroring.
*mike hodgson*
http://sqlnerd.blogspot.com
Linchi Shea wrote:
[vbcol=seagreen]
>SAN is not necessarily faster than local disks, and it doesn't depend on
>what other systems are using the SAN. Whether you get better performance wi
th
>SAN or local disks depends on how they are configured.
>Conceivably, you can >almost< always configure local disks to outperform
>disks presented from SAN.
>Linchi
>"Michael D'Angelo" wrote:
>
>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com...
> SAN is not necessarily faster than local disks, and it doesn't depend on
> what other systems are using the SAN. Whether you get better performance
> with
> SAN or local disks depends on how they are configured.
> Conceivably, you can >almost< always configure local disks to outperform
> disks presented from SAN.
> Linchi
I'd be interested to see when that would be the case. If both cases have
the same disk configuration, but your local raid controller has, say, 128mb
of cache, but the SAN has a 2gb cache, wouldn't the SAN conceivable
outperform local disk?
Of course I can see if local disks or the SAN can provide more physical
disks than the other, then that would make a difference.
[vbcol=seagreen]
> "Michael D'Angelo" wrote:
>|||The most likely factor that would influence SAN disk performance would
be load applied by other hosts (mostly on the disks themselves, but also
the SAN cache & controllers). With the local disks they're always 100%
dedicated to the SQL host, but often (not always but often) the RAID
group on the SAN that you get your LUNs for SQL from will also be used
by other systems like email, other SQL hosts, file servers, VMs, etc.
Also, depending on how the cache on the SAN is configured it may not be
very useful for what SQL will use the LUN for (so that 2GB cache may be
useless). For example, if the cache is configured mostly for read, then
that's not going to help transaction log LUNs, which are almost
exclusively write only.
Most of the time I would think SAN LUNs ought to give better
performance, all things being equal, but I can see cases (and have
experienced them myself - sharing with Exchange can be a dog) where
local DAS disks can yield better performance.
*mike hodgson*
http://sqlnerd.blogspot.com
Michael D'Angelo wrote:

>"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
>news:3EAE716E-B80B-41E8-86BD-53C672D67935@.microsoft.com...
>
>I'd be interested to see when that would be the case. If both cases have
>the same disk configuration, but your local raid controller has, say, 128mb
>of cache, but the SAN has a 2gb cache, wouldn't the SAN conceivable
>outperform local disk?
>Of course I can see if local disks or the SAN can provide more physical
>disks than the other, then that would make a difference.
>
>
>
>

RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!
yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!
|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:

> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you put
> the log on the write cache controller...but what are you going to put ont he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>
>
|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...[vbcol=seagreen]
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
sql

RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:
> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you put
> the log on the write cache controller...but what are you going to put ont he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> > Currently I have a database server that has the log files on a raid array
> > supported by a controller that does NOT support write caching, only 100%
> > read. I have another raid controller on the server that does support write
> > caching.
> >
> > Would there be any performace benefits from having the raid 1 array with
> > the
> > log files supported by the raid controller with write caching over one
> > that
> > does not?
> >
> > Thanks!!!
>
>|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
>> yes, as unless there is a rollback or some recovery operation the log is
>> mostly a write-to file therefore you could reduce some queue time if you
>> put
>> the log on the write cache controller...but what are you going to put ont
>> he
>> read caching controller ?
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>> > Currently I have a database server that has the log files on a raid
>> > array
>> > supported by a controller that does NOT support write caching, only
>> > 100%
>> > read. I have another raid controller on the server that does support
>> > write
>> > caching.
>> >
>> > Would there be any performace benefits from having the raid 1 array
>> > with
>> > the
>> > log files supported by the raid controller with write caching over one
>> > that
>> > does not?
>> >
>> > Thanks!!!
>>

RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:

> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you p
ut
> the log on the write cache controller...but what are you going to put ont
he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>
>|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...[vbcol=seagreen]
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
>

Wednesday, March 21, 2012

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
--
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292
John
"DWalker" wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> > More than likely you have a computed column or an indexed view. Both
> > require certain settings that the MP can't handle. Although I believe
> > this was fixed in SP4. Otherwise you need to do the reindex in your
> > own custom job with the proper settings set.
> >
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>> Hi David
>> Check out
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
>> particular table probably failed because it is the first that has the
>> index on a computed column.
>> If not you may want to look at SQL profiler to see exactly what
>> statements are being sent to the database by the maintenance plan.
>> John
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> > Hi David
> >
> > Check out
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> > particular table probably failed because it is the first that has the
> > index on a computed column.
> >
> > If not you may want to look at SQL profiler to see exactly what
> > statements are being sent to the database by the maintenance plan.
> >
> > John
> >
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D4BDB694-0A2E-408D-AA7A-562DBEC42515@.microsoft.com:
> Hi David
> You may want to use the send feedback option at the bottom of the
> article.
> John
>
I'll do that.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OsfT#fYMGHA.3460@.TK2MSFTNGP15.phx.gbl:
>> That article is sure confusing; it says "These statements require
>> that the QUOTED_IDENTIFIER SET option is set to ON."
>> It *IS* set to On in my database.
> The database setting is essentially useless since it will be
> overridden by session setting. And most API's will set this setting,
> whether the developer is aware of it or not.
>
Thanks, Tibor.
Davidsql

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/defaul...b;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/defaul...kb;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by s
ession setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...[v
bcol=seagreen]
> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker[/vbcol]|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>