0

Our production setup is that we have an application server with applications connecting to a SQL Server 2016 database. On the application server there is several IIS applications which run under a GMSA account. The GMSA account has db_datawriter and db_datareader privileges on the database.

Our team have db_datareader privileges on the same SQL Server database. We require this for production support purposes.

We recently had an incident where a team member invoked a query on SQL Server Management Studio on their local machine:

SELECT * FROM [DatabaseA].[dbo].[TableA] order by CreateDt desc;

TableAhas about 1.4m records and there are multiple blob type columns. CreateDt is a DATETIME2 type column.

We have RedGate SQL Monitor configured for the SQL Server Database Server. This raised a long-running query alert that ran for 1738 seconds.

At the same time one of our web applications (.NET 4.6) which exclusively inserts new records to TableA was experiencing constant query timeout errors:

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

These errors occurred for almost the exact same 1738 second period. This leads me to believe these are connected.

My understanding is that a SELECT query only creates a Shared lock and would not block access to this table for another connection. Is my understanding correct here?

My question is that is db_datareader safe for team members? Is there a lesser privilege that would allow reading data but absolutely no way for blocking behaviours to be created.

Ben
  • 2,518
  • 4
  • 18
  • 31
  • Does this answer your question? [Understanding SQL Server LOCKS on SELECT queries](https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries) – Ian Kemp May 11 '21 at 08:15
  • 1
    `This leads me to believe these are connected.` connected and thoroughly documented, with hundreds of articles, courses, books explaining why it happens and how to avoid this. It starts from *not* querying 1M rows without reason. When you read an entire table locks will escalate to the table level itself, blocking modifications. What are you trying to do? If you *really* need to read all those rows, you could use SNAPSHOT isolation. That has its own cost, as versions of modified rows are copied into `tempdb` until the reading transaction completes. A better design may be faster and cheaper – Panagiotis Kanavos May 11 '21 at 08:19
  • OK I understand that these are definitely connected so. The answer seems to indicate that the developer should compose their query is a particular way to avoid this scenario which is fine. However this doesn't prevent this scenario from occurring (e.g. by accident) is there a privilege level that would make this impossible? – Ben May 11 '21 at 08:23
  • Check [Resolve blocking problems caused by lock escalation in SQL Server](https://learn.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation#:~:text=Lock%20escalation%20is%20the%20process,when%20to%20do%20lock%20escalation.) for an explanation why locks escalate to the page or table level and how to avoid this. This isn't about privileges anyway, more about design. It's a bad idea to use the same table for operational and reporting purposes, precisely because reports read lots of data, blocking modifications – Panagiotis Kanavos May 11 '21 at 08:26
  • For example, in most applications there are different reporting and transactional tables/schemas, not only to reduce locking, but because the two scenarios require very different models. If you want to retrieve new/modified rows from a transactional table, you can use [Change Tracking](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15) to retrieve only the modified rows by PK. That's A LOT faster than `order by CreateDt desc;`. If changes are infrequent, `SNAPSHOT` isolation can help – Panagiotis Kanavos May 11 '21 at 08:30
  • OK I understand. You are right there are plenty of techniques to retrieve this data but there is effectively no way to prevent someone executing a similar query at a privilege level? If a user has db_datareader privileges, this is subsequently possible (even maliciously for argument's sake) to create a table lock and there is absolutely to prevent this? – Ben May 11 '21 at 08:38
  • BTW 1M rows is very little data for SQL Server. If you have performance issues you should find the cause and fix it, not try to prevent it with permissions. For really big scenarios though, eg when the same server is used for multiple heavy workloads, you can use [Resource Governor](https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15) to put a limit on the resources that can be used by separate workloads. – Panagiotis Kanavos May 11 '21 at 08:40
  • 3
    @Ben `to prevent this` don't run ad-hoc queries on production. Don't give people `datareader` for no reason. If people need to experiment or report, clone or replicate the data. Backup operations in SQL Server are online, ie they don't block applications. You can take backups as needed and restore them on a different server. Give developers `datareader` on that server, not the production database – Panagiotis Kanavos May 11 '21 at 08:42
  • @Ben Does an [index](https://learn.microsoft.com/sql/relational-databases/sql-server-index-design-guide) exist for the column? Otherwise sorting through millions of records may be slower. You should create one or more. Also you are able to increase the [timeout](https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option)... Maybe you can create a [view](https://learn.microsoft.com/sql/t-sql/statements/create-view-transact-sql) too, if suitable. –  May 11 '21 at 08:46
  • @PanagiotisKanavos if you want to put this as an answer I'll accept it. People seem to be fixating on the query and how it can be improved to work. This was not my question. I know this possible but my question was to establish Is there a lesser privilege that would allow reading data but absolutely no way for blocking behaviours to be created. Evidently there no way around this. – Ben May 11 '21 at 11:38
  • I think the wrong question is being asked here. This may be a better question for the Database Administrators community, because you are essentially asking a DBA question. What we do as DBA's is monitor, and administer and correct problems. Your agency monitored just fine. But your DBA did not administer in a timely manner. The user let the query run non-stop for 30 minutes. After the first 10 seconds they should have realized something was wrong and canceled the query. After the first couple of minutes the DBA should have detected the blocking issues and asked questions to the user. – Zorkolot May 12 '21 at 12:45

2 Answers2

0

The presence of SELECT * (SELECT STAR) in a query, leads generally to do not use an index and make a SCAN of the table. With many LOBs (BLOBs or CLOBS or NCLOBs) and many rows, the order by clause will take a long time to :

  1. generate the entries
  2. make a sort on CreateDt

So a read lock (shared lock) is put while reading all the data of the table. This lock accepts other shared locks but prohibit to put an exclusive lock to modify data (INSERT, UPDATE, DELETE). This may guarantee to other users that the data won't be modified.

This locking technics is well known as pessimistic lock. The locks are taken before beginning the execution of the query and relaxed at the end. So reader blocks writers and writers blocks all.

The other technic, that SQL Server can do, called optimistic locking, consists to use a copy of the data, without any locking and verify at the end of the execution that the data involved in writes has not been modified since the beginning. So the blocking is less...

To do a pessimistic locking you have the choise to allow or to force:

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;
SQLpro
  • 3,994
  • 1
  • 6
  • 14
0

In SQL Server, writers block readers, and readers block writers. This query doesn't have a where clause and will touch the entire table, probably starting with an IS (Intent Shared) and eventually escalating to a shared lock that updates/inserts/deletes can't access while the lock is there. This is likely held during that very long sort, the order by is causing.

It can be bypassed in several ways, but I don't assume you're actually after how, seeing as whoever ran the query was probably not really thinking straight anyway, and this is not a regular occurrence. Nevertheless, here are some ways to bypass:

  1. Read Committed Snapshot Isolation
  2. With (nolock). But only if you don't really care about the data that is retrieved, as it can return rows twice, rows that were never committed and skip rows altogether.
  3. Reducing the columns you return and reading from a non-clustered index instead.

But to answer your question, yes selects can block inserts.

Max Emanuelsson
  • 21
  • 2