Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
Oskar
You might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing RAID level

Hi,
In your opinion what would be a better choice for running Microsoft SQL
Server 2000 on Microsoft Windows 2000 Advanced Server:
1) 4-disk RAID10 for OS and tempdb,
2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
Why?
The server will be dedicated only for running the SQL Server so paging
shouldn't be an issue.
--
Many thanks,
OskarYou might want to start here:
http://b.wunder.home.comcast.net/18960.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:51D6E5C7-88C3-45B4-96A9-14C3CCDCCD2C@.microsoft.com...
> Hi,
> In your opinion what would be a better choice for running Microsoft SQL
> Server 2000 on Microsoft Windows 2000 Advanced Server:
> 1) 4-disk RAID10 for OS and tempdb,
> 2) 2x2-(same)disk RAID1 - one for OS, one for tempdb
> Why?
> The server will be dedicated only for running the SQL Server so paging
> shouldn't be an issue.
> --
> Many thanks,
> Oskar
>

Choosing correct database

Hello,
So far, I've been programming distributed multiuser applications using MSDE
as my choice of db.
I got another project that requires client (local) application.
Which database would you suggest?
I don't want to use MSDE as I'm having fear about it running as service,
they might disable it + it will put additional cpu load on client.
Is Access or plain XML solution to my problem ?
Of course, I'm trying to stay as cheap as possible, if not free.
Thank you
Vlado
Vlado,
Jet might work depending on the number of users and the size.
HTH
Jerry
"Vlado Jasovic (excelleinc.com)" <vjasovicATexcelleincDOTcom> wrote in
message news:OkTNX0n1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hello,
> So far, I've been programming distributed multiuser applications using
> MSDE as my choice of db.
> I got another project that requires client (local) application.
> Which database would you suggest?
> I don't want to use MSDE as I'm having fear about it running as service,
> they might disable it + it will put additional cpu load on client.
> Is Access or plain XML solution to my problem ?
> Of course, I'm trying to stay as cheap as possible, if not free.
>
> Thank you
> Vlado
>

Choosing correct database

Hello,
So far, I've been programming distributed multiuser applications using MSDE
as my choice of db.
I got another project that requires client (local) application.
Which database would you suggest?
I don't want to use MSDE as I'm having fear about it running as service,
they might disable it + it will put additional cpu load on client.
Is Access or plain XML solution to my problem ?
Of course, I'm trying to stay as cheap as possible, if not free.
Thank you
VladoVlado,
Jet might work depending on the number of users and the size.
HTH
Jerry
"Vlado Jasovic (excelleinc.com)" <vjasovicATexcelleincDOTcom> wrote in
message news:OkTNX0n1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hello,
> So far, I've been programming distributed multiuser applications using
> MSDE as my choice of db.
> I got another project that requires client (local) application.
> Which database would you suggest?
> I don't want to use MSDE as I'm having fear about it running as service,
> they might disable it + it will put additional cpu load on client.
> Is Access or plain XML solution to my problem ?
> Of course, I'm trying to stay as cheap as possible, if not free.
>
> Thank you
> Vlado
>

Choosing correct database

Hello,
So far, I've been programming distributed multiuser applications using MSDE
as my choice of db.
I got another project that requires client (local) application.
Which database would you suggest?
I don't want to use MSDE as I'm having fear about it running as service,
they might disable it + it will put additional cpu load on client.
Is Access or plain XML solution to my problem ?
Of course, I'm trying to stay as cheap as possible, if not free.
Thank you
VladoVlado,
Jet might work depending on the number of users and the size.
HTH
Jerry
"Vlado Jasovic (excelleinc.com)" <vjasovicATexcelleincDOTcom> wrote in
message news:OkTNX0n1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hello,
> So far, I've been programming distributed multiuser applications using
> MSDE as my choice of db.
> I got another project that requires client (local) application.
> Which database would you suggest?
> I don't want to use MSDE as I'm having fear about it running as service,
> they might disable it + it will put additional cpu load on client.
> Is Access or plain XML solution to my problem ?
> Of course, I'm trying to stay as cheap as possible, if not free.
>
> Thank you
> Vlado
>

Choosing clustered index

Dear friends:
There is some confusion here about the choice of which index should be
clustered. The choices are generally:
- the surrogate identity column.
- one or more columns that make up the natural key.
My contention has been that the latter is the obvious choice. This is the
order in which the rows are commonly placed on the screen and on reports.
The identity column really places the rows in no particular order at all,
except somewhat "sequentially" with respect to the order they were entered
(assuming an incrementing integer key).
What is the conventional wisdom about this?
Tom EllisonUsually clustered indexes should:
- Be as narrow as possible.
- Be placed on columns that would benefit the most:
-- Columns that have very high cardinality are good candidates.
-- So are columns that are searched for large ranges at a time using
operators like BETWEEN, LIKE 'x%' and >, <, etc.
-- Columns that are searched the most frequently, since they eliminate
bookmark lookups which can occur with narrow nonclustered indexes.
A lot of times the Primary Key will fit a lot of these requirements, but not
always. I usually would only make an IDENTITY column the clustered index on
supporting (lookup-type) tables, to improve JOIN performance. One or more
columns (with high cardinality) that make up the natural key on your main
tables would be good clustered index candidates.
[url]http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/05/08/599.aspx[
/url]
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
> Dear friends:
> There is some confusion here about the choice of which index should be
> clustered. The choices are generally:
> - the surrogate identity column.
> - one or more columns that make up the natural key.
> My contention has been that the latter is the obvious choice. This is the
> order in which the rows are commonly placed on the screen and on reports.
> The identity column really places the rows in no particular order at all,
> except somewhat "sequentially" with respect to the order they were entered
> (assuming an incrementing integer key).
> What is the conventional wisdom about this?
> Tom Ellison
>|||Dear Mike:
Thanks for the opinions. The article was quite helpful.
Now, how do you figure that an IDENTITY column makes a good clustered index?
I ask because:
- in my experience, processing rarely proceeds in identity number order.
- user directed searches don't proceed along these lines
- moving through the data in some sequential order, such as generating data
for the screen or a report will follow a natural key order, not an identity
order
My thought is that the identity column is assigned sequentially over time
(if auto-incremented) but does not generally follow any organization of the
data that is likely to be repeated.
The natural key of which I spoke is unique (thus, highly cardinal) and tends
to be the most commonly used sequence in processing (screens and reports).
It is often the column(s) filtered or JOINed in many of the queries used.
Thanks again,
Tom Ellison
"Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
> Usually clustered indexes should:
> - Be as narrow as possible.
> - Be placed on columns that would benefit the most:
> -- Columns that have very high cardinality are good candidates.
> -- So are columns that are searched for large ranges at a time using
> operators like BETWEEN, LIKE 'x%' and >, <, etc.
> -- Columns that are searched the most frequently, since they eliminate
> bookmark lookups which can occur with narrow nonclustered indexes.
> A lot of times the Primary Key will fit a lot of these requirements, but
> not always. I usually would only make an IDENTITY column the clustered
> index on supporting (lookup-type) tables, to improve JOIN performance.
> One or more columns (with high cardinality) that make up the natural key
> on your main tables would be good clustered index candidates.
> http://blogs.sqlservercentral.com/b...99.asp
x
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
>|||Tom,
are you using non-clustered indexes? If yes, take in account that
bookmark lookups take more time if bookmarks are wider.|||I generally use IDENTITY columns as clustered indexes only when it's in
supporting (lookup/foreign-key) tables. The only reason then is to improve
JOIN performance by potentially eliminating bookmark lookups you might get
with a narrow nonclustered index. Other than that, put your clustered
indexes where they'll do the most good.
"Tom Ellison" wrote:

> Dear Mike:
> Thanks for the opinions. The article was quite helpful.
> Now, how do you figure that an IDENTITY column makes a good clustered inde
x?
> I ask because:
> - in my experience, processing rarely proceeds in identity number order.
> - user directed searches don't proceed along these lines
> - moving through the data in some sequential order, such as generating dat
a
> for the screen or a report will follow a natural key order, not an identit
y
> order
> My thought is that the identity column is assigned sequentially over time
> (if auto-incremented) but does not generally follow any organization of th
e
> data that is likely to be repeated.
> The natural key of which I spoke is unique (thus, highly cardinal) and ten
ds
> to be the most commonly used sequence in processing (screens and reports).
> It is often the column(s) filtered or JOINed in many of the queries used.
> Thanks again,
> Tom Ellison
>
> "Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
>
>

choice of column for clustered index

I often founds 2 recommendations that sounds contradicting to me,
regarding what types of columns should be chosen for a clustered index
(in OLTP environment with lots of inserts):
1)avoid identity column, it will cause inserts to be slowed since they
will compete the same disk area at the end of the table (hot spot).
Instead, use a column whose new value can be at any part of the table
2)use sequential column (like identity one), so that new inserts only
happen at the end of the table and don't cause row migrations (when a
row inserted forces the next rows to move to a new page)
Can anyone give me a more sounding judgement of each of these 2
choices? I'm curious to know in which scenario, which choice is better
than the other, and what the cures are. Correct me if I'm wrong, I feel
that "row migration" is more fearful than "hot spot"
thanks,
TamUnless you're writing a million new rows a day, I doubt the hot spot concern
is valid on today's hardware. Obviously there will be a threshold but for
most applications I can envision this should be a minimal concern.
I have witnessed cases where fragmentation and page splitting, on the other
hand, has caused abysmal performance.
I can't really think of a situation where you'd rather jab new data in the
middle of a page than tack it on the end, unless you were just stuffing
every single transaction on a very busy system into an audit table that you
purge regularly and that you're rarely going to query, in which case, who
cares which way you go.
A
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125521936.674430.273880@.g49g2000cwa.googlegroups.com...
>I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Tam Vu" wrote:

> I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||> Unless you're writing a million new rows a day,
Wow, did I really say "day" there? Eep. In my experience, a hot spot comes
at a far greater volume than that. Then again, I have been spoiled with
must faster hardware than I had access to in college. :-)