Clustered Index - Trade-off Queries VS Inserts? 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?
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 &amp; 3 and showid 34 will be inserted between 33 &amp; 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.