Improving SQL Server / SQL Performance

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 enabled or disabled (EventOn = True, EventOn = False). Now I need to get all events that are "active" (not suppressed) at a given time. I have a SQL that works, however, since this table has millions of rows, it is quite slow (10 seconds for 5 equipIds).

Here he 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 shows that 80% of the time is spent on sorting, which is a function of the section / order window.

Execution Plan:

I am in no way an INDEX expert, but added:

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 looks like the sort is looking through most of the table where I really want it to look backward until it finds the first matching event.

Any pointers would be much appreciated either with Index or SQL enhancement.

+3


source to share


1 answer


After comments:

  • Try replacing the table variable with a temporary table: table variables have no statistics, there may be temporary tables.

  • The second index looks redundant.

  • Try replacing the scalar function.

  • Examine the selectivity of your columns

    EquipID, EventTimeStamp, EventMilliSeconds, EventID, EventOn 
    
          

and create the index in order of highest selectivity to lowest. Selectivity is a measure of the number of duplicate values โ€‹โ€‹in a column. It ranges from any duplicates (high selectivity) to all the same values โ€‹โ€‹(zero selectivity). Ideally, the index should have columns in the order of highest selectivity.



For example, the selectivity of your column EquipId

would be

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

      

  • Make sure your statistics are out of date.
+1


source







All Articles