I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some performance gains (which I read could be debatable, but I think in my case it is true).

I would ideally like to create a table on primary with an index on indexes to realize these gains, however the PK constraint happens to contain the same columns we would normally query, therefore the clustered PK index must, by definition also live on primary.

Would I still realize any performance gains if I created a nonclustered index containing the same columns as the PK on the indexes FG?

  • Can you clarify if the nonclustered index colums would ONLY be on the key columns of the primary key (and I'm assuming clustered index)? – Erik Darling Dec 8 '17 at 20:49
  • Also, unless you're using direct attach storage (or your SAN has a much higher tier of storage you can pin the nonclustered indexes to), I'd skip separating anything. – Erik Darling Dec 8 '17 at 20:50
  • @sp_BlitzErik that is correct in that that was my intention. If my primary key was on columns A, B, and C, users will query the database specifying A, B, C, and also pulling in additional aggregate columns grouped by A, B, and C, generally. – dav3ypants Dec 8 '17 at 20:53
  • @sp_BlitzErik it's direct attached storage. – dav3ypants Dec 8 '17 at 20:55
  • 1
    My first step would be to pack as much RAM in the server as possible so that disk isn't as much of a factor. Separating data out rarely gets anyone the kind of performance increase they're looking for. – Erik Darling Dec 9 '17 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.