September 2, 2008 at 11:45 am
I have always thought non-clustered indexes and data should reside in separate physical devices, but I want to hear it from other people as well, just to get an independent confirmation.
How critical is it to separate non-clustered indexes and data this way, especially for a large data warehouse database?
Anyone have any stories to share?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 2, 2008 at 11:57 am
I'd say if you can put the nonclustered indexes on a seperate filegroup on a seperate set of disks, you should. You can take advantage of parallel IO's between the nonclustered indexes and the actual data (clustered index/heap).
😎
September 2, 2008 at 12:02 pm
Thanks for the input!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 2, 2008 at 12:31 pm
There can be an advantage to this when it comes to writing the indexes. I've read that, when you do this, indexes that are commonly joined together in queries should be on the same drives. I tested this once, but at the time I tested it, I didn't really know enough about how to compare these things to have a definitive answer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 12:40 pm
I wish I had a server configured where I could test it out. I'm just going on what I have read about databases and physical design. I think this is a time to ask others what they have read or learned through experience.
😎
September 2, 2008 at 1:27 pm
Lynn Pettis (9/2/2008)
I wish I had a server configured where I could test it out. I'm just going on what I have read about databases and physical design. I think this is a time to ask others what they have read or learned through experience.😎
In our shop we place non-clustered indexes in a separate filegroup from the data, but I noticed this morning that our developers had been neglectful lately and were creating indexes in the PRIMARY file-group. In production we have placed the IDX filegroup on a separate drive from PRIMARY. I need to find out from our SAN admin whether the 2 drives are actually on 2 separate physical devices or striped on the same phys. device on the SAN. Assuming they do correspond to 2 different phys. devices, I wonder what kind of performance hit we are currently getting from having non-clustered indexes defined on the PRIMARY file-group (as opposed to IDX).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 2, 2008 at 1:33 pm
Indeed, this guideline to enhance IO still goes for sql2005 and 2008.
However, if you're going for partitioned objects, you should keep in mind there is a huge advantage in keeping your indexes aligned. (because of your partitioning rules then also are being applied to your indexes !)
Read the white papers regarding partitioning ! and make sure you understand them.
Just make sure you understand the difference between a clustering index and a non-clustering index.
Same goes for disc size. Prefer more smaller disks over one big one to take advantage of parallel IO capacity.
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
September 2, 2008 at 1:36 pm
ALZDBA (9/2/2008)
Indeed, this guideline to enhance IO still goes for sql2005 and 2008.However, if you're going for partitioned objects, you should keep in mind there is a huge advantage in keeping your indexes aligned. (because of your partitioning rules then also are being applied to your indexes !)
Read the white papers regarding partitioning ! and make sure you understand them.
Just make sure you understand the difference between a clustering index and a non-clustering index.
Same goes for disc size. Prefer more smaller disks over one big one to take advantage of parallel IO capacity.
Thanks for the recommendation, but we are not using partitioning at the moment. We may be getting into that though in the near future.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 3, 2008 at 6:31 am
Disk operations working the way they do, more spindles is better than fewer. That's at least part of why SAN's (properly configured, propertly maintained, etc.) succeed so well. I would definitely suggest breaking up the storage if you can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2008 at 1:25 pm
I am not a big fan of SAN. We had our prodiction set up with Clustering (Ofcourse SAN as well) . Everything was redundant and there is one point where it is not redundant, and that failed bringing down the whole SAN thus the DB. (It was Controller that freaked out).
I prefer Hardwired Raid Array. For clustering, you could always use Marathon Cluster.
-Roy
September 3, 2008 at 6:31 pm
Grant Fritchey (9/3/2008)
Disk operations working the way they do, more spindles is better than fewer.
"Oh, oh, oh! More Power!"
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2008 at 6:58 pm
Grant Fritchey (9/3/2008)
Disk operations working the way they do, more spindles is better than fewer. That's at least part of why SAN's (properly configured, propertly maintained, etc.) succeed so well. I would definitely suggest breaking up the storage if you can.
Unless you're dealing with random writes on a RAID5, in which case more spindles = higher write penalty....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 4, 2008 at 8:02 am
Roy Ernest (9/3/2008)
I am not a big fan of SAN. We had our prodiction set up with Clustering (Ofcourse SAN as well) . Everything was redundant and there is one point where it is not redundant, and that failed bringing down the whole SAN thus the DB. (It was Controller that freaked out).I prefer Hardwired Raid Array. For clustering, you could always use Marathon Cluster.
Pretty much all of the SAN's I know of allow for redundant controllers, which would have avoided this single point of failure.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 4, 2008 at 8:09 am
I know very little about SANs but one thing that's always bothered me is the fact that multiple drive letters in each server can be striped across a single physical device/spindle. That's as SQL-Server unfriendly as it gets!
What are workarounds to address this problem? Ideally one would want separate spindles for data, logs, non-clustered indexes and tempdb. I think that should be the least requirement for proper SQL Server performance.
Can SANs accomodate this requirement and to what extent?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 8:12 am
We go through the same worries about SANS all the time. You're completely dependent on your SAN admins to set up the drives appropriately and maintain them well. If your SAN admins make poor choices, you'll never know except you'll be seeing all kinds of I/O problems. However, once you get confidence that your admins are doing their jobs correctly (and believe me, that surrender of control is difficult), then everything works just fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply