Moving Indexes to separate RAID

[timscronin]

Has anyone seen any great success in moving indexes to a separate RAID array. I realize you don't want to do this with clustered, but I have a situation where we are going to expand a Storage array, and was considering doing this. it is for a 3rd party app of with 4 db's which constitute about 25 gig with 60 users doing Order Entry.

 

[mrdenny]

Yes, I've done this in the past, and seen great performance improvement when done on a high load system.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

Did you move your heavy high used indexes? My other option is to move heavily used tables to this RAID

 

[mrdenny]

It will depened on the system. Check your execution plans. Where are you spending the most IO time, on the heavly used indexes, or the heavily used tables?

You may want to invest in another new array and move the tables to one and the indexes to another. (Or if you have enough drives in this array, create two arrays one for each.)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

keep in mind that you can't seperate clustered indexes and tables
if you create a table on Filegroup A then add a clustered index to that table and create that on filegroup B
Guess what? Your whole table will reside on Filegroup B now
Nonclustered indexes are fine to move to another filegroup

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[timscronin]

Interestingly enough, the 3rd party vendor who we are upgrading their database, went guid happy with all their indexes, with no clustered indexes on any of their tables. Which is I guess why they recommend a separate array for their indexes. Want to here real fun, some of their tables use multiple giuds as the primary key!

 

[SQLDenis]

>>some of their tables use multiple giuds as the primary key

wow,

when you do
dbcc showcontig ('TableName') with tableresults

what is the average RecordSize 1 K? ;-(


>>with no clustered indexes on any of their tables
When you create a primary key SQL server creates a clustered index on those column(s) if you DON't specify nonclustered

create table abd (id int primary key nonclustered)

insert abd values(1)

Go

create table abd2 (id int primary key)

insert abd2 values(1)

Go



dbcc showcontig ('abd') --heap

dbcc showcontig ('abd2') --clustered
go
drop table abd2,abd

go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[timscronin]

All their guids are uniqiue no clustered. In fact no clustered on any of their tables(what a novel idea)