THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Filegroups and Non-Clustered Indexes

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2013/03/12/filegroups-and-non-clustered-indexes/

Published Tuesday, March 12, 2013 12:20 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

tobi said:

Also, even append-only workloads might fragment a table and its index to 100% if both are on the same filegroup (even if nothing else is on that filegroup). They might get every other extent allocated. Using a FG per partition solves that (and AFAIK is the *only* thing that solves it proactively).

SQL Servers allocation algorithms are really awful and I have already complained about it on connect.

March 12, 2013 6:22 AM
 

Rob Farley said:

Yeah, there are a bunch of reasons to think carefully about index storage. I really think they deserve to be considered more carefully that they typically are.

March 12, 2013 6:26 AM
 

sheen81 said:

Hi, Rob, good post. Can you explain more about "it can be nice to have transactions which update both the clustered and non-clustered indexes using different disks", better with a quick example? Just a few sentences. Thank you.

March 12, 2013 8:24 AM
 

Rob Farley said:

Well, if you have some "write this" commands, it can be better to involve multiple disk controllers, rather than having it all go through the one.

March 12, 2013 9:30 AM
 

Brad Schulz said:

Hi Rob...

As I understand it, the DEFAULT filegroup only comes into play when you do a CREATE TABLE.  In other words, if no ON clause is specified with the CREATE TABLE command, it's created on the DEFAULT filegroup.  However, when you do a CREATE INDEX (without an ON clause), the index is created on the same filegroup as the table that's being indexed... not the DEFAULT one.

I guess that makes sense... even though, as you say, it's "almost never what [you] want."

(Love your date-population query, by the way)

--Brad

March 14, 2013 10:26 AM
 

Andrew Rowlings said:

Hey Rob, do you normally still go to the trouble of putting indexes in a separate filegroup on a separate disk when the disks presented to the server are all part of the same RAID array? Cheers.

May 13, 2013 11:16 PM
 

Rob Farley said:

Hi Andrew,

When the volumes are all part of the same drive, I do find myself wondering if it's worth separating them out or not. Generally, multiple volumes that map to the same physical drive array are there for one of two reasons. One is ignorance, but the other is that it's in anticipation of the configuration being used on a different server. I frequently see Dev/Play environments where the volumes are all on the same physical disk(s), but that the plan is to deploy to a machine where they are separate. So I don't want to say "Yes, if it's all part of the same RAID array, don't bother separating it", but do make a conscious decision about what you want to do and why you want to go down that path.

Rob

May 13, 2013 11:45 PM
 

Vijay Anand Madhuranayagam said:

Rob,

We already have everything on a single filegroup for a small business standard edition of SQL Server 2008 R2 database. Database size is 30 GB approximately.

We are going to move the already existing SQL Server 2012 Standard Edition DB server to the new cloud environment. We are asked to suggest the partition of the disk storage for SQL Server DB Server. We allot one for MDF, 2nd one for LDF, Third one for Backups and the Fourth one for TempDB. Our Technical Director insists us to create another drive for Indexes voluntarily. Allotting a separate drive for Indexes is a good move?

If not in which situation we can do that. If yes, will this improve the performance for this much of small database?

Thanks & regards,

Vijay

October 30, 2014 6:34 AM
 

Rob Farley said:

Hi Vijay,

I wouldn't worry. Separating indexes off is far less important than the things you've suggested around having separate disks for data files, log files, and tempdb. You could put your backups across the network even (if it's fast enough). The biggest thing to consider here is that MDFs and LDFs and Tempdb have different styles of disk activity, and should use disk that's configured to cater for that particular style.

If it's easy to seperate stuff out even more, then maybe indexes, but it's not a huge priority.

Rob

October 31, 2014 2:24 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement