Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file
(contains all clustered indexes) and Nonclustered Index file file (contains
all nonclustered indexes). Which Raid level is the best and recommended for
clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
KtfI would suggest that this is not enough information. The standard safe answ
er will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that
you will never overflow the cache, or that the writes across 168 drives of r
aid 5 will provide excellent performance.|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped
by Dell (builtin that way). I know about raid 10 but my real question is wha
t would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file (contains all clustered indexes) and Nonclustered Index file file (contains all nonclustered indexes). Which Raid level is the best and recommended for clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
Ktf
I would suggest that this is not enough information. The standard safe answer will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that you will never overflow the cache, or that the writes across 168 drives of raid 5 will provide excellent performance.
|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped by Dell (builtin that way). I know about raid 10 but my real question is what would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

purge process from large table

Hi,
We have specific request here: there is a large table, around 5,000,000 rows
(3GB). It has unique clustered index, created on 6 of it's 10 columns. We
have to delete around 15% of rows every day, but in a way that table keeps
being available all the time. Deletion criteria is date (where
column_date<=getdate()). There is nonclustered index created on column_date
table. Since there is availability criteria, simple: delete <table name>
where column_date<=getdate() is out of the question because of exclusive
table lock on this table.
Any ideas?
Thanks,
PedjaBy "available" do you mean readable via a SELECT?
If you are using SQL 2005, you can take advantage of the new Read Committed
Snapshot Isolation level, which allows a SELECT to read the most
recently-committed version of a set of data, even while that data is being
modified.
If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
to the SELECT statements, putting the transaction into read uncommited. Your
readers won't have to wait for writers, but you may get inaccurate data.
Is it possible that you can split this delete operation so that it occurs
several times a day? That way it can delete fewer rows, resulting in less
blocking time.
"Pedja" wrote:

> Hi,
> We have specific request here: there is a large table, around 5,000,000 ro
ws
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on column_dat
e
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedja|||Mark,
We use sql server 2000. By available, I mean both, read/write operations.
NOLOCK hint won't help, because once it is grabbed by purge process, table i
s
being locked until it is completed (that is why I posted this question
initially), so neither reads nor writes are allowed during this time. My ide
a
was to split deletion process to batches of 1000 rows (set rowcount 1000),
but I wanted to hear some other ideas too.
Thanks
"Mark Williams" wrote:
> By "available" do you mean readable via a SELECT?
> If you are using SQL 2005, you can take advantage of the new Read Committe
d
> Snapshot Isolation level, which allows a SELECT to read the most
> recently-committed version of a set of data, even while that data is being
> modified.
> If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
> to the SELECT statements, putting the transaction into read uncommited. Yo
ur
> readers won't have to wait for writers, but you may get inaccurate data.
> Is it possible that you can split this delete operation so that it occurs
> several times a day? That way it can delete fewer rows, resulting in less
> blocking time.
> --
> "Pedja" wrote:
>|||Pedja,
you could split up your data into several tables, one table per day.
You can access them via a UNION ALL view. Then the purge is very fast,
you just re-create the view, which is a snap, and drop the oldest
table. There are some divantages: some of queries against the view
will work slower, and you will not be able to enforse unique (and
sometimes other) constraints just as easily|||Hi
Divide your deletion into small batches
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here your DML Statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:38445382-60B6-4327-A3FE-C5AD8BF88E41@.microsoft.com...
> Hi,
> We have specific request here: there is a large table, around 5,000,000
> rows
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on
> column_date
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedjasql