If a SQL database becomes unresponsive, it’s possible that it’s either busy or has locking issues.

By default, executing a SELECT statement will lock the tables used by it. This lock prevents other statements from making changes to these tables until the execution ends. For instance, one cannot drop tables, or add/remove rows from tables involved in the executing SELECT statement. Most locks will go away after the SELECT ends. However, some locks might not go away depending on how the SQL statements are written and what the lock conflict is about. For instance, executing a drop of a table locked by a SELECT might lock the drop process - the only way to unlock it is by killing the process or restarting SQL.

How to see if SQL is Locked

If a SQL database becomes unresponsive, it’s possible that it’s either busy or has locking issues.

If your SQL user has sysadmin or VIEW SERVER STATE rights, connect to your SQL instance via SSMS, right-click the SQL instance name → Activity Monitor → if the Overview bar shows (Paused), right-click on it and select Resume -> expand the Processes pane and you'll likely see rows of processes with information changing every few seconds. A row with Wait Type = LCK_M* indicates that this process is locked. See below for 3 examples. Sometimes, these locks are normal and will go away once the resources are unlocked. A locked row with a very large Wait Time (ms) might require a manual unlock (see next section).

SSMS Activity Monitor - Processes Panel

How to Unlock SQL

Please try the approaches below in this order:

  1. Terminate (kill) the reporting tools that are querying the data warehouse data (ex.: refreshing Tableau extracts, SSM queries, refreshing Power BI datasets, Excel reports).

  2. Terminate your ETL+ running processes loading into the data warehouse.

  3. On the Processes Panel above, right-click a row with Wait Type = LCK_M* -> Kill Process. Repeat this for all locked processes. If you have multiple databases on this list and only one with locking issues, use the panel’s Database header’s droplist to select the database that’s locked.

  4. Caution: If you have access and sysadmin rights to the SQL instance, right-click the instance name → Restart and follow the wizard. This will reset all SQL connections and processes currently running.

Tips to Permanently Avoid SQL Locks

  • Schedule your table changes to avoid conflicting with executing SELECTs (especially the ones that take a long time to complete). Examples of techniques:

    • Instead of using reporting tools to query complex SELECT statements, load the statements into SQL tables via ETL+.

    • Use ETL+ Jobs to run reporting tool queries such as refreshing Tableau data sources, or Power BI data sets, or Excel SELECTs after the feeding tables have been refreshed.

    • If you’re running queries manually, avoid doing so when their tables are being modified.

  • Consider adding UNLOCK or READUNCOMMITTED table hints to your SELECT statements or querying tools. Keep in mind that adding these table hints might affect the results of SELECT queries if their tables’ data changes during a SELECT execution. For more, search the internet for Dirty Reads and Phantom Reads with SQL Server. The following are examples of table hints:

    • SELECT * FROM TableName1 WITH (UNLOCK)

    • SELECT * FROM TableName1 WITH (READUNCOMMITTED)

    • SSMS: Go to menu Tools → Options → Query Execution → SQL Server → Advanced → select the Transaction isolation level READ UNCOMMITTED.

    • Tableau: When configuring the connection to a MS SQL Server data source, check the box for “Read uncommitted data”.

    • Power BI: As noted above, manually add WITH (UNLOCK) to datasets' SELECT statements.

    • Excel: It seems that there’s no way to configure this in Excel. Instead, add WITH (UNLOCK) at the MS SQL Server level (such as using a view to query the data and add the table hint).

More documentation about this topic:
https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16
https://www.sqlshack.com/locking-sql-server/

* Wait Types for locks: LCK_M_IS LCK_M_IU LCK_M_IX LCK_M_RIn_NL LCK_M_RIn_S LCK_M_RIn_U LCK_M_RIn_X LCK_M_RS_S LCK_M_RS_U LCK_M_RX_S LCK_M_RX_U LCK_M_RX_X LCK_M_S LCK_M_SCH_M LCK_M_SCH_S LCK_M_SIU LCK_M_SIX LCK_M_U LCK_M_UIX LCK_M_X

Keywords: dirty read, SQL Server dirty read concurrency problem, Transaction Isolation Levels,