Considerations for updating a 'SiteVisit' row many times over a session

Question

SQL Server 2005:

I have a SiteVisit row which contains information about a users visit, for instance HttpRefer, whether or not they placed an order, browser, etc.

Currently for reporting I am joining this table with SiteEvent which contains information about each 'section' visited. This then produces a view which shows statistics about how many sections each user visited. Obviously this is not a sustainable way to do this and now I'm doing some refactoring.

I'd like to move the SectionsVisited column from my View to an actual column in SiteVisit. I'd then update it everytime a user went to that session.

Now my actual question (!) : **What kind of considerations do I need to take into account when updating a row many times per session. Obviously I have an index on the row (currently indexed by a GUID to prevent most malicious tampering). **

I just want to know what non-obvious things I should do (if any). Are there any specific things I should do to optimize the table or will SQL server 2005 pretty much take care of itself

NB: it is a flash site so please dont just recommend a tracking tool. I want to do some 'crazy' datamining and have developed the tracking as such. This is primarily intended to be a database question not a question about 'how to track'.

Requested table definition (subject to change and not tied to it):

USE [RazorSite]
GO
/****** Object:  Table [dbo].[SiteVisit]    Script Date: 10/29/2008 14:35:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SiteVisit](
    [SiteVisitId] [int] IDENTITY(1,1) NOT NULL,
    [SiteUserId] [int] NULL,
    [ClientGUID] [uniqueidentifier] ROWGUIDCOL  NULL CONSTRAINT [DF_SiteVisit_ClientGUID]  DEFAULT (newid()),
    [ServerGUID] [uniqueidentifier] NULL,
    [UserGUID] [uniqueidentifier] NULL,
    [SiteId] [int] NOT NULL,
    [EntryURL] [varchar](100) NULL,
    [CampaignId] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [Cookie] [varchar](50) NULL,
    [UserAgent] [varchar](255) NULL,
    [Platform] [int] NULL,
    [Referer] [varchar](255) NULL,
    [RegisteredReferer] [int] NULL,
    [FlashVersion] [varchar](20) NULL,
    [SiteURL] [varchar](100) NULL,
    [Email] [varchar](50) NULL,
    [FlexSWZVersion] [varchar](20) NULL,
    [HostAddress] [varchar](20) NULL,
    [HostName] [varchar](100) NULL,
    [InitialStageSize] [varchar](20) NULL,
    [OrderId] [varchar](50) NULL,
    [ScreenResolution] [varchar](50) NULL,
    [TotalTimeOnSite] [int] NULL,
    [CumulativeVisitCount] [int] NULL CONSTRAINT [DF_SiteVisit_CumulativeVisitCount]  DEFAULT ((0)),
    [ContentActivatedTime] [int] NULL CONSTRAINT [DF_SiteVisit_ContentActivatedTime]  DEFAULT ((0)),
    [ContentCompleteTime] [int] NULL,
    [MasterVersion] [int] NULL CONSTRAINT [DF_SiteVisit_MasterVersion]  DEFAULT ((0)),
 CONSTRAINT [PK_SiteVisit] PRIMARY KEY CLUSTERED 
(
    [SiteVisitId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SiteVisit]  WITH CHECK ADD  CONSTRAINT [FK_SiteVisit_Platform] FOREIGN KEY([Platform])
REFERENCES [dbo].[Platform] ([PlatformId])
GO
ALTER TABLE [dbo].[SiteVisit] CHECK CONSTRAINT [FK_SiteVisit_Platform]
GO
ALTER TABLE [dbo].[SiteVisit]  WITH CHECK ADD  CONSTRAINT [FK_SiteVisit_Site] FOREIGN KEY([SiteId])
REFERENCES [dbo].[Site] ([SiteId])
GO
ALTER TABLE [dbo].[SiteVisit] CHECK CONSTRAINT [FK_SiteVisit_Site]
GO
ALTER TABLE [dbo].[SiteVisit]  WITH CHECK ADD  CONSTRAINT [FK_SiteVisit_SiteUser] FOREIGN KEY([SiteUserId])
REFERENCES [dbo].[SiteUser] ([SiteUserId])
GO
ALTER TABLE [dbo].[SiteVisit] CHECK CONSTRAINT [FK_SiteVisit_SiteUser]

Current indexes:

IX_CampaignId - non unique, non clustered
IX_ClientGUID - Unique, non clustered     <-- this is how a user is identified for updates
IX_UserGUID - non unique, non clustered
PK_SiteVisit - (SiteVisitId column) - clustered

Answer 1

It depends! Is it a clustered index on the GUID column? What is the ratio of reads to writes? Does any other process insepct/change that table?

You should keep any transaction length to a minimum, and ensure indexes and statistics are up to date.

Answer 2

Can you perhaps post the definition of your sitevisit table, along with the indexes you have? It will help with suggestions.

Answer 3

The best suggestion that I can give is: keep the table small.

How? Have one table that contains all "live" data, i.e. active sessions. When a session expires -> move the data out to an "archive" table or even another database server to do your mining.

Have only very few indexes on the "live" table (session id). You can have all the indexes you want on the "archive" table for faster data retreival.