Disclaimer: I am trying to tune a database where the queries cannot be modified. I can only adjust indexes for the most part.
I have a table that is a real table but is used essentially as a temp table. A report runs, inserts thousands of rows into this table, opens a cursor to update each row on that table one by one, then does a final select from that table based on a userId and unique queryid. a sql agent job deletes records that are older than 1 hour every 30 minutes.
This table has a primary key of 5 rows: userid, queryid, date, and two other columns.
there are only 2 non clustered index on the table and they already have long durations of page and row level locks. I am concerned about needing another one as it may cause more issues. The current indexs have a fill factor of 65 to help with the high level of inserts. This was already configured like this before i got involved.
any help on preventing such long waits on a single table would be helpful. its not easy because none of the queries use no lock and all gather into this one table.
would it make any sense to remove the primary key, add a single icrementing primary key and enforce uniqueness through a non clustered key? this would just be to help the other indexes reference the primary key by id instead of 5 columns