How to know when a SQL user query is blocking other transaction in DB

Rishabh Patil 121 Reputation points

Hi All,

I have been thinking to ask this question for a some time.

I have a highly transaction production database in which we often asked to give read only/select access to the whole database (tables,views,Sproc's) to a SQL login user so that the different team can leverage the data for the automation, DW, Data marts etc.

When we create a login with db_datareader, It happens that the people are not much familiar with the blocking so, they try to run select * queries from their projects without nolock on the tables. Which causes the other transactions stuck for a while and we have to kill the transaction in between.

Any help please @Erland Sommarskog

I don't know how to deal with this situations and it would be grateful if someone shares knowledge on it. Looking to hear the answer to the below questions.

1] What's the best way to give read only access to the SQL user which could prevent harm on the Db at first place.
2] How to know the suspicious query running by any such logins ?
3] Any automated way to get notification proactively?

Any help must be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,523 questions
No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 67,821 Reputation points Microsoft MVP

    A start could be to consider setting the database in READ_COMMITTED_SNAPSHOT, so that readers and writers cannot block each other. Now, while this change is often beneficial, there are situations where RCSI can lead to update anomalies, so you need to understand it.

    And it is obviously only a partial solution, since the query still could take up a lot of resources.

    The solution to that: don't let those users in in the first place. Give them a different database. This can be achieved in a multitude of ways:

    • Database snapshots.
    • Readable secondaries.
    • Log shipping.
    • Transactional replication.
    • Or just restore the most recent full backup.

    All depending on the actual needs of these users.

    No comments

  2. Rishabh Patil 121 Reputation points

    Hi @Erland Sommarskog , thanks for the reply

    I will check about the first two options.

    No comments

  3. Tom Phillips 17,511 Reputation points

    In your situation, you would likely be better off creating a "read-only" replica and giving them access to that, so it does not interfere with your OLTP application.

  4. Tom Phillips 17,511 Reputation points

    I have used this to email and/or automatically kill long blocking processes. Create a SQL Agent job to run this proc every 2 min.

    CREATE PROC [dbo].[usp_EmailLongQueries]
    -- Make sure dbmail is started - sometimes it stops unexpectedly
    EXEC msdb..sysmail_start_sp
    DECLARE @emaildba varchar(255), @subject varchar(256), @body varchar(max), @killcmd INT
    SET @emaildba = ''
    DECLARE @servername varchar(256), @loginid varchar(256), @ntdomain varchar(100), @ntusername varchar(100),
     @hostname varchar(255), @hostproc varchar(10), @spid varchar(10), @execcont varchar(10), @waittype varchar(256),
     @waitresource varchar(255), @waittime varchar(10), @blockedby varchar(10), @starttime datetime, @runtimesecs varchar(10),
     @runtimemin varchar(10), @status varchar(20), @dbname varchar(255), @commandtype varchar(25), @sqllen int, @sqlcmd varchar(max), 
     @progname varchar(255), @blkcnt int
    DECLARE longquery CURSOR FOR
     @@SERVERNAME as ServerName,
     sproc.loginame AS LoginID,
     sproc.nt_domain AS NTDomain,
     sproc.nt_username AS NTUserName,
     sproc.Hostname AS HostName,
     sproc.spid Session_ID,
     sproc.ecid Execution_Context,
     sproc.lastwaittype AS Wait_Type, 
     sproc.waitresource AS Wait_Resource,
     sproc.waittime AS Wait_Time, 
     CASE WHEN sproc.blocked = 0 THEN 0 ELSE sproc.blocked END as BlockedBy,
     sproc.last_batch Started_At,
     datediff(second,sproc.last_batch,getdate()) Elapsed_Seconds,
     datediff(mi,sproc.last_batch,getdate()) Elapsed_Mins,
     sproc.status Status, 
     DB_NAME(sproc.dbid) AS DBName,
     sproc.cmd Command,
     len(sqltext.TEXT) SQL_Length,
     ISNULL(SUBSTRING(sqltext.text, (sproc.stmt_start/2)+1, 
            ((CASE sproc.stmt_end
              WHEN -1 THEN DATALENGTH(sqltext.text)
              WHEN 0 THEN DATALENGTH(sqltext.text)
             ELSE sproc.stmt_end
             END - sproc.stmt_start)/2) + 1),'') AS Query_SQL,
      blockingCnt = (SELECT COUNT(*) FROM master.sys.sysprocesses blk WHERE blk.blocked <> 0 AND blk.blocked = sproc.spid)
     from master.sys.sysprocesses sproc
     OUTER APPLY master.sys.dm_exec_sql_text(sproc.sql_handle) AS sqltext
     where sproc.spid <> @@SPID
     AND sproc.spid > 50
     AND sproc.ecid = 0
     AND sproc.cmd <> 'AWAITING COMMAND'
     AND sproc.loginame <> 'sa'
     AND sproc.loginame NOT IN ('domain\serviceaccount','NT AUTHORITY\SYSTEM')
     AND DB_NAME(sproc.dbid) = 'databasenamehere'     -- Specific database
     AND datediff(mi,sproc.last_batch,getdate()) > 5  -- Running longer than x mins
     ORDER BY sproc.spid, sproc.ecid
    OPEN longquery
    FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
     @hostname, @hostproc, @spid, @execcont, @waittype,
     @waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
     @runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt
     SET @killcmd = 0
     -- Kill specific select statement running > X mins
     IF @dbname='abc' 
     AND (@LoginID = 'specificlogin' OR @LoginID = 'specificlogin' OR @LoginID = 'specificlogin') 
     AND @commandtype = 'SELECT' AND @runtimemin > 10
     SET @killcmd = 1
     IF @killcmd = 1
     SET @subject = 'KILLED: ' + @servername + ' SPID: ' + @spid + ' process running for ' + @runtimemin + ' Mins'
     DECLARE @sql varchar(1000)
     SET @sql = 'KILL ' + @spid + ';'
     EXEC (@sql)
     SET @subject = CASE WHEN @blkcnt > 0 THEN 'Blocking: ' WHEN @blockedby <> 0 THEN 'Blocked: ' ELSE 'Warning: ' END + @servername + ' SPID: ' + @spid + ' Runtime: ' + @runtimemin + ' Mins'
     SET @body = 'Server: ' + @servername + CHAR(10) 
     SET @body = @body + 'SPID: ' + @spid + CHAR(10) 
     SET @body = @body + 'Start Time: ' + CONVERT(varchar(25), @starttime, 100) + '  Running for: ' + @runtimemin + ' Mins' + CHAR(10) 
     SET @body = @body + 'Username: ' + @loginid + CHAR(10) 
     SET @body = @body + 'Database: ' + @dbname + CHAR(10) 
     SET @body = @body + 'Wait: ' + RTRIM(@waittype) + ' ' + @waittime + 'ms ' + CASE WHEN @blockedby <> 0 THEN 'Blocked By SPID: ' + @blockedby ELSE '' END+ CHAR(10) 
     SET @body = @body + 'From Host: ' + RTRIM(@hostname) + ' PID: ' + @hostproc + CHAR(10) 
     SET @body = @body + 'Program Name: ' + RTRIM(@progname) + CHAR(10)
     SET @body = @body + CHAR(10) + 'Query: ' + LEFT(@sqlcmd,1000) + CASE WHEN LEN(@sqlcmd)>1000 THEN ' ...more...' ELSE '' END + CHAR(10) 
     SET @body = @body + CHAR(10) + CHAR(10) + '******* THIS IS AN UNMONITORED MAILBOX.  PLEASE DO NOT REPLY TO THIS EMAIL ******* ' + CHAR(10) 
     --PRINT @subject
     --PRINT @body
     -- Send email
     EXEC msdb.dbo.sp_send_dbmail @recipients = @emaildba, @subject=@subject, @body=@body, @body_format = 'TEXT'
     FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
     @hostname, @hostproc, @spid, @execcont, @waittype,
     @waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
     @runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt
    CLOSE longquery
    DEALLOCATE longquery