473,239 Members | 1,549 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

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 2339


"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Phil Powell | last post by:
Retracing my problem leads me to believe I never successfully created fulltext indexes for MySQL 3.23.58 MyISAM tables. I went to the MySQL manual and was able - or so I thought - to create them,...
4
by: jens.aggergren | last post by:
Something strange. I have a database(SQL2000) with two file group(on seperate physical drives). One is meant for table data and one for indexes . So i create a table on the file group, and...
4
by: Melissa | last post by:
I have a frontend file named CustomerApp and backend file named CustomerData. CustomerApp is at C:\Customer Database and CustomerData is at S:\Customer Database. Could someone help me with the code...
8
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub...
2
by: tekanet | last post by:
Hello folks, I'm developing a program that reads an access MDB file and produce some scripts to rebuild the same structure against other databases (MSSQL, MySQL and so on). Reading structure...
5
by: Shiraz | last post by:
Is there some manual on how to make indexes? for example: table is create mytable ( a varchar, b varchar, c int, d int
5
by: shelleybobelly | last post by:
Hi, I have a new job. It needs to drop and re-create (by insert) a table every night. The table contains approximately 3,000,000 (and growing) records. The insert is fine, runs in 2 minutes. The...
4
by: Owen Jenkins | last post by:
Hi, No-one replied to this when I sent it last week. Any bites out there today?? ----- My application allows users to create a new back end for separate purposes. It does this by using Make...
1
by: rcamarda | last post by:
I was checking out an indexes property via SQL 2005 Management Studio and it looks like I can move the index to other filegroups via the drop down. I checked the BOL ALTER INDEX and I didnt see an...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.