Separating Index from Data

Question

Post reply

Separating Index from Data

Patti Johnson

SSCertifiable

Points: 7054
More actions
August 4, 2010 at 6:00 am

#144395

I need to separate the indexes from the data in the database MDF file. Has anyone done this before? I know I need to create an NDF file but what I don't know is what is the best process to extract the indexes from the data and put in the NDF.
Thanks.
Patti

Viewing 11 posts - 1 through 10 (of 10 total)

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

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 1

You may want to check this out:

http://technet.microsoft.com/en-us/library/ms175905.aspx

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

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

Patricia Johnson (8/4/2010)
I need to separate the indexes from the data in the database MDF file.

Why?

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 3

For performance reasons. I have a know it all application owner "wanna be DBA" telling me that the users are complaining about slow performance. He sees high utilization on the drives where 6/7 databases are located. The mdf's, ldf's and tempdb are located on separate drives already. Backup are on another drive. Now he wants me to separate out the indexes and put them on another drive.

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

Have you checked and confirmed that the slow performance is due to an IO bottleneck? If not, splitting the indexes out may do nothing for performance.

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 5

I have attached a document with metrics. The H: drive is where all the MDF files are located. The I: drive is where all the LDF files are located and the T: drive is where the transaction log backups are written to every 15mins.

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

Mind zipping that? It's a little large.

i wasn't asking if you were seeing high disk usage. You said that you were. I asked if it has been checked and confirmed that the SQL performance problems are caused by the disks, as opposed to inadequate indexing, blocking, etc.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

Complete agreement with Gail. Rather than knock yourself about moving the index locations (you just have to recreate the indexes and reference the new file or filegroups using the ON clause, BTW), you would be much better off determining the precise pain points within the system.

"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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 8

I am resending the attachment zipped. No it hasn't been confirmed that the SQL performance problems are caused by the disks. I am not sure how to tell. There are separate people that manage disks in this company. What would be the best approach to determining if the indexes and/or blocking is inadequate. We run weekly optimization jobs on the databases. Specifically reorganizing index pages and perform database integrity checks but they exclude indexes. This is sql 2000 (sorry). I am stuck with this release until they are ready to move to 2005 or 2008.

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 9

Thanks Grant. I with you guys on this. Now I just need to figure out how to do that. I in know way consider myself an expert and really appreciate everyones help.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Because you're working with 2000, this will be harder, but what you need to do is analyze the wait states and queues to figure out what you're slowing down on. For 2005 and up you can refer to this white paper from Microsoft as a starting point. But for 2000... It's just harder. You can look at this sys.processes view to see what things are waiting on. Microsoft is hiding a lot of the 2000 documentation. Here's the 2008 version and more.

Additionally, I'd suggest setting up a server side trace and capture your TSQL performance metrics directly. Frequently just identifying and fixing the longest running or most frequently called queries is all that's needed.

"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