July 22, 2013 at 4:06 pm
For performance sake. I have a server with drives D:, E:, & F:. I have a large table I want to create a ndf for TableA on E:. and place all non clustered indexes for TableA on D:. I have another large table TableB, I want to place this tables Clustered index on D: and its non clustered indexes on E:. The F: drive is where the log files reside.
Is this design a bad concept? Should both TableA and TableB Clustered indexes be on the same drive?
I understand the the clustered index is in fact where the table is living.
Question is which is the better practice for gaining peformance out of large tables.
Thanks,
Mike
July 22, 2013 at 4:09 pm
No simple answer here, you'd need to analyse usage patterns, whether the tables get read often from disk, whether the system is even IO bottlenecked, whether those are partitions on the same array or separate drives, etc, and test, test, test, test.
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
July 22, 2013 at 4:18 pm
+1 to what Gail said. You need to test, test, test. If nothing else, test multiple configurations and compare them.
July 22, 2013 at 5:15 pm
Is the method I sugeted a bad practice on high I/O bottleneck system? Is it a common practice to stagger the table/Cluster on one drive and place the non-Clustered on another drive?
July 22, 2013 at 5:37 pm
Some people do this. It can help, but it depends. Also, if you recover the database partially, you may have really poor performance if you are missing indexes.
People may move different data onto different drives as well, perhaps putting large tables that are joined on separate filegroups.
It's not a bad plan, just needs testing.
July 23, 2013 at 1:31 am
Steve Jones - SSC Editor (7/22/2013)
Some people do this. It can help, but it depends. Also, if you recover the database partially, you may have really poor performance if you are missing indexes.
Not poor performance, actual failed queries. The definitions of the indexes would be present so the optimiser would compile plans using them and with the filegroups unavailable any query that uses an index not available will fail.
Michael, filegroup layout is a lot more complex than just 'clustered index here, nonclustered there', as such there's no real standard design, it's really very dependant on system design, performance, hardware and a whole lot more.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply