Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Move indexes to separate filegroup

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-17 : 15:50:29
I've read some conflicting information on this. I currently have my database on a server with 5 different RAID 5 arrays. The database in on one array, tempdb on another, transaction logs on another, test databases on another, and the OS on another.

Seconds per read/write on the main database array is 9ms - which isn't bad. But on the C drive seconds per read/write is 0.03ms. So I was thinking of creating a second filegroup and moving 10 of the most heavily used indexes to the C array.

Thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-17 : 15:55:56
That sounds like a terrible idea to me. Just because the C drive is currently 0.03ms doesn't mean that's going to continue once you move the indexes there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-17 : 16:19:54
quote:
Originally posted by tkizer

That sounds like a terrible idea to me. Just because the C drive is currently 0.03ms doesn't mean that's going to continue once you move the indexes there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



I would only move some indexes there and then check the perf stats again. And adjust accordingly. It seems a shame to have one RAID array sitting there doing next to nothing. May as well take advantage of the extra IO horsepower.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 17:20:14
The C drive should be the OS, the page file, the SQL binaries and nothing else.

9ms/read is excellent. It's not as if you're under IO strain (when doing this might be a temp workaround for a seriously overstrained IO subsystem)

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -