Results 1 to 3 of 3

Thread: How to check blocking queries in SQL Server Ask?

  1. #1
    Join Date
    Dec 2017
    Location
    hyderabad
    Posts
    2

    How to check blocking queries in SQL Server Ask?

    Hi,

    I have one stockroom server which got information/match up from heritage framework every minute of every day, I saw some of my reports/sql occupations execution is questionable and more often than not I got notification from DBA group that my inquiry is obstructing to other synchronize process.

    From DBA group I came to know summon i.e. Executive SP_WHO2 by which I can distinguish spid of question which cause hindering by investigating section BlkBy.

    If it's not too much trouble propose me how I can abstain from blocking and different approaches to check obstructing in SQL Server

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can run sp_who instead, sp_who2 creates a temp table that can cause blocking.

  3. #3
    Join Date
    Aug 2022
    Posts
    22
    In SQL Server, you can use the system function "sp_who2" to check for currently running or blocked queries. This function returns information about all active connections and sessions on the SQL Server instance. You can filter the results to show only blocked queries by adding a WHERE clause to the query, such as "WHERE BlkBy <> 0" which will return all rows where the BlkBy column is not equal to 0, indicating that the query is currently blocked.

    Another alternative is to use the Dynamic Management View (DMV) sys.dm_exec_requests which contains information about all requests that are currently executing within SQL Server. You can filter the results to show only blocked queries by adding a WHERE clause to the query, such as "WHERE blocking_session_id <> 0" which will return all rows where the blocking_session_id column is not equal to 0, indicating that the query is currently blocked.

    You can also use SQL Server Profiler to trace the events of your database, to trace all the running queries and also to trace the blocked queries.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •