SQL Server Index/SQL Performance Enhancement

Question

I have a table in SQL Server that looks like this:

CREATE TABLE [dbo].[FCT_RawEvents](
    [EquipID] [int] NOT NULL,
    [EventTimeStamp] [int] NOT NULL,
    [EventMilliSeconds] [smallint] NULL,
    [EventID] [int] NOT NULL,
    [EventOn] [bit] NOT NULL,
    [JobID] [int] NULL,
    [FirstEvent] [bit] NULL,
    [OperatorId] [int] NULL,
    [Suppressed] [bit] NULL,
    [ManualOverride] [bit] NULL
) 

This contains events, that are either turning on or off (EventOn = True, EventOn = False). Now I need to get all the events which are 'Active' (not suppressed) at a given time. I have some SQL that works, however as this table has rows in the millions, it runs pretty slow (10 seconds for 5 equipIds).

Here it is:

DECLARE @StartDateTime datetime = '2013/01/01'
DECLARE @csvEquipIds nvarchar(MAX) = '5,6,7,8'

DECLARE @StartTimeStamp int = dbo.GetSecondsFromDate(@StartDateTime)
DECLARE @StartMilliSeconds smallint = DATEPART(Ms, @StartDateTime) 

DECLARE @EquipIds TABLE (EquipId int)
INSERT INTO @EquipIds(EquipId) SELECT EquipID FROM dbo.getEquipmentIDs(null,@csvEquipIds)

SELECT dbo.getDateFromSeconds(EventTimeStamp), * FROM
    (   SELECT  re.EquipID,EventTimeStamp,EventMilliSeconds,EventID,eventon,
            ROW_NUMBER() OVER (PARTITION BY re.EquipId,EventID ORDER BY EventTimeStamp DESC,EventMilliSeconds DESC) AS RowNo
        FROM dbo.FCT_RawEvents re
        JOIN @EquipIds eq
        ON eq.EquipId = re.EquipID
        WHERE (re.EventTimeStamp < @StartTimeStamp OR(re.EventTimeStamp = @StartTimeStamp AND re.EventMilliSeconds <= @StartMilliSeconds)) AND re.EventID > 0
        AND (re.Suppressed IS NULL)-- OR re.Suppressed = 0)
    ) ev
WHERE  RowNo = 1  AND EventOn = 1

ORDER BY EquipID,EventID, EventTimeStamp  desc, EventMilliSeconds desc

The execution plan indicates that 80% of the time is being spent on the sort, which is the Partition/Order window function.

I am by no means an INDEX expert, but have added these:

CREATE CLUSTERED INDEX [IX_Clustered] ON [dbo].[FCT_RawEvents] 
(
    [EquipID] ASC,
    [EventTimeStamp] DESC,
    [EventMilliSeconds] DESC,
    [EventID] ASC,
    [EventOn] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_EquipEventTime] ON [dbo].[FCT_RawEvents] 
(
    [EquipID] ASC,
    [EventID] ASC,
    [EventTimeStamp] DESC,
    [EventMilliSeconds] DESC
)
INCLUDE ( [EventOn]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Suppressed] ON [dbo].[FCT_RawEvents] 
(
    [Suppressed] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

It appears that the sort is scanning a large part of the table, where I really want it to 'Look back' until it finds the first matching event.

Any pointers would be much appreciated, either by use of an Index or improving the SQL.

Answer 1

Following up on comments:

• Try replacing the table variable with a temp table: Table variables don't have statistics, temp tables can.

• The second index looks to be redundant.

• Try replacing the scalar valued function.

• Examine the selectivity of your columns

  EquipID, EventTimeStamp, EventMilliSeconds, EventID, EventOn 
  

and create index in order of highest selectivity to lowest. Selectivity is a measure of how many duplicate values are in a column. It ranges from no duplicates (high selectivity) to all the same value (zero selectivity). Ideally, an index should have the columns in the order of highest selectivity.

For example, the selectivity of your EquipId column would be

(SELECT COUNT(DISTINCT EquipId) FROM dbo.FCT_RawEvents) /
    (SELECT COUNT(*) FROM dbo.FCT_RawEvents)

• Ensure your statistics are up date.