1

Our application performs these two queries:

select   A.* from   LETTUREAPERTE A
where IDAZIENDAOPERATORE=3

and

select   A.* from   LETTUREAPERTE A
where IDAZIENDAOPERATORE=2

according to the ID of the company a user is considering. Well, while the second query is correctly executed, the first blocks and never executes. In LETTUREAPERTE tables there are less than 400 records, some with IDAZIENDAOPERATORE with 2, some other with 3.

I don't know why this is happening and why the first query blocks... I get this error in the end I get an error saying that process was chosen as a deadlock victim.

Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I even ran some queries to detect if there are some update locks on some records of that table but there's none. And so it must be because in the whole project we've never used UPDLOCK in queries...

SagittariusA
  • 5,289
  • 15
  • 73
  • 127
  • 3
    Add `with option (recompile)` to see if you get a better execution plan. – Gordon Linoff Jan 26 '17 at 14:38
  • Ehm, where should I add `with option (recompile)`? What's that statement? – SagittariusA Jan 26 '17 at 14:40
  • 1
    `select A.* from LETTUREAPERTE A where IDAZIENDAOPERATORE=2 with option(recompile)`..but i don't think this can effect table with 400 records.Can you post deadlock graph – TheGameiswar Jan 26 '17 at 14:41
  • I get the same result...it takes sometimes 80 seconds to execute. Some other times it's aborted as I said before... – SagittariusA Jan 26 '17 at 14:43
  • you could paste the execution plan here to share it if you want: https://www.brentozar.com/pastetheplan/ – Tanner Jan 26 '17 at 14:45
  • @Tanner: this is my execution plan if you want to have a look, even if I don't know what you are talking about: https://www.brentozar.com/pastetheplan/?id=ryXnL5vve – SagittariusA Jan 26 '17 at 15:33
  • @LoryLory so LETTUREAPERTE is a view? Can you share it's definition? There's some massive read counts in that plan. – Gareth Lyons Jan 26 '17 at 15:46
  • yes, it's a very overused view... now I'm performing a `UPDATE STATISTICS [dbo].[TABLE] WITH FULLSCAN` on each table taking part into the view...from what do you understand there's a massive read? – SagittariusA Jan 26 '17 at 15:56
  • what do you mean for definition, its query? – SagittariusA Jan 26 '17 at 15:57
  • @LoryLory yes the query thanks (as in the select part of "create view as select ......"). You can see the number of reads (the value "actual number of rows") by hovering over each box on the plan - there's 11m rows being read from a couple of tables. I also can't see a predicate based on IDAZIENDAOPERATORE = something anywhere, so I'm thinking it's a derived value. – Gareth Lyons Jan 26 '17 at 16:55

4 Answers4

1

One possibility is an uncommitted/unrolled back transaction on one of the rows with a 3.

If using transactions you need to use a TRY/CATCH and either commit or rollback.

You could try using (NOLOCK):

select   A.* from   LETTUREAPERTE A (NOLOCK)
where IDAZIENDAOPERATORE=3

Another options is to restart SQL server to see if that clears the issue, but it is likly to reoccure

apc
  • 5,306
  • 1
  • 17
  • 26
  • Even if I use `(NOLOCK)` in the query I get the same result... I don't know if there are blocked transaction which should be deleted. Is there a way to get this? – SagittariusA Jan 26 '17 at 14:45
  • 1
    http://stackoverflow.com/questions/4449719/is-there-a-way-to-list-open-transactions-on-sql-server-2000-database – apc Jan 26 '17 at 15:26
  • Thank you for your help. I had already tried that query and I had checked there was no open transaction... – SagittariusA Jan 26 '17 at 15:28
1

as Gordon suggest in the comment add option recompile like the below 

select   A.* from   LETTUREAPERTE A
where IDAZIENDAOPERATORE=2 OPTION (RECOMPILE)
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • I get the same result...it takes sometimes 80 seconds to execute. Some other times it's aborted as I said before... – SagittariusA Jan 26 '17 at 14:43
  • you missed the `with` key word before `option`, not sure it's **option**al is it?! – Tanner Jan 26 '17 at 14:46
  • since 10 minutes ago I haven't got any error anymore...it just takes really too much time to execute. Always around 70/80 seconds and everyone is complaing with me that the application is slow... – SagittariusA Jan 26 '17 at 14:47
  • @Tanner its optional as you mentioned – Moudiz Jan 26 '17 at 14:48
  • Can you share the execution plan? – Bartosz X Jan 26 '17 at 14:52
  • @BartoszX: execution plan? – SagittariusA Jan 26 '17 at 14:59
  • @BartoszX I've tried to include an execution plan. For the moment this is what I get sometimes: "Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." – SagittariusA Jan 26 '17 at 15:23
  • @BartoszX this is my execution plan if you want to have a look, even if I don't know what you are talking about: https://www.brentozar.com/pastetheplan/?id=ryXnL5vve – SagittariusA Jan 26 '17 at 15:34
  • @LoryLory try this http://dba.stackexchange.com/questions/78683/detecting-the-locked-table-or-row-in-sql-server – Moudiz Jan 26 '17 at 15:37
  • @Moudiz: thank you too but nothing blocked is detected. – SagittariusA Jan 26 '17 at 15:41
  • @LoryLory you should be running the query then run the select in the link ,. to catch a lock a query should be executed , this url i dont know if it might help you to save a deadlock https://msdn.microsoft.com/en-us/library/ms190465.aspx, you are saying that this table doesnt contain much data and when you run it it takes alot of time to be execute this means there is a lock. unless i am understanding the issue wrong – Moudiz Jan 26 '17 at 15:53
1

Try to run sp_WhoIsActive by Adam Mechanic and track the transaction which can possibly use the same table source. After that find this object in sp_lock (system one). Based on that you should get some idea why do you have that deadlock. Possibly the value 2 is not used by the same (locked) index during the execution as the value 3 - which is possible when using filtered indexes on a table.

Bartosz X
  • 2,620
  • 24
  • 36
  • Forgive me, I cannot understand from documentation how to use these procedures...If I run `exec sp_WhoIsActive` it says such a procedure does not exists. I know almost anything about these stores procedures... – SagittariusA Jan 26 '17 at 14:56
  • 1
    [link](http://sqlblog.com/blogs/adam_machanic/) - You will find everything you need on the authors blog. In basics - just execute it without any parameters at all and this will show you all of the active transactions on your SQL Server instance along with their session IDs. Based on that ID you can execute the 'sp_lock' [link](https://msdn.microsoft.com/en-us/library/ms187749.aspx) to see which object are affected (basically to see are you trying to access an object which is used by another process at the moment. – Bartosz X Jan 26 '17 at 15:02
  • I will have a look... I don't know if I am allowed to download and install external libraries as last week we got a crypto locker in our second production server...I will ask to my development team leader – SagittariusA Jan 26 '17 at 15:03
  • This is one of the most commonly used and approved by DBAs ;-) if not try at least the built in 'sp_who2' - this one will show you everything (apart from the query itself) and you have to filter it on your own as this includes system transactions as well. – Bartosz X Jan 26 '17 at 15:07
  • I've just run the query linked here (http://www.sqlservergeeks.com/sys-dm_tran_active_transactions/) and I've noticed there are 6 Read/Only active transaction which began 6 hours ago...should I close them? – SagittariusA Jan 26 '17 at 15:10
  • If you are the only person who use this environment then yes, go for it. But if this is a live production then maybe try to speak to your DBA before ;-) – Bartosz X Jan 26 '17 at 15:13
  • These old transaction are called `worktables`. If I understand well they are system transaction so...noithing useful. Apparently there's nothing blocking anything from being read... so it's a mistery why it's so slow. – SagittariusA Jan 26 '17 at 15:19
  • this is my execution plan if you want to have a look, even if I don't know what you are talking about: https://www.brentozar.com/pastetheplan/?id=ryXnL5vve – SagittariusA Jan 26 '17 at 15:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134092/discussion-between-bartosz-x-and-lory-lory). – Bartosz X Jan 26 '17 at 15:34
  • @ Bartosz X, thank you mate for your precious help. Things seem to work better now. If you want, have a look to my updated answer to see what exactly I did...I also added a maintenance plan to update statistics weekly. Really thank you, you were very kind to me. – SagittariusA Jan 27 '17 at 09:29
1

As ** Bartosz X** suggested to my, I launched the following command for each table taking part in the view:

UPDATE STATISTICS [Schema].[Table_Name] WITH FULLSCAN

It took about an hour to finish but things seem to have improved considerably. So, I added the following maintencance plan to perform weekly:

enter image description here

If interested, this is the query of my view:

SELECT        
  IDOPERATORE, 
  COGNOMENOMEOPERATORE, 
  IDAZIENDAOPERATORE,
  (SELECT
     SUM(LETTURERIMASTE) AS Expr1
   FROM dbo.LETTURERIMASTE AS B
   WHERE (IDLOTTOLETTURISTA IN
           (SELECT IDLOTTOLETTURISTA
            FROM dbo.LOTTILETTURISTA AS C
            WHERE (DATAFINELOTTOLETTURISTA >= CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1))) AND (IDLETTURISTALOTTOLETTURISTA = A.IDOPERATORE)))) 
   AS LETTURERIMASTE
SagittariusA
  • 5,289
  • 15
  • 73
  • 127