There is a query:
SELECT entry_id FROM table_name WHERE (path_id = 7 AND value_string = N'00020003-0004-0005-0006-000700080009' )
The table in this query contains some millions of records. Both
value_string have their own non-clustered indexes.
When I use SQL Server Profiler to trace this query with
Lock:Acquired event, I see that there are three IS page locks taken: one on a page that contains this
value_string as '00020003-0004-0005-0006-000700080009', one on a page that contains
6 (which is staggering), and one on a page that contains
7 (which is expected).
The way I get information about pages: in
Lock:Acquired there is a field
TextData containing text like
1:87032; I take this and execute
DBCC PAGE ('mc33', 1, 87032, 3)
Then I get output like this:
The question is: why engine puts an IS lock on a page containing only sixes, if the query is only interested in sevens? This "excessive" locking causes us deadlocks when this query is executed in parallel with certain inserts, and I would like to know how I can get rid of such locking.