April 28, 2011 at 7:21 am
I am looking to move Indexes to a second filegroup on a separate set of storage than the primary mdf storage/ filegroup. My question is: Should I move the clustered indexes AND nonclustered indexes or just the nonclustered indexes.
Background:
Sql Server 2005 Enterprise
Active/Passive nodes
EMC San Storage
April 28, 2011 at 7:27 am
If you move the clustered indexes as well, that's the same as moving the data (assuming that all tables have a clustered index). You would be left with just system tables on your primary filegroup. It all depends on your reasons for doing it. If it's for performance then you may see an improvement if you move the non clustered indexes to a filegroup on a physically separate disk.
John
April 28, 2011 at 7:31 am
Thanks John, that is where I was going with it as well. The goal is to improve performance of a heavily used application. I appreciate the quick response.
April 28, 2011 at 8:37 am
Also, make sure you update your statistics on the columns and indexes that participate in the relocation.
Some query plans look crazy after a relocation, until the stats are updated.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 28, 2011 at 8:39 am
excellent recommendation. Thanks Jim!
April 28, 2011 at 9:33 am
[Jim].[dba].[Murphy] (4/28/2011)
Also, make sure you update your statistics on the columns and indexes that participate in the relocation.
Rebuilding the indexes will update the statistics, so no need to do it again.
A relocation of indexes won't change the distribution of data in the tables, and that's the only thing that stats stores, so simply moving indexes won't require a stats update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 9:51 am
One thing to keep in mind, if you are looking for partial database recovery in a DR situation, moving the indexes away from the table can cause issues. Think about what needs to be there for your application to come up.
Not saying don't do it, but be careful. Moving just all NC indexes doesn't necessarily increase performance. To get performance increases from separate spindles you usually want to separate contentious processes to separate disks. Look at the access patterns, what do you go to disk for often, or concurrently, and separate those things.
Moving tempdb might be a better move, but you have to test some choices and see what works better.
April 28, 2011 at 9:57 am
Currently we have 3 luns set up for this instance (all dedicated Raid 10) ... one for mdf, one for ldf and one for temp db. We have created a fourth new lun to move the indexes to. Also rest assured this isn't the only thing we are doing to improve performance. I have some code changes out at the vendor being reviewed. We are moving the replicated reporting database off the same server (DBA before me did this) to a dedicated reporting server that we already have.
April 28, 2011 at 12:10 pm
Steve Jones - SSC Editor (4/28/2011)
One thing to keep in mind, if you are looking for partial database recovery in a DR situation, moving the indexes away from the table can cause issues. Think about what needs to be there for your application to come up.Not saying don't do it, but be careful. Moving just all NC indexes doesn't necessarily increase performance. To get performance increases from separate spindles you usually want to separate contentious processes to separate disks. Look at the access patterns, what do you go to disk for often, or concurrently, and separate those things.
Moving tempdb might be a better move, but you have to test some choices and see what works better.
Do you mean, for example, if the OrderHeader table and OrderDetail table are always joined and accessed at the same time, there may be some benefit to having them on different spindles ??
April 28, 2011 at 12:14 pm
homebrew01 (4/28/2011)
Steve Jones - SSC Editor (4/28/2011)
One thing to keep in mind, if you are looking for partial database recovery in a DR situation, moving the indexes away from the table can cause issues. Think about what needs to be there for your application to come up.Not saying don't do it, but be careful. Moving just all NC indexes doesn't necessarily increase performance. To get performance increases from separate spindles you usually want to separate contentious processes to separate disks. Look at the access patterns, what do you go to disk for often, or concurrently, and separate those things.
Moving tempdb might be a better move, but you have to test some choices and see what works better.
Do you mean, for example, if the OrderHeader table and OrderDetail table are always joined and accessed at the same time, there may be some benefit to having them on different spindles ??
that's how I interpreted his post ... if you think about it, the more things you can do at once the better.
I'll give an example of an old server we have. One of the drive arrays has 15 7200 rpm 36 gig drives, it outperforms a 5 disk 10k rpm 600 gig array because it is able to do more things at once.
April 28, 2011 at 12:17 pm
Yes, but it's more that if you are querying large ranges in both OrderHeader and OrderDetails and possibly aggregating or adding in lookup information from other indexes.
The benefit from separate spindles is from parallel access, either from the same or different queries.
I regret not having a good resource that I can point you to for gathering this and trying to make some calculated guesses about what to move. In the past I have seen benefit by separating out some heavily used tables into separate spindles because they were queried often. We put what was the equivalent of a fact table from our OLTP database separate from all other indexes/data and had improvement in performance, both for individual queries and from the users' perspective.
April 28, 2011 at 12:25 pm
Steve Jones - SSC Editor (4/28/2011)
Yes, but it's more that if you are querying large ranges in both OrderHeader and OrderDetails and possibly aggregating or adding in lookup information from other indexes.
And neither is in memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 12:32 pm
GilaMonster (4/28/2011)
Steve Jones - SSC Editor (4/28/2011)
Yes, but it's more that if you are querying large ranges in both OrderHeader and OrderDetails and possibly aggregating or adding in lookup information from other indexes.And neither is in memory.
Sorry, yes. Thanks, Gail.
In the past we had a 2GB ram system (total) and a 4-5GB database. One of our larger tables was approaching 600-700MB, and so when a large scan took place it couldn't fit in memory, or wouldn't be there. We would be hitting disks hard, so adding another R1 array, and moving that table helped.
April 28, 2011 at 1:29 pm
GilaMonster (4/28/2011)
[Jim].[dba].[Murphy] (4/28/2011)
Also, make sure you update your statistics on the columns and indexes that participate in the relocation.Rebuilding the indexes will update the statistics, so no need to do it again.
A relocation of indexes won't change the distribution of data in the tables, and that's the only thing that stats stores, so simply moving indexes won't require a stats update.
Ya, I think I still have fears of the way SQL Server used to work in older versions. Believe me, it was critical to restat tables after relocating indexes to another file group. I knew that rebuilding indexes rebuilds the stats as well - until I was working in an older SQL Server version about a year ago and learned the hard way it didn't. SQL 2005 probably does work properly though. I just still have a little fear relying on Microsoft for somethings.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply