Performance/Deadlock Problems with large table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Performance/Deadlock Problems with large table

Author

Topic

netzlab
Starting Member

2 Posts

Posted - 2010-01-22 : 05:20:52

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?

Here is the code for the table of the year:

 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

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 05:40:42

"The Server is right now SQL Server Express 2008, but we would have the option to upgrade to the SQL Server 2008 Web Edition"

Separate issue, but if your website is "public facing" I don't think the license allows you to use Express - others will know for sure.

If you don't have good housekeeping routines in place the performance issues may stem from that:

ALTER INDEX ALL ON dbo.MyTable REBUILD WITH (ONLINE=OFF, STATISTICS_NORECOMPUTE = OFF)

UPDATE STATISTICS dbo.MyTable WITH FULL SCAN

The Index Rebuild will lock the table, and may take some time, so don't do it whilst site is busy (there are alternative methods if you have no slack time to run it in)

That may fix the performance issues.

If I've understood correctly I don't think that using a trigger to insert into Statistik2010 and delete from Statistik_neu is a good approach.

I would recommend one of the following:

Fill up the "New" table, then rename the table and create a new one (each day, or week, whatever suits you)

Build a routine to delete regularly. We delete all web log data that is more than 7 days old. (You could "move" it to a different table instead, but rename table,a s above, is quicker / uses less log resources)

Note that this routine has to be sophisticated to not block real work use. We delete in blocks, allow other processes to access in between blocks, and automatically reduce the size of the delete-block if the site becomes busy.

If you rename tables you can then copy/move data from the renamed table to the "master history" table (as a background operation, it won't block current table usage)

You can use a horizontal-partition if you need to query the New table, the temporary renamed table, and the master-history table (or several master-history tables). Thus you can query one VIEW that will actually make intelligent queries of the underlying tables that make up the partitions.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 05:50:07

Good answer from Kristen but I can't resist to comment the approach from OP.

First you say that the insert is too slow.
Then your solution/approach via trigger: Now you have two inserts and one delete instead of one insert. That sounds a bit funny to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

netzlab
Starting Member

2 Posts

Posted - 2010-01-22 : 06:46:35

quote:
Originally posted by webfred

Good answer from Kristen but I can't resist to comment the approach from OP.

First you say that the insert is too slow.
Then your solution/approach via trigger: Now you have two inserts and one delete instead of one insert. That sounds a bit funny to me...

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for the quick answers. The web site uses ASP.NET and when an insert into the database takes about 0,5 -1 second, the website waits these 0,5-1 sec. until the statistics are written and until the visitor of the site can do anything else. So we tried to solve this by writing in an empty table. The solution with the trigger resulted in a terrific increase of performance for the website but introducing these deadlocks.
Kristen’s idea to do the two table solution with a regular routine would solve this problem, but will arise another problem. We would have to rewrite a lot of code with the analytic tools related to this table, when there were two tables, that store the data instead of one...
Using the automatic partitioning function in web edition and above would perhaps be the best long term solution. What do you think?

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 07:02:34

You could:

Rename the current table and create a VIEW in place (i.e. using the name of the current table) - all queries would run as before. The VIEW could be a UNION ALL of the New table and the History table (i.e. using horizontal partitions - i.e. CHECK CONSTRAINTS etc.)

You could put an INSTEAD OF trigger on the View to handle any inserts. However, if the number of places where an Insert or Update can be made is few I would recommend fixing those in-place (INSTEAD OF TRIGGERS are a bit of a PITA).

So, with no / few changes the application does not need to change.

You can then implement "housekeeping" independent of the application. (e.g. rename New table to Working, create New table, move rows from Working to History, then drop Working). Application would still keep querying the VIEW and be none the wiser :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources