mercredi 31 décembre 2014

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 > 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 ( <list_all_other_columns_returned> );
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 ?


enter image description here


Blocking inspection : enter image description here





Aucun commentaire:

Enregistrer un commentaire