Create Index on Network drive

Question

Create Index on Network drive

GF

Hall of Fame

Points: 3774
More actions
February 8, 2006 at 4:33 pm

#116145

Hi all, I have a question with multiple parts.
1.) Is it possible to create an index on a networked drive?
2.) Excluding the possibility, is it advisable?
Reason for my question:
I have sql running in a clustered environment. The databases are on a 10 disk array. So basically I have a very large area for databases to grow.
I am looking at creating indexes to improve performance. If I create the index on the array, it provides no real improvement because SQL reads from the same drive that the db is on, which will impede I/O or creates more I/O on that array.
The draw back I can see Is if I have a failure and need to restore the db to another server then all the indexes will be lost.
Any insight is always appreciated.
Gary

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>If I create the index on the array, it provides no real improvement because SQL reads from the same drive that the db is on

That part is definitely not true.

An indexed table will perform better than a non-indexed table even if the data & index pages are on the same physical drive(s).

It is not optimal, but it is still better than not indexing, period.

For example, if a query requires 100 page reads if executed as a Tablescan, but requires 10 combined index & data page reads if an Indexed Seek occurs, you will get better performaince.

Strommy SSCrazy Points: 2386 More actions · Answer 2

Hello Gary. To your questions:

>> 1.) Is it possible to create an index on a networked drive?

I don't believe so.

>> 2.) Excluding the possibility, is it advisable?

If it were, it would never be advisable. If you are looking for speed improvements, doing network I/O is slower than disk I/O (easily by hundreds to thousands of times slower).

In general, indexes in and of themselves will provide significant speed improvements, even on the same disk as the data (as PW mentioned). If you are considering ways to maximize performance in regards to indexes, your best bet is to know when to use indexes and understand when SQL uses indexes as opposed to table scans, etc. If you know SQL Server internals, what "sargable" means, and other things like that, you can increase performance infinitely more than having a faster disk I/O system. I hope that helps.

Thanks,

Eric

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

just my 2ct.

in addition to PW's and Strommy 's replies :

Your cluster-instance must have the new index-drive(s) defined in the appropriate cluster-group. only then sqlserver will let you do things for that drive. Then you can create a new filegroup and add a file to host the indexes. Indexes must then be "routed" to that filegroup e.d. fgIndex

create index x1 on table y (colz) on fgIndex

Keep in mind that to sqlserver the data _and_ the indexes are one regarding restores.

Also keep in mind that a clustering index contains the data !, and therefor in your topology needs to reside in the data-filegroup (primary ? ).

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

1.) Is it possible to create an index on a networked drive?

Answer ==> NO

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

http://support.microsoft.com/default.aspx?scid=kb;en-us;812504

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."