1

I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask.

My application caveats

In my application I have the concept of Show.

This means that a user can create/open a Show and then it will access only the objects related to that Show (Actors, clothes, items, etc). So it is really commom to have queries using "...WHERE ShowId = X"

Also we do have quite a lot of inserts because of integrations (we have a job running daily that would get +/- 30k new rows to the database, all in the same Show) and deletions are not that commom... The user may delete a Show (which is really taking quite a long time).

The usage of the application is usually like:

  • User creates a Show based on a "Master Show" (which copies a lot of data changing only the ShowId)
  • User navigates around some screens to check this data
  • User calls an algorithm that proccess the input of this show and generate a lot of outputs (30k+ inserts again, on the same show)
  • User analyses these outputs

The question

Based on that, I thought that the best clustered key would be on [ ShowId, id ] for every table. But then, reading this documentation, I have doubts if this wouldn't make it worse given the number of inserts or if, because the inserts are made on the same ShowId, it would be ok.

I don't have access to the PRD database nor to a copy of it for testing. I tested with the dev database but it is so small it didn't seem to have any impact at all.

Can someone help?

closed as too broad by Erik Darling, mustaccio, RDFozz, LowlyDBA, Marian Nov 16 '17 at 18:09

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Your idea looks good. I suggest you create some sample data so the database size grows enough (as the production database or even more, 10 times more) and test. You may see benefits from this - but its possible they will only appear with larger sizes. – ypercubeᵀᴹ Nov 15 '17 at 10:09
  • It's also possible that some queries are slow because of lack of proper indexes, so the design doesn't make much difference in that case. – ypercubeᵀᴹ Nov 15 '17 at 10:09
0

30k to 40k rows of Insert/update/Delete is not considered a big problem.

CI should be the most searchable column of each table.

Yes CI do have impact on DML due to index fragmentation.As you are saying there can 30k insert in same showid.You can minimise this be tuning correct Fill Factor.

Choosing correct fill factor will avoid frequent page split.

Correct application design (Not look and feel),DB design and optimize query is very important.

As per you explanation one user insert 30k rows and next minute it will analyse those 30K rows.That means Insert/Select both are equally important in your application and Select have more weight-age than insert.

So CI will be there in each table.only concern is correct choice of KEY and fill factor.

I mean, it would be slower if it was with different ShowId? My understanding is, since this is CI, then each insert would have to physically shift positions to insert. Is that true? If so, why having the same ShowId makes any difference?

Sorry If i am wrong.See if there are 30k insert,and showid is CI and also there are different Showid then insert will be relatively slower.Why ? Because CI are ordered and optimizer will first search for for correct sequence of each page then insert showid one by one.Example Showid 2 will be inserted between 1 & 3 and showid 34 will be inserted between 33 & 35 and so on.So optimizer task will increase.

In case of similar showid,optimizer have to search just once.

I think this link will clear your doubts,

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

Of course first read about fill factor also.

And Rebuild Index every 1-2 days.

  • Hi Kumar, thanks for answer. Im actually not familiar with Fill Factor, will search about it. Just one thing that is not clear to me: Does it make difference the fact that I insert 30k rows with the SAME ShowId? I mean, it would be slower if it was with different ShowId? My understanding is, since this is CI, then each insert would have to physically shift positions to insert. Is that true? If so, why having the same ShowId makes any difference? (Only maybe in batch insertion) – João Menighin Nov 15 '17 at 10:21
  • Fill Factor won't be useful here, as the page splits will only happen on the boundary between two different ShowIds. There will be a half-full page at the end of each Show that will take the page splits. All the other pages will be 100 full, and you wouldn't want to leave them partially empty since they will not generate a lot of page splits. – David Browne - Microsoft Nov 15 '17 at 15:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.