469,275 Members | 1,782 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Create indexes - own File Group

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

Mar 12 '07 #1
2 2201


"rcamarda" <ro**************@gmail.comwrote in message
news:11**********************@30g2000cwc.googlegro ups.com...
With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Mar 12 '07 #2
Greg,
Its a data warehouse. I have tools that could rebuild an empty
database pretty quickly (few hours) and I have daily backups. Builds
take about 2 hours, then its read only the rest of the day.
I use enterprise because of our licensing.

Thanks for the info!
>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Mar 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Phil Powell | last post: by
4 posts views Thread by jens.aggergren | last post: by
5 posts views Thread by Shiraz | last post: by
5 posts views Thread by shelleybobelly | last post: by
1 post views Thread by rcamarda | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.