3

I have two SQL select queries executed on SQL Server 2008 R2 without explicit transactions

Query 1:

SELECT * 
FROM MyTable 
WHERE Field1 = 'XXX' AND Field2 = 'YYY'

Query 2:

SELECT * 
FROM MyTable 
WHERE Field1 = N'XXX' AND Field2 = N'YYY'

"Query 2" is faster than "Query 1", but while "Query 2" is running I can't write (INSERT) new records on "MyTable"..."MyTable" seems locked. Instead, while "Query 1" is running I can add new records quietly.

Note: "MyTable" has a clustered composite key (two columns) and other INDEXES.

Can you explain why this behavior?

Thanks.

Luca Petrini
  • 1,695
  • 2
  • 28
  • 53
  • 1
    What is your default isolation-level? `DBCC useroptions;` – Shaneis Apr 22 '16 at 15:36
  • It's "Read Committed" – Luca Petrini Apr 22 '16 at 15:39
  • Does query 1 and query 2 return the same amount of rows? – domenicr Apr 22 '16 at 19:40
  • Yes...are the same query, change only the way of parameters assignment. – Luca Petrini Apr 23 '16 at 22:06
  • @Luca Petrini, did you find an explaination? – Ruslan K. Apr 25 '16 at 07:18
  • @RuslanK. I'm investigating...the cause is the presence of indexes. If I delete them I no longer have the problem, but I have a deterioration in performance. I also tested the solution posted by Pouria Sharif, but still I did not get results. – Luca Petrini Apr 26 '16 at 07:59
  • Have you tried to analyze the locks using `sp_lock2`? – Ruslan K. Apr 26 '16 at 08:53
  • At the end, I worked around the problem by reading data by one "Read Uncommitted" transaction (in my case should not be a problem). Anyway, the problem occurred with the presence of one specific index (note: the table has more than 10 million records); removing that index changes the execution plan and the insert is not blocked (but the query appeared much slower). – Luca Petrini Apr 28 '16 at 10:20

1 Answers1

1

Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:

ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);
Pouria Sharif
  • 156
  • 1
  • 15
  • I tested your solution but without any result...anyhow the cause is the presence of indexes...if I delete them I no longer have the problem, but I have a deterioration in performance. I'm investigating. – Luca Petrini Apr 26 '16 at 08:07