Optimize RAID configuration

I am setting up a server that will be a dedicated database server. I don't have much flexibility in choosing a server, and the servers we use are limited to six disk drives. There will be only one user database on the server, aside from the system databases. Everything I have read indicates it is better to place the transaction log file on a separate disk array from the data files. I could put the transaction log file on a mirrored disk array (RAID 1) and then put the data file on a RAID 10 array of four disks. But where would I put the OS and SQL binaries? The application in question is fairly write-intensive, and does not perform well in a RAID 5 array. In the past, we placed everything (OS, data, logs, etc.) on the same RAID 5 array. It is apparent that this configuration is not going to work, or at least it won't scale very well. My questions are as follows:

1) How much write activity is generated by the transaction log? Will I see a tangible performance benefit by separating the log file onto its own physical array?

2) What would be the optimal RAID configuration for the above scenario, while limited to six drives?

3) What would be the optimal RAID configuration if I had more that one user database on the same server, with the six drive limit?

Any help would be very much appreciated.As a GENERAL rule, you want to seperate your log files, databases and indexs from each other. In your case I doubt if you will have much oppertunity to optimize your hardware.

IMnsHO, I would put the os, binaries and logs on the RAID 1 drives and db et all on the RAID 10 drives. I suspect you have a single channel controler so watch disk I/O as I would bet you will have a bottleneck here. If you have a dual channel controler put the RAID 1 on one channel and the RAID 10 on the other.

Your question...

1) Every write to a table will generate a write to the transaction log even in Simple mode! Putting your logs on a RAID one drive will increase performance on a write intensive db.

2) It is more a question of RAID configuration and controler configuration. Unless you have a dual channel controler you aren't going to see much of a performance increase. Switching from RAID 5 to RAID 1 & 10 will help. Make sure the data cache on the controler is optimized for more writes than reads.

3) Get a three channel controler, channel one for the RAID 1 drives, channel two and three for the RAID 10 drives, mirror across the two channels so the strip stays on one channel.

optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimDo you have the right service pack to support that?
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Does anyone at all have any ideas?
I would be very grateful for any relevant feedback! :-)
Thanks,
Jim
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Why would that not be relevant? This feature was not a part of SQL Server
until Service Pack 4. Since you did not post what version you are using it
is quite feasible that it is not working because that service pack does not
support it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
> Does anyone at all have any ideas?
> I would be very grateful for any relevant feedback! :-)
> Thanks,
> Jim
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> aintenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>|||Hi Andrew,
It is SQL Server SP4.
Regards,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
> Why would that not be relevant? This feature was not a part of SQL Server
> until Service Pack 4. Since you did not post what version you are using
> it is quite feasible that it is not working because that service pack does
> not support it.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> intenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>|||Jim,
SP4 and that option was supposed to fix those issues and I haven't heard of
anyone else using them and it not working as expected. Unless someone does
pipe in with something else you really have two options. One is to call MS
PSS and report this. If it is a bug they will refund your initial trouble
ticket charge. If not it will cost about $225. The other and recommended
option is to forget about the Maintenance plans either altogether or at
least for this part. It looks like you are having issues with the updating
of the stats. First I would ask why you are using the MP for that? Do you
also reindex each table either with DBCC DBREINDEX or with the maintenance
plans? If so then that will update the stats for you and this step is
unnecessary. Otherwise you can create your own job and simply run
sp_updatestats and be done with it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:uScFlH%23XGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> It is SQL Server SP4.
> Regards,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
>> Why would that not be relevant? This feature was not a part of SQL
>> Server until Service Pack 4. Since you did not post what version you are
>> using it is quite feasible that it is not working because that service
>> pack does not support it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> ntenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>>
>

optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimDo you have the right service pack to support that?
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Does anyone at all have any ideas?
I would be very grateful for any relevant feedback! :-)
Thanks,
Jim
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Why would that not be relevant? This feature was not a part of SQL Server
until Service Pack 4. Since you did not post what version you are using it
is quite feasible that it is not working because that service pack does not
support it.
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
> Does anyone at all have any ideas?
> I would be very grateful for any relevant feedback! :-)
> Thanks,
> Jim
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>|||Hi Andrew,
It is SQL Server SP4.
Regards,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
> Why would that not be relevant? This feature was not a part of SQL Server
> until Service Pack 4. Since you did not post what version you are using
> it is quite feasible that it is not working because that service pack does
> not support it.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>|||Jim,
SP4 and that option was supposed to fix those issues and I haven't heard of
anyone else using them and it not working as expected. Unless someone does
pipe in with something else you really have two options. One is to call MS
PSS and report this. If it is a bug they will refund your initial trouble
ticket charge. If not it will cost about $225. The other and recommended
option is to forget about the Maintenance plans either altogether or at
least for this part. It looks like you are having issues with the updating
of the stats. First I would ask why you are using the MP for that? Do you
also reindex each table either with DBCC DBREINDEX or with the maintenance
plans? If so then that will update the stats for you and this step is
unnecessary. Otherwise you can create your own job and simply run
sp_updatestats and be done with it.
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:uScFlH%23XGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> It is SQL Server SP4.
> Regards,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
>sql

Optimal Physical Layer config SQL Server on IBM xSeries 345 server

Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
most of the SQL Servers databases on it (~40). I am trying to figure out the
best configuration for the disk subsystem for both reasonable performance and
disk cost savings and reduced down time. The server may have upto 6 X 73.4
GB SCSI disks. Can anyone suggest any ideas?
DISK 1 and
DISK 2 as RAID 1 â' System & Tlog
DISK 3 and
DISK 4 as RAID 1 - DATA
DISK 5 and
DISK 6 as RAID 1 â' DATA
---
DISK 1 and
DISK 2 as RAID 1 System & Tempdb
DISK 3 and
DISK 4 as RAID 1 â' Tlogs
DISK 5 and
DISK 6 as AID 1 â' DATA
---
Thanks."Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:493C572F-C196-4301-B2D7-4AD665B2A48C@.microsoft.com...
> Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
> most of the SQL Servers databases on it (~40). I am trying to figure out
> the
> best configuration for the disk subsystem for both reasonable performance
> and
> disk cost savings and reduced down time. The server may have upto 6 X
> 73.4
> GB SCSI disks. Can anyone suggest any ideas?
> DISK 1 and
> DISK 2 as RAID 1 - System & Tlog
> DISK 3 and
> DISK 4 as RAID 1 - DATA
> DISK 5 and
> DISK 6 as RAID 1 - DATA
> ---
> DISK 1 and
> DISK 2 as RAID 1 System & Tempdb
> DISK 3 and
> DISK 4 as RAID 1 - Tlogs
> DISK 5 and
> DISK 6 as AID 1 - DATA
> ---
>
Physical optimization like this mostly involves aranging the spindles to
maximize the number of spindes used and minimize the contention for
spindles. However with 40 databases and 40 different workloads on the
server you will probably not be able to optimize the physical layout much.
BTW, I wouldn't consolodate onto the x345 if I could help it because it's a
32-bit machine. The newer x346 can run 64bit Windows and SQL Server 2005.
This will let you use much more memory, and minimize how much your workload
utilizes the disks.
David|||I second that emotion.
I'd also like to add that many writes wind up being cached and first written
into the transaction log... the point being that you could probably do all
your data on one RAID-5 array and have more actual space as a result or keep
one remaining disk as a spare.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4b2YHzJGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
> Physical optimization like this mostly involves aranging the spindles to
> maximize the number of spindes used and minimize the contention for
> spindles. However with 40 databases and 40 different workloads on the
> server you will probably not be able to optimize the physical layout much.
>

optimal location for database files on SAN?

Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>

optimal location for database files on SAN?

Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>

optimal location for database files on SAN?

Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>

Opteron vs Xeon

I've recently been attempting to put into production some Itanium based
servers. I'm running (amongst other things) Remedy, which means highly
serialised transactions (thus limiting the effect of the Itanium) and has
lead me to discover that the low clock speed on the Itanium is causing the
application to run slower (the biggest test of this was a basic bulk insert
into a table with no indexes that would run 50% slower on a 4x1.6GHZ
Itanium vs a 2x2.4GHZ Xeon).
I'm now looking into going to back to a 32bit system, folks are touting the
benefits of the Opteron processor as opposed to the Xeon, stating that the
performance difference is pretty large.
My question is, does the slower clock speed on the Opteron translate into
the same problems that I was experiencing on the Itanium, or am I actually
going to find better i/o performance through the AMD processor?
Thanks
NicOn Thu, 01 Sep 2005 04:20:11 -0700, Nicholas Cain
<nicholas.cain@.nospam.t-mobile.com> wrote:
>I've recently been attempting to put into production some Itanium based
>servers. I'm running (amongst other things) Remedy, which means highly
>serialised transactions (thus limiting the effect of the Itanium) and has
>lead me to discover that the low clock speed on the Itanium is causing the
>application to run slower (the biggest test of this was a basic bulk insert
>into a table with no indexes that would run 50% slower on a 4x1.6GHZ
>Itanium vs a 2x2.4GHZ Xeon).
>I'm now looking into going to back to a 32bit system, folks are touting the
>benefits of the Opteron processor as opposed to the Xeon, stating that the
>performance difference is pretty large.
>My question is, does the slower clock speed on the Opteron translate into
>the same problems that I was experiencing on the Itanium, or am I actually
>going to find better i/o performance through the AMD processor?
Seems unlikely that CPU speed is really the limiting factor on a bulk
load.
J.|||JXStern <JXSternChangeX2R@.gte.net> wrote in
news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:
> Seems unlikely that CPU speed is really the limiting factor on a bulk
> load.
> J.
>
I've had the gurus at HP look and tell me that this is the limiting factor
(after a lot of consideration and followup with MS).
I didn't believe it myself, however all indications point to that problem.|||I've also seen high CPU utilization with bulk inserts, at least the
fully-logged variety.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45AE8629B4nicholascainnospamtm@.207.46.248.16...
> JXStern <JXSternChangeX2R@.gte.net> wrote in
> news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:
>
>> Seems unlikely that CPU speed is really the limiting factor on a bulk
>> load.
>> J.
>>
> I've had the gurus at HP look and tell me that this is the limiting factor
> (after a lot of consideration and followup with MS).
> I didn't believe it myself, however all indications point to that problem.|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
> I've also seen high CPU utilization with bulk inserts, at least the
> fully-logged variety.
>
The cpu utilisation was extremely high on the Itanium, the majority of that
was kernel usage.
Changing the max degree of parallelism made no difference, nor did setting
offsets on the disk, nor sp4, adding numa options, setting affinity masks
or anything.
The bulk insert itself was a single 1.5GB file into a table with no
indexes. The db itself was in simple recovery mode, db and logs on seperate
luns on a Hitachi XP1024 SAN with a 40GB cache.
Performance speeds for the bulk insert were idnetical on both a Dell and HP
Itanium based system with the same specs.|||not being an expert in SQL optimizations, i`d recomend Opterons
in task w/ no parallelism (like games) opterons rule, and in smp system
unlike xeons each opteron has its own memory controller
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||i believe there were xeons vs opterons tests (w/ DB2 and MySQL) on
anandtech.com
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||hehe - me again
and u can get double core opterons - and have 2 cpu while paying licence for
1,
and there r IMHO no double core Xeons
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||I was acutally looking at the dual core.
I guess my best course of action would be to throw a Xeon and a Opteron in
a head to head and see what comes out as the leader.
"Coldman" <nomorespam@.mail.com> wrote in
news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
> hehe - me again
> and u can get double core opterons - and have 2 cpu while paying
> licence for 1,
> and there r IMHO no double core Xeons
>|||good idea :)
y dont u post the results here after the test
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>|||If you are going to do that you should test a dual core Pentium against the
dual core Opteron. I have several clients with single core Opterons and
they are very happy with them but I think Dual Core processors will rule the
earth very soon<g>.
--
Andrew J. Kelly SQL MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>|||but there r no dual core Xeons yet i think, and Pentium 4 lacks server class
motherboards(w/ a lot of 64bit slots and dual power connectors)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9qbrgxrFHA.2272@.TK2MSFTNGP11.phx.gbl...
> If you are going to do that you should test a dual core Pentium against
> the dual core Opteron. I have several clients with single core Opterons
> and they are very happy with them but I think Dual Core processors will
> rule the earth very soon<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
> news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>>I was acutally looking at the dual core.
>> I guess my best course of action would be to throw a Xeon and a Opteron
>> in
>> a head to head and see what comes out as the leader.
>>
>> "Coldman" <nomorespam@.mail.com> wrote in
>> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>>
>

Opteron performance with reindex operation

Hi,

We have been migrating some of our itanium servers to Opteron. With the last migration, we move our Siebel analytics server to a 4 way Opteron (4 dual core) with 64 GB RAM - SQL server Enterprise edition 32 bit, with AWE enabled. Every task that is executed against this new server (queries, nightly data ETL jobs etc) have shown a massive improvement in performance with the sole exception of a reindex job that creates a clustered index (using with drop eixsting option) on one the the large ETL tables (6 - 8 million rows, clustered index is 7 columes wide, 2 varchar (30), 3 numeric, 2 datetime). The reindex job runs after the ETL process that loads the data into the table. The ETL modifies approx 30% - 40% of the table data, so fragmentation is severe after the ETL load. The prior reindex method that we were using (db reindex rather than a complete rebuld took much longer to complete). Also, dropping the clustered index before the ETL is not supported (although it is outside the scope of this question, I thought I would mention it in case anyone was wondering why we didnt drop the index before loading the data).

I can run the same reindex job against the same data set on a dual zeon with 6 GB of RAM in 26 minutes... On the Opteron it takes almost 2 hours.....

Performance in every other aspect is phenomenal Any help would be appreciated.

Hi Mike,

Please let us know if this issue is now resolved.

It could be that the problem is IO related on your new server. but then again everything else runs fine.

Are you able to tell if any of the susbsystems (IO, memory, CPU) are under pressure during the re-index. What the re-index process is mostly waiting on?

Is it SQL 2000?

What is the recovery model you use during the re-index on both the boxes?

regards

Jag

|||

Are you running on SQL Server 2005 or SQL Server 2000. Especially if you have 64GB of RAM, you might want to consider using the 64-bit version instead of using 32-bit with AWE, since it lets you use your memory more effectively than 32-bit with AWE.

Does it look like you are seeing memory pressure or IO pressure during the clustered index rebuild? Do you have the "Sort in TempDB" option enabled for the index?

Perhaps the index rebuild is spilling over to disk (where the index is stored or in TempDB). Microsoft recommends having one TempDB file per processor (and dual core processors count as two).

Opteron performance with reindex operation

Hi,

We have been migrating some of our itanium servers to Opteron. With the last migration, we move our Siebel analytics server to a 4 way Opteron (4 dual core) with 64 GB RAM - SQL server Enterprise edition 32 bit, with AWE enabled. Every task that is executed against this new server (queries, nightly data ETL jobs etc) have shown a massive improvement in performance with the sole exception of a reindex job that creates a clustered index (using with drop eixsting option) on one the the large ETL tables (6 - 8 million rows, clustered index is 7 columes wide, 2 varchar (30), 3 numeric, 2 datetime). The reindex job runs after the ETL process that loads the data into the table. The ETL modifies approx 30% - 40% of the table data, so fragmentation is severe after the ETL load. The prior reindex method that we were using (db reindex rather than a complete rebuld took much longer to complete). Also, dropping the clustered index before the ETL is not supported (although it is outside the scope of this question, I thought I would mention it in case anyone was wondering why we didnt drop the index before loading the data).

I can run the same reindex job against the same data set on a dual zeon with 6 GB of RAM in 26 minutes... On the Opteron it takes almost 2 hours.....

Performance in every other aspect is phenomenal Any help would be appreciated.

Hi Mike,

Please let us know if this issue is now resolved.

It could be that the problem is IO related on your new server. but then again everything else runs fine.

Are you able to tell if any of the susbsystems (IO, memory, CPU) are under pressure during the re-index. What the re-index process is mostly waiting on?

Is it SQL 2000?

What is the recovery model you use during the re-index on both the boxes?

regards

Jag

|||

Are you running on SQL Server 2005 or SQL Server 2000. Especially if you have 64GB of RAM, you might want to consider using the 64-bit version instead of using 32-bit with AWE, since it lets you use your memory more effectively than 32-bit with AWE.

Does it look like you are seeing memory pressure or IO pressure during the clustered index rebuild? Do you have the "Sort in TempDB" option enabled for the index?

Perhaps the index rebuild is spilling over to disk (where the index is stored or in TempDB). Microsoft recommends having one TempDB file per processor (and dual core processors count as two).