We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, i.e. add one to the click_events table, adds a row to the devices_seen table etc.
The main, large tables are those raw tables that store events (e.g. click_events). Those have 1-2 indexes but no clustered index and an auto-increment PK. They sed to have clustered indexes (hence the identity PK) but were later removed (many months ago).
The problems we are facing are as follows:
1) The processors that pick the events take 30-50 ms for each register (the merges). Often - every few seconds - the process stops / locks for 2-3 seconds and that event takes that amount of time to register. Why is that? Is there anything related to the database that we are missing? We are running the profiler but nothing really stands out.
2) Our heap tables get super fragmented often. I thought that on inserts only they won't get any fragmentation. Are we better off creating clustered indexes on those identity columns and let the DB do its job?
Would appreciate any help.