how to move index pages to another NDF file from MDF file?

Question

how to move index pages to another NDF file from MDF file?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

GilaMonster (8/21/2011)
When most of my queries use covering indexes
When my data is in cache already (which hopefully is most of the time)
When a query is only using the clustered indexes of the tables it's querying.
When those two drives share the same IO channel at any point
To pull that off and have it show performance improvements you need to do a lot more thought and a lot more design than just 'tables on filegroup 1, indexes on filegroup 2'

Of all the scenarios you mention, none would cause performance degradation, if nonclustered indexes and data were split up.

At worst, performance would not improve relative to a situation where data and nc indexes were in same filegroup and same drive.

So we have a situation where Microsoft recommends something as a best practice.

If adopted, it "may" help with performance, but most certainly will not hinder it.

If not adopted, any potential gains that might have resulted from it being adopted, are lost.

I agree that this is not a panacea.

But to come out and say this does not matter, is I think a stretch, especially after there is some evidence to the contrary.

What is the point of "best-practice" recommendations from Microsoft, if people are not going to pay any attention to them?

These recommendations provide a foundation and a starting point of building successful systems and applications.

__________________________________________________________________________________
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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Marios Philippopoulos (8/22/2011)
But to come out and say this does not matter, is I think a stretch, especially after there is some evidence to the contrary.

I never said that it doesn't matter.

I said that just because Microsoft recommends something does not mean that you must go out and do it. (There have been very dangerous recommendations that have come from MS in the past)

I said that it won't automatically improve performance

I said that it's a lot more complex than just 'data here, indexes there' (and BoL states that too)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

GilaMonster (8/22/2011)
I never said that it doesn't matter.
I said that just because Microsoft recommends something does not mean that you must go out and do it. (There have been very dangerous recommendations that have come from MS in the past)
I said that it won't automatically improve performance
I said that it's a lot more complex than just 'data here, indexes there' (and BoL states that too)

I fully agree with all of the above.

__________________________________________________________________________________
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]