Posted by Erland Sommarskog on 12/05/24 11:33

Matik (marzec@sauron.xo.pl) writes:
> Table: 1735833396 = tab_locations
> The lock is on one table.
> Table contains only 339 rows, and it's a static table (no insert's
> performed, just updates and selects)
>
> Index: 1 = primary key, clustered, on "IDX" field (static) (fill factor
> 90%)
> Index: 3 = non-clustered index, on "fk_dtsIndex" field, which is
> updatable very often, and used by other resources (fill factor 90%).
> Procedure: proc_importData
> Procedure: proc_generateNextOrder

Without seeing the queries that deadlocks it is a tad difficult to say
for sure, but 339 is not much for a table. Unless the table has excessive
row size, I would not expect that the index be that much of a performance
booster. Right now it appears to be a deadlocker. The update process comes
from the clustered index, and needs to update the index node, where as
the SELECT process has arrived from an index seek and is now going to the
data page.

It's possible that more shrewd indexing is possible, but without knowledge
of the application, I cannot tell.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]