I am writing this because I have followed advice I have picked up on a variety of forums, blogs, and documentation sites but am still experiencing what I feel like is unnecessary latency in a query. I guess I am looking for either 

1. validation that I have done what I can
2. suggestions on what more I can do.

I have two tables, `SiteCategories` and a `Sessions` table 

```
CREATE TABLE [assets].[SiteCategories]
(
    [SiteCategoryId] [bigint] NOT NULL,
    [SiteCategoryName] [nvarchar](50) NOT NULL,
    [SiteCategoryDesc] [nvarchar](max) NULL,
    [CreatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    [CreatedBy] [nvarchar](100) NOT NULL DEFAULT ('system'),
    [UpdatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    [UpdatedBy] [nvarchar](100) NOT NULL DEFAULT ('system'),

    PRIMARY KEY CLUSTERED ([SiteCategoryId] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) 
)

CREATE TABLE [session].[Sessions]
(
    [SessionId] [bigint] IDENTITY(1,1) NOT NULL,
    [SessionUID] [uniqueidentifier] NOT NULL,
    [AssetNumber] [nvarchar](50) NOT NULL,
    [SessionUTCOffset] [bigint] NULL,
    [SessionStartUTCTimestamp] [datetime] NULL,
    [SessionStartTimestamp] [datetime] NULL,
    [SessionEndTimestamp] [datetime] NULL,
    [SessionLanguage] [nvarchar](50) NULL,
    [SessionLength] [bigint] NULL,
    [SessionCity] [nvarchar](255) NULL,
    [SessionState] [nvarchar](50) NULL,
    [SessionPostalCode] [nvarchar](255) NULL,
    [SessionCountry] [nvarchar](255) NULL,
    [SessionSiteCategoryId] [bigint] NULL,
    [SessionSiteName] [nvarchar](255) NULL,
    [SessionLocationName] [nvarchar](255) NULL,
    [SessionLocationOwner] [nvarchar](255) NULL,
    [SessionRegion] [nvarchar](255) NULL,
    [SessionStartMethod] [nvarchar](255) NULL,
    [SessionStartMethodId] [bigint] NULL,
    [SessionEndMethod] [nvarchar](255) NULL,
    [SessionEndMethodId] [bigint] NULL,
    [SessionTotalValue] [decimal](12, 2) NULL,
    [SessionTotalDiscount] [decimal](12, 2) NULL,
    [SessionTotalPayment] [decimal](12, 2) NULL,
    [OperatingSystem] [nvarchar](50) NULL,
    [OnlineAccessCode] [nvarchar](50) NULL,
    [CaptureSource] [nvarchar](50) NULL,
    [CreatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    [CreatedBy] [nvarchar](50) NOT NULL DEFAULT ('system'),
    [UpdatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    [UpdatedBy] [nvarchar](50) NOT NULL DEFAULT ('system'),

    PRIMARY KEY CLUSTERED ([SessionId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) 
) 
GO

ALTER TABLE [session].[Sessions] WITH NOCHECK 
   ADD CONSTRAINT [FK_Sessions_ToSiteCategories] 
       FOREIGN KEY([SessionSiteCategoryId])
       REFERENCES [assets].[SiteCategories] ([SiteCategoryId])
GO

ALTER TABLE [session].[Sessions] 
    CHECK CONSTRAINT [FK_Sessions_ToSiteCategories]
GO

```

The `SiteCategories` table only has about 14 records. 

The `Sessions` table contains 1,291,482 records.

I have written a very minimal query which joins the two, extracting records for a date range ... 

```
DECLARE @nUndefined INT = -1;
DECLARE @catID BIGINT   = -1;

SELECT 
    [hdr].SessionStartTimestamp,
    [hdr].SessionSiteCategoryId,
    [hdr].AssetNumber
FROM 
    session.Sessions AS [hdr]
LEFT OUTER JOIN 
    assets.SiteCategories AS [cat] ON [hdr].SessionSiteCategoryId = [cat].SiteCategoryId
WHERE 
    [hdr].AssetNumber NOT LIKE 'FE%'
    AND [hdr].AssetNumber NOT LIKE 'QA%'
    AND [hdr].AssetNumber NOT LIKE '%alex%'
    AND [hdr].AssetNumber NOT LIKE '%desk%'
    AND [hdr].AssetNumber NOT LIKE '%test%'
    AND [hdr].AssetNumber NOT LIKE '%R&D%'
    AND [hdr].AssetNumber <> 'base'
    AND (@catID = @nUndefined OR (@catID <> @nUndefined AND [hdr].SessionSiteCategoryId = @catID))
    AND [hdr].SessionStartTimestamp BETWEEN '2016-03-01 00:00:00' AND '2016-06-28 23:59:59'

```

The query returns 1,172,082 records in about 13 seconds (according to SSMS). I need to return these records as the basis for further data aggregation. Starting with 13 seconds is a tedious amount of time to have to wait and it seems like it could/should be quicker. I know 1 million records is nothing to sneeze at but it's not exactly "big data" (is it?). Of course, the smaller the date range and result set the quicker the query.

The index I have added to the `Sessions` table includes all three of the lookup fields as well as including any returned fields (in this case just the indexed fields) so that any return to the original table is unnecessary as it can query and extract all it needs from the index. 

I have also run `UPDATE STATISTICS` against the tables and indexes ... 

Are my expectations overly optimistic? Is 13 seconds for this data volume and query within the realm of reason or am I missing something? 

Thanks,
G

**EDIT - ADDED EXECUTION PLAN**

[![enter image description here](https://i.stack.imgur.com/6ueZy.jpg)](https://i.stack.imgur.com/6ueZy.jpg)

**EDIT- UPDATED QUERY AND EXECUTION PLAN** 

In an effort to incorporate all the suggestions (thanks a TON by the way!), I have trimmed down the query to the bare necessities.

```
DECLARE @nUndefined INT = -1;
DECLARE @catID BIGINT   = @nUndefined;

SELECT 
    [hdr].SessionStartTimestamp, 
    [hdr].SessionSiteCategoryId,
    [hdr].AssetNumber
FROM 
    session.Sessions AS [hdr]
WHERE 
    (@catID = @nUndefined OR 
         (@catID <> @nUndefined AND [hdr].SessionSiteCategoryId = @catID))
    AND [hdr].SessionStartTimestamp BETWEEN '2016-03-01 00:00:00' AND '2016-06-28 23:59:59'

```

[![enter image description here](https://i.stack.imgur.com/4XPQx.jpg)](https://i.stack.imgur.com/4XPQx.jpg)

**EDIT - INDEX**

This is the index that the query is using. I have indexed ALL the fields that are being filtered on and even included those fields in the index to prevent needing to go from the index to the table to fetch the actual data. 

```
CREATE NONCLUSTERED INDEX [IX_SessionStartTimestamp] ON [session].[Sessions]
(
    [SessionStartTimestamp] DESC,
    [SessionSiteCategoryId] ASC,
    [AssetNumber] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

```