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
>>
>>
>
Showing posts with label updates. Show all posts
Showing posts with label updates. Show all posts
Friday, March 30, 2012
Monday, February 20, 2012
Questions on triggers and when they fire...
I have a stored proc that does something like 'Update table1 set a =
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris
"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.
|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:
>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris
"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.
|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:
>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP
Subscribe to:
Posts (Atom)