April 23, 2007 at 3:48 am
I would like some advice on the best indexes to use on a couple of tables that capture web site user history.
I have a main table TRAFFIC_LOG that will only hold a days worth of records.
The main columns are shown in the table definition below
CREATE TABLE [dbo].[TRAFFIC_LOG](
[RecordNo] [varbinary](50) NOT NULL CONSTRAINT [DF_TRAFFIC_LOG_RecordNo] DEFAULT (newid()),
[Stamp] [datetime] NOT NULL CONSTRAINT [DF_TRAFFIC_LOG_LogDate] DEFAULT (getdate()),
[WebServer] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[SiteFK] [int] NOT NULL,
[SiteVersion] [int] NOT NULL CONSTRAINT [DF_TRAFFIC_LOG_Version] DEFAULT ((1)),
[SessionID] [int] NOT NULL,
[CandFK] [int] NULL,
[ClientFk] [int] NULL,
[UserFk] [int] NULL,
[AdminFK] [int] NULL,
[InAdminMode] [bit] NULL CONSTRAINT [DF_TRAFFIC_LOG_InAdminMode] DEFAULT ((0)),
CONSTRAINT [PK_TRAFFIC_LOG] PRIMARY KEY CLUSTERED
(
[RecordNo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I then have a history table TRAFFIC_LOG_HISTORY (same column definition) which holds all data apart from the current days worth.
A job will run 3.am that will drop any indexes I have on the history table, insert all the data from the main table and then rebuild the indexes with a fillfactor of 100 as the table will only be used for searches throughout the day and the only insert is this nightly insert.
All searches against the 2 tables will use the Stamp date column and then a mixture of (SiteFK, AdminFK, ClientFk, UserFk, CandFK, SessionID) depending on what search terms have been provided.
I don't have much experience of using GUIDs for PKs and don't know whether its best to use a clustered index on the RecordNo (GUID) PK and then a series of non-clustered indexes on (Stamp, SiteFK, AdminFK, ClientFk, UserFk, CandFK, SessionID) OR maybe a non-clustered unique index on RecordNo and have the clustered index as a composite on (Stamp, SiteFK, SessionID - which are always stored in the table)
I know performance tuning is an art in itself and the best way is to test/tweak/test again but I would just like to see what people out there suggest would be the best place to start and then go from there.
Thanks.
April 23, 2007 at 7:47 am
Don't put the cluster on the GUID.
Experimentation is the key. Just a swag, put the cluster on the date column and then, depending on the manner in which the queries are written, a compound index on SiteKf,AdminFk...etc, or possibly, again, depending on how the queries are responding, individual indexes on some of these columns with INCLUDES to fill in & make the indexes covering in areas where they can be.
As to my first statement, actually, that can be a good choice, but I've seldom seen it. It all really depends on the way the data is being retrieved.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2007 at 10:23 am
Currently I am not retrieving any data using the RecordNo GUID. Although maybe in future I may need to retrieve individual records based on that ID alone.
I have re jigged the indexes so that I have
Primary Key is a non-clustered unqiue constraint on Record No (GUID)
Clustered Index is on Stamp
Non-clustered index on SitePk
Non-clustered index on SessionID
Non-clustered compound index on AdminFK,ClientFk,UserFK,CandFK
All with fill factor of 100
Seems to be working okay at the moment so I will see how it pans out and run some tests with the existing search procs to see if the Query Plans are all ok.
Thanx for help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply