2
CREATE TABLE [dbo].[session]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    [supervisor_id] [int] NULL,
    some columns ..

    CONSTRAINT [PK_session] 
      PRIMARY KEY CLUSTERED ([id] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl2]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [close_date] [datetime] NULL,
    [edit_date] [datetime] NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    some columns ..

    CONSTRAINT [PK_tbl2] 
    PRIMARY KEY CLUSTERED ([id] ASC)
       WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
             ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

I have those two tables which I can join on the session_id column. There are more than 20 000 rows in each table, and the join is too slow. Is it a good idea to create a nonclustered index on session_id columns which are of uniqueidentifier type? Or there is another way to do the join faster ?

Edit:

For example query is like :

SELECT some_columns...
FROM [dbo].[session] s
INNER JOIN [dbo].[tbl2] t2 on s.[session_id] = t2.[session_id]
WHERE t2.some_column = something
  • Can you add a sample query ? – itzik Paz Nov 30 '14 at 19:31
  • @itzikPaz I edit question – GeoVIP Nov 30 '14 at 19:42
2

According to your sample query, you must have two separate NonClustered Indexes on Session_id column for both tables. Further it must contain columns that are being used by SELECT clause to avoid bookmark lookups.

Fill factor for indexes on uniqueidentifier: If only key column (like in your case) is uniqueidentifier then you can fill all the page (Fill Factor = 100), as there are no chances of value updates for this column.

But drawback for index creation on uniqueidentifier column is that GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters and are randomly generated by SQL Server hence huge chances of page split and index fragmentation. Using NEWSEQUENTIALID() (SQL SERVER 2008 and above) to insert values instead of NEWID() can resolve this problem.

  • I wouldn't term a uniqueidentifier "wide". It is a 128-bit integer, not a char() or binary(). It is just two bigints stuck together. :-) – Greenstone Walker Dec 2 '14 at 2:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.