I support the DB for a 3rd party application. Lately users have been complaining about the application being sluggish. Checking into it I found that the vendor decided to write their own locking system (much like peoplesoft does/did).
In short, I have a very small table (maxes out at around 3000 rows, 5 columns (int, varchar(30), int, varchar(30), int, int). They store locking records in the table, they are inserted and deleted at a rate of about 10 per sec/600-900 per min. The PK is
composite across the columns required to enforce uniqueness. There is no identity column.
The IO involved in these inserts and deletes results in enormous amounts of writelog waits. Since the application is built around this custom record locking, and this table is the record of who has what locked, delays on these inserts and deletes translates
to delays on everything.
I already have plans to address the disk subsystem. In the interim, is there anything I can do to relieve IO this table generates from a design perspective?
View Complete Post