EMC Clarion Performance Issue

[sjs]

I am in the process of testing an EMC Clarion install with our Data
Warehouse. Performance is fast and consistent for queries using a
clustered index but very poor for queries using non-clustered indexes.
Performance on non-clustered indexes is very slow and inconsistent
compared to our current production environment using EMC Symmetrix.

I eliminated the server and SQL install as the issue by testing the
same queries on local disk on the server with good results. I am
running SQL2000 SP3. EMC has not helped yet. Any ideas?

Thanks

[Bernie Velivis]

sjs,

I am currently benchmarking a CX200 and find query performance good with
both clustered and non-clustered index lookups. My environment is Windows
2000 SP4, and SQL Server Enterprise Edition 8.00.760, Dell 2650 Dual 2.8 GHz
processor.

I have 10 X 36GB 15Krpm drives in one large raid 0 stripe set (to
approximate 20 X 36GB configured as raid 0+1 at 1/2 the cost... Its a
benchmark environment, I can afford to loose data.). I have my log file one
naked 36GB drive on its own controller (to dedicate it's write back caching
to help log file writes). I put each hot table in a separate file group, and
the non-clustered for those hot files each into their own separate file
group. Default alloc on the disks is 8K.

Perhaps if you shared your physical disk layout and how db objects are
mapped onto the array, it would help. Also include how the disks have been
formatted by windows (If you are seeing a lot of split IOs? If so, you might
have formatted the drives at something less then 8K)

-Bernie

"sjs" <ss******@connecticare.com> wrote in message
news:6c**************************@posting.google.c om...

I am in the process of testing an EMC Clarion install with our Data
Warehouse. Performance is fast and consistent for queries using a
clustered index but very poor for queries using non-clustered indexes.
Performance on non-clustered indexes is very slow and inconsistent
compared to our current production environment using EMC Symmetrix.

I eliminated the server and SQL install as the issue by testing the
same queries on local disk on the server with good results. I am
running SQL2000 SP3. EMC has not helped yet. Any ideas?

Thanks

[sjs]

This server is attached to 6 X 146GB 10Krpm drives with Raid 5.
Unfortunately, many years ago, production was set up with data,
indexes, and logs on one raid. Because of this I am doing the testing
with the same configuration. Production will have the data and
indexes on raid groups. I have actually tested with both
configurations (same and separate raid groups) with the same results.
Performance is so poor that performance monitor looses connection for
5 to 10 sec at a time. The server is a HP with dual Zeon 2.8 GHz
running Server 2003 and SQL2000 SP3. I checked for split IOs and
found none.

Thanks

[Bernie Velivis]

If you are suggesting that the performance monitor is loosing data, then you
are might be running SQL server with priority boost enabled and scanning
fairly large tables Do you have a sample show plan for a query that uses the
non-clustered index?

-Bernie

"sjs" <ss******@connecticare.com> wrote in message
news:6c**************************@posting.google.c om...

This server is attached to 6 X 146GB 10Krpm drives with Raid 5.
Unfortunately, many years ago, production was set up with data,
indexes, and logs on one raid. Because of this I am doing the testing
with the same configuration. Production will have the data and
indexes on raid groups. I have actually tested with both
configurations (same and separate raid groups) with the same results.
Performance is so poor that performance monitor looses connection for
5 to 10 sec at a time. The server is a HP with dual Zeon 2.8 GHz
running Server 2003 and SQL2000 SP3. I checked for split IOs and
found none.

Thanks