May 21, 2013 at 9:01 am
Our application is using nHibernate as our ORM, and during some small load tests we've run I am noticing that the load test script is bogging down badly due to long waits and blocking. It mostly is affecting two tables (Alert and VehicleOfInterest - see create scripts below). These are the two queries. The INSERT into alert is grabbing a MISCELLANEOUS lock and is blocking the second statement below (a SELECT grabbing a shared lock):
INSERT INTO [Alert] (ModifiedDate, Type, CreatedDate, CreatedBy, SalespersonName, DupeDetectionRuleCriteria, OpportunityType, CustomerMatches, RemainingUsers, OrganizationId, AssignedToId, CustomerId, OpportunityId, SalesLeadId, VehicleOfInterestId, ActionId, NoteId, WantListId, ServiceLeadId, PartsLeadId, VehicleId, CompanyId, OrganizationTemplateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22); select SCOPE_IDENTITY()
(@p0 bigint)select vehicleofi0_.Id as Id252_, vehicleofi0_.ModifiedDate as Modified2_252_, vehicleofi0_.Year as Year252_, vehicleofi0_.IsPrimary as IsPrimary252_, vehicleofi0_.Make as Make252_, vehicleofi0_.Model as Model252_, vehicleofi0_.Source as Source252_, vehicleofi0_.Vin as Vin252_, vehicleofi0_.Status as Status252_, vehicleofi0_.Type as Type252_, vehicleofi0_.OrganizationId as Organiz11_252_, vehicleofi0_.VehicleId as VehicleId252_, vehicleofi0_.SalesLeadId as SalesLe13_252_ from [VehicleOfInterest] vehicleofi0_ inner join [SalesLead] saleslead1_ on vehicleofi0_.SalesLeadId=saleslead1_.Id where saleslead1_.IsActive=1 and (saleslead1_.ClosedDate is null) and vehicleofi0_.VehicleId=@p0
As a DBA, I have concerns with how nHibernate is hydrating the alert object with an Id (SCOPE_IDENTITY). However, it seems this is an extremely common method for populating an Id into a model. I've voiced my concerns with using SCOPE_IDENTITY, and we've found some other better options (high-low algorithm for instance), but these cannot be deployed immediately, as they are large refactors. I've created all necessary indexes, and the blocking is still prominent at low load on the hardware.
Create Statement For Alert:
CREATE TABLE [dbo].[Alert]
(
[Id] [bigint] NOT NULL IDENTITY(1, 1),
[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SalespersonName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DupeDetectionRuleCriteria] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OpportunityType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerMatches] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrganizationId] [bigint] NOT NULL,
[AssignedToId] [bigint] NOT NULL,
[CustomerId] [bigint] NULL,
[OpportunityId] [bigint] NULL,
[SalesLeadId] [bigint] NULL,
[VehicleOfInterestId] [bigint] NULL,
[ActionId] [bigint] NULL,
[NoteId] [bigint] NULL,
[WantListId] [bigint] NULL,
[ServiceLeadId] [bigint] NULL,
[VehicleId] [bigint] NULL,
[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_Alert_ModifiedDate] DEFAULT (getutcdate()),
[CompanyId] [bigint] NULL,
[RemainingUsers] [int] NULL,
[OrganizationTemplateId] [bigint] NULL,
[PartsLeadId] [bigint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [PK__Alert__3214EC071BFD2C07] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Alert_OrganizationId_AssignedToId_Type] ON [dbo].[Alert] ([OrganizationId], [AssignedToId], [Type]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Alert_AssignedToId] ON [dbo].[Alert] ([AssignedToId]) INCLUDE ([OrganizationId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Alert_ActionId] ON [dbo].[Alert] ([ActionId]) INCLUDE ([Id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Alert_CustomerId] ON [dbo].[Alert] ([CustomerId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [OpportunityId], [SalesLeadId], [VehicleOfInterestId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Alert_VehicleOfInterestId] ON [dbo].[Alert] ([VehicleOfInterestId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [CustomerId], [OpportunityId], [SalesLeadId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId], [PartsLeadId]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Action_ActionId_OrganizationEntityBaseId] FOREIGN KEY ([ActionId]) REFERENCES [dbo].[Action] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Company_CompanyId_OrganizationEntityBaseId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Customer_CustomerId_OrganizationEntityBaseId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Note_NoteId_OrganizationEntityBaseId] FOREIGN KEY ([NoteId]) REFERENCES [dbo].[Note] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Opportunity_OpportunityId_OrganizationEntityBaseId] FOREIGN KEY ([OpportunityId]) REFERENCES [dbo].[Opportunity] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_OrganizationTemplate_OrganizationTemplateId_OrganizationEntityBaseId] FOREIGN KEY ([OrganizationTemplateId]) REFERENCES [dbo].[OrganizationTemplate] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_PartsLead_PartsLeadId_LeadId] FOREIGN KEY ([PartsLeadId]) REFERENCES [dbo].[PartsLead] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Person_AssignedToId_OrganizationEntityBaseId] FOREIGN KEY ([AssignedToId]) REFERENCES [dbo].[Person] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_ServiceLead_ServiceLeadId_LeadId] FOREIGN KEY ([ServiceLeadId]) REFERENCES [dbo].[ServiceLead] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])
GO
ALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_VehicleOfInterest_VehicleOfInterestId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleOfInterestId]) REFERENCES [dbo].[VehicleOfInterest] ([Id])
GO
ALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_WantList_WantListId_OrganizationEntityBaseId] FOREIGN KEY ([WantListId]) REFERENCES [dbo].[WantList] ([Id])
GO
Create Script for Voi:
CREATE TABLE [dbo].[VehicleOfInterest]
(
[Id] [bigint] NOT NULL IDENTITY(1, 1),
[Year] [int] NULL,
[IsPrimary] [bit] NOT NULL,
[Make] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Model] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Source] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrganizationId] [bigint] NOT NULL,
[VehicleId] [bigint] NULL,
[SalesLeadId] [bigint] NOT NULL,
[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_VehicleOfInterest_ModifiedDate] DEFAULT (getutcdate()),
[Vin] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [PK__VehicleO__3214EC07592635D8] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_IsPrimary] ON [dbo].[VehicleOfInterest] ([IsPrimary]) INCLUDE ([Year], [Make], [Model], [VehicleId], [SalesLeadId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_SalesLeadId,IsPrimary_VehicleId] ON [dbo].[VehicleOfInterest] ([SalesLeadId], [IsPrimary], [VehicleId]) INCLUDE ([Year], [Make], [Model]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_VehicleId] ON [dbo].[VehicleOfInterest] ([VehicleId]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])
GO
ALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])
GO
ALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])
GO
ALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])
GO
Does anybody see any glaring issues here?
June 27, 2013 at 7:41 am
bump on this issue.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply