locked
ENCRYPTION_SCAN Lock without TDE enabled. RRS feed

  • Question

  • We are getting a lock that cause a lot of issues on our database, and the Resource subtype is a ENCRYPTION_SCAN, this kind of subtype is when the database have TDE.

    

    I execute this query with no results, so we don't have TDE enabled. 

    select * from sys.databases
    where is_encrypted = 1

    I hope you can help me. 

    Thank you. 

    Friday, June 5, 2015 10:11 PM

All replies

  • I see that its returning that value for tempdb database. What is the SQL version?

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, June 6, 2015 12:32 AM
  • I have frequently seen this resource subtype when monitoring the situation at our customer sites, and none of them use TDE to my knowing. I have not paid much attention, but assumed that it something that just is there.

    You say that this lock causes a lot of issues. Could you detail how you have arrived at this conclusion?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 6, 2015 8:03 AM
  • The version is SQL Server 2008 R2 SP3.

    Monday, June 8, 2015 9:51 PM
  • At the moment when this event raises, we start having performance problems, the query never ends so it stay during a long time in the runnable status or running status. Right after that, the sytem user start to report slow problems, we kill the spid and the problem is eliminated. 

    We captured this Screenshots, also shows a open transaction on tempdb whit a sort_init. 

     

    Thank for your answer.

    Tuesday, June 9, 2015 9:56 PM
  • More likely the query that spid 862 is running has a query plan which takes a lot of resources. The ENCRYPTION_SCAN as such is not likely to be the problem, rather the issue is in the query plan. It could be that the query is truly a rogue query that should not run at all. It could also be that statistics are out of date or indexes are missing.

    I note that there a lot of queries that starts off SELECT T1.CONFLICT_ID, CONVERT... Maybe query 862 is running that query too, but it is formatted differently. Since cache lookup is on a hash of the raw query text, this means that spid 862 has a different cache entry from the rest.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 10, 2015 7:28 AM