January 22, 2010 at 3:30 am
We save real time statistic from a website in one table (50000 and more each day). The Server is right now SQL Server Express 2008, but we would have the option to upgrade to the SQL Server 2008 Web Edition. We save the statistics in tables for each year. Since the INSERT performance began to start being poor after the table grows over 200.000 or so we decided to save the data in an interim table first and use a trigger to move that in a table for each year after that.
Now we get following transaction errors with the trigger:
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We tried to use the insert and the delete statement of the trigger with ROWLOCK, but this didn’t have any effect.
What can we try to solve these problems? What can we do to INSERT in large tables like this without the serious performance problems we got?
USE [DATABASENAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Statistik2010](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Old] [int] NULL,
[Id_Statistik2] [int] NULL,
[SessionID] [nvarchar](500) NULL,
[NewSession] [bit] NULL,
[ClientIP] [nvarchar](50) NULL,
[UserName] [nvarchar](500) NULL,
[Hostname] [nvarchar](500) NULL,
[Referrer] [nvarchar](500) NULL,
[ReferrerQuery] [nvarchar](max) NULL,
[theRequest] [nvarchar](500) NULL,
[Suchbegriffe] [nvarchar](max) NULL,
[FilePath] [nvarchar](500) NULL,
[Language] [nvarchar](50) NULL,
[theAccessedUri] [nvarchar](500) NULL,
[theBrowser] [nvarchar](500) NULL,
[theReferer] [nvarchar](500) NULL,
[theStatus] [nvarchar](2500) NULL,
[Browser] [nvarchar](500) NULL,
[BrowserMajor] [nvarchar](500) NULL,
[Javascript] [bit] NULL,
[JavaApplets] [bit] NULL,
[Cookies] [bit] NULL,
[Platform] [nvarchar](50) NULL,
[ScreenPixels] [nvarchar](50) NULL,
[DateTime] [datetime] NULL,
[Date] [datetime] NULL,
[Week] [int] NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[PageVisitedNumber] [int] NULL,
[TimeOnWebsite] [datetime] NULL,
[UniqueID] [nvarchar](500) NULL,
[NumberOfVisits] [int] NULL,
[OriginalReferer] [nvarchar](500) NULL,
[QueryStrings] [nvarchar](500) NULL,
CONSTRAINT [PK_Statistik2010] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is the code for the first table:
USE [DATABASENAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Statistik_neu](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Old] [int] NULL,
[Id_Statistik2] [int] NULL,
[SessionID] [nvarchar](500) NULL,
[NewSession] [bit] NULL,
[ClientIP] [nvarchar](50) NULL,
[UserName] [nvarchar](500) NULL,
[Hostname] [nvarchar](500) NULL,
[Referrer] [nvarchar](500) NULL,
[ReferrerQuery] [nvarchar](max) NULL,
[theRequest] [nvarchar](500) NULL,
[Suchbegriffe] [nvarchar](max) NULL,
[FilePath] [nvarchar](500) NULL,
[Language] [nvarchar](50) NULL,
[theAccessedUri] [nvarchar](500) NULL,
[theBrowser] [nvarchar](500) NULL,
[theReferer] [nvarchar](500) NULL,
[theStatus] [nvarchar](2500) NULL,
[Browser] [nvarchar](500) NULL,
[BrowserMajor] [nvarchar](500) NULL,
[Javascript] [bit] NULL,
[JavaApplets] [bit] NULL,
[Cookies] [bit] NULL,
[Platform] [nvarchar](50) NULL,
[ScreenPixels] [nvarchar](50) NULL,
[DateTime] [datetime] NULL,
[Date] [datetime] NULL,
[Week] [int] NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[PageVisitedNumber] [int] NULL,
[TimeOnWebsite] [datetime] NULL,
[UniqueID] [nvarchar](500) NULL,
[NumberOfVisits] [int] NULL,
[OriginalReferer] [nvarchar](500) NULL,
[QueryStrings] [nvarchar](500) NULL,
CONSTRAINT [PK_Statistik_neu] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is the code of the trigger:
USE [DATABASENAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Trigger_Copy]
ON [dbo].[Statistik_neu]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Insert
Statistik2010 (Id_Statistik2,
SessionID,
NewSession,
ClientIP,
UserName,
Hostname,
Referrer,
ReferrerQuery,
theRequest,
Suchbegriffe,
Filepath,
Language,
theAccessedUri,
theBrowser,
theReferer,
theStatus,
Browser,
BrowserMajor,
Javascript,
JavaApplets,
Cookies,
Platform,
ScreenPixels,
DateTime,
Date,
Week,
Month,
Year,
PageVisitedNumber,
TimeOnWebsite,
UniqueID,
NumberOfVisits,
OriginalReferer,
QueryStrings)
SELECT Id,
SessionID,
NewSession,
ClientIP,
UserName,
Hostname,
Referrer,
ReferrerQuery,
theRequest,
Suchbegriffe,
Filepath,
Language,
theAccessedUri,
theBrowser,
theReferer,
theStatus,
Browser,
BrowserMajor,
Javascript,
JavaApplets,
Cookies,
Platform,
ScreenPixels,
DateTime,
Date,
Week,
Month,
Year,
PageVisitedNumber,
TimeOnWebsite,
UniqueID,
NumberOfVisits,
OriginalReferer,
QueryStrings
FROM
Inserted
with (ROWLOCK)
DELETE FROM Statistik_neu
with (ROWLOCK)
END
GO
January 22, 2010 at 3:44 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2010 at 5:32 am
You're delete statement in your trigger is incorrect. You are attempting to delete the entire table without a where clause which is not what I think you want to do. What you need to do is join the table to the virtual Deleted Table on the primary key so that you are only deleting the records from the underyling table that were added for this SPID.
Your deadlock is most likely occuring because you have two separate SPID's attempting to obtain locks on the data simultaneously 1 SPID is locking to insert into your yearly table while the 2nd SPID is trying to ROWLOCK to delete the data.
Change your delete statement in your trigger as follows
DELETE Statistik_neu
FROM DELETED [D]
WHERE S.ID = D.ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply