none
Table for high

    Question

  • Hello, I have an application that constantly work on one table. 

    This table have like 2 million records and I don`t believe it will have more than 3 in the next 3 years. 

    This table is constantly access by the application to (count according to some columns), read task and update. 

    My application run fine when just one user or 5 users are using it, but when 15 users are using it, I get so many locks that its almost impossible to work with it. 

    What is the correct approach for this case? 

    Due to the kind of search, it have 2 index one clustered and one non-cluster. that`s all. 

    If i remove the index, I get no locks, but the lag is so high that my application got time out errors. 


    Gilberto H.

    Wednesday, May 9, 2018 10:12 PM

Answers

  • Hi Gilberto,

    You could try to change the database default isolation level to read committed snapshot isolation. It causes SQL Server to maintain a copy of any record being modified in tempdb which will be used for reads if the record has a lock on it.

    Hope it helps!

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as Answered if this helps!


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    Wednesday, May 9, 2018 10:30 PM

All replies