The forum I'm in charge of has 1 million plus posts and just shy of 100k topics. Recently we've been getting a good deal of deadlocks on the posts and topics tables. The machine it's running on is a quad xeon 550 mhz machine with a meg of cache on each processor, along with 2 gigs of ram. Average daily concurrent forum users is anywhere between 400-500 logged in users, with high spots of up to 700+ logged in users.Here's the tables:CREATE TABLE [dbo].[Topics] ( [ID] [int] IDENTITY (1, 1), [Forum] [int], [Subject] [varchar] (200), [Icon] [smallint], [Replies] [int], [FirstUserID] [int], [LastUserID] [int], [LastPost] [datetime], [Views] [int], [Poll] [int], [Locked] [tinyint] , [LockedOn] [smalldatetime]) ON [PRIMARY]ALTER TABLE [dbo].[Topics] WITH NOCHECK ADD CONSTRAINT [PK_Topics] PRIMARY KEY CLUSTERED ( [ID] DESC ) WITH FILLFACTOR = 55 ON [PRIMARY] CREATE INDEX [IX_Topics] ON [dbo].[Topics]([LastPost] DESC , [Forum]) WITH FILLFACTOR = 75 ON [PRIMARY]CREATE INDEX [IX_Topics_1] ON [dbo].[Topics]([FirstUserID] DESC , [LastUserID] DESC ) WITH FILLFACTOR = 55 ON [PRIMARY]CREATE TABLE [dbo].[Posts] ( [ID] [int] IDENTITY (1, 1), [SID] [int], [Topic] [int], [Subject] [varchar] (200), [Message] [text], [UserID] [int], [Posted] [datetime], [Image1] [varchar] (5), [Image2] [varchar] (5), [Image3] [varchar] (5), [Image4] [varchar] (5), [Image5] [varchar] (5), [IP] [varchar] (15), [Edited] [datetime], [EditIP] [varchar] (15), [Inserted] [timestamp] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]ALTER TABLE [dbo].[Posts] WITH NOCHECK ADD CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED ( [ID] DESC ) WITH FILLFACTOR = 95 ON [PRIMARY] CREATE INDEX [IX_Posts] ON [dbo].[Posts]([Topic] DESC , [SID]) WITH FILLFACTOR = 95 ON [PRIMARY]CREATE INDEX [IX_Posts_1] ON [dbo].[Posts]([UserID]) WITH FILLFACTOR = 95 ON [PRIMARY]
Of note: - Posts.Topic is foreign key to Topic.ID - Posts.SID is Sequential ID field for that topic (numbers the posts of the topic) - I DBCC DBREINDEX both of these tables once a week during off-peak hours - for a little extra speed, the database is on simple recovery modeAre my indexes horribly off or something? I'm just trying to make sure I can squeeze all the performance out of this box as we can before telling the boss it's time to open his wallet for a nice new machine. If I'm spelling horrible or am being retarded about something I apologize; it's 3:30 AM here in NY right now...Thx,Jay
Deadlocks on high traffic forum
Posted - 2004-02-13 : 03:34:53
