Determine whether there is any blocking while deleting large number of records I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure.
I have clustered index on primary key and a non-clustered index on ID which is being used in where condition in Delete Query 

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted &gt; 0)
BEGIN
    DELETE top(100) FROM table WHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END


Is there any way to know whether there are any locks on this table 

indexes:

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO



 Alter NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( &lt;list_all_other_columns_returned&gt; );
GO


CREATE TABLE [dbo].[ActCost](
    [ActCostID] [int] IDENTITY(1,1) NOT NULL,
    [ActID] [int] NOT NULL,
    [ActCostTypeID] [int] NOT NULL,
    [Description] [nvarchar](200) NOT NULL,
    [Cost] [float] NOT NULL,
    [CostPerProductUnit] [float] NOT NULL,
    [CostPerEndProductUnit] [float] NOT NULL,
    [OtherValue] [float] NULL,
    [OtherID] [int] NULL,
    [Comment1] [nvarchar](200) NULL,
    [Comment2] [nvarchar](200) NULL,
    [OPerProductUnit] [float] NULL,
    [OPerHour] [float] NULL,
    [OCostPerUnit] [float] NULL,
    [OCostPerHour] [float] NULL,
    [PerfEnh_ProcessID] [int] NOT NULL,
    [PerfEnh_MillID] [int] NOT NULL,
    [ScenarioID] [int] NOT NULL,
 CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO


Azure Space :
Available 2 GB
Used :500MB

I have another database with same data but with more Available Size which works fine i mean it takes just 20-30 seconds to do the same job , Is it going to be a factor ?



Blocking inspection :
The title says blocking, but the question says locking. I'll deal with the former since the latter should be a given: yes, locks need to be taken in order to perform a delete.

To check for blocking, in the window where you're performing the deletes:

SELECT @@SPID; -- make note of this


In another window:

SELECT blocking_session_id, wait_type
  FROM sys.dm_exec_requests
  WHERE session_id = &lt;spid from above&gt;;


I know this DMV was available in the last two SQL Database refreshes, but not quite sure what version you're on.

Now, if you get a blocker, check what they are doing.

SELECT r.[status], r.command, r.blocking_session_id, 
    r.wait_type, query = SUBSTRING(CONVERT(NVARCHAR(MAX), 
    t.[text]), r.statement_start_offset/2, 
    COALESCE(NULLIF(r.statement_end_offset,-1), 
    LEN(CONVERT(NVARCHAR(MAX),t.[text]))*2)/2)
  FROM sys.dm_exec_requests AS r
  OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
  WHERE r.session_id = &lt;blocking session&gt;;


You can't run DBCC INPUTBUFFER in this case because in SQL Database this is somewhat protected - you can only run this against your own @@SPID.

It might also be that log file autogrowth is the problem - if the clustered index delete here actually has to go and delete rows from many indexes and/or indexed views, the problem could be a log bottleneck. My usual go-to here is the default trace, but I don't believe you have access to that in Azure. Thankfully in your scenario we can see that this is the case due to the wait type shown - your query is actually being slowed down intentionally by the system because you're generating too much log activity. I'm not sure exactly how this algorithm works, but if it is happening when you delete 100 rows, it may be time to migrate to an instance with better "guaranteed" performance (which may mean paying more). Simply spinning up a new instance on the same plan may even help, as you'll likely end up on newer generation hardware which should naturally perform log operations quicker and may help you avoid hitting the governor.

You should also be committing transactions at some level of frequency within the loop. Otherwise chunking doesn't really help because you're likely still holding the locks on the first 100 rows while you're trying to delete the last 100 rows. Committing smaller chunks more frequently will have less impact on the log and potentially on locking/blocking too.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted &gt; 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;


You can also put a waitfor in the loop - maybe pausing long enough will prevent tripping the log rate governor.