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?

enter image description here

Would appreciate any help.


  • What wait types do the sessions encounter during these delays? – Martin Smith May 18 '16 at 8:17
  • How do I find that? I get quite a lot of CXPACKET and LATCH_EX – user2624034 May 18 '16 at 9:06
  • As this seems quite reproducible and occurs frequently (every few seconds for 2-3 seconds?) you could just poll sys.dm_os_waiting_tasks whilst it is running. It is also possible to use extended events and filter on the session of interest. sqlskills.com/blogs/paul/… – Martin Smith May 18 '16 at 11:10
  • What about using the heap table vs a clustered index for this type of workload? – user2624034 May 18 '16 at 13:03

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.