How to make my query use the available index

Question

I have the following time-series Table in SQL:

CREATE TABLE [dbo].[SensorData](
 [DateTimeUtc] [datetime2](2) NOT NULL,
 [SensorId] [int] NOT NULL,
 [Key] [varchar](20) NOT NULL,
 [Value] [decimal](19, 4) NULL,
CONSTRAINT [PK_SensorData] PRIMARY KEY CLUSTERED 
(
  [SensorId] ASC,
  [Key] ASC,
  [DateTimeUtc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = 
ON, Data_Compression=PAGE) ON PS_Daily(DateTimeUtc))

Now based on this index, every single query needs to have the following parameters in the query where filter:

[SensorId],
[Key],
[DateTimeUtc]

When the query has all three the query returns really fast as expected.

Now I am stuck with a specific query, that does not have any specific value for [Key]. For example:

Check if there is ANY data for sensor: 1234 in the past 12 hours. In this case, we have a filter value for DateTime and SensorId. But this query returns really slowly.

Does adding Where [Key] is not null make SQL hit that index?

I know the easy answer is to add a new index on the table with only [SensorId],[DateTimeUtc]; however, this will add a substantial amount of space to the db based on its size and will also slow down inserts.

Is there any way I can get the above query to hit the clustered index?

The reason I used the clustered index key order I did, was after reading up on how you should order it, items of which values will be the most unique should be first.

I ran EXEC sp_spaceused [SensorData]

Query:

SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[SensorData] AS [Extent1]
    WHERE ([Extent1].[DateTimeUtc] > @p__linq__0) AND ([Extent1].[DateTimeUtc] <= @p__linq__1) AND ([Extent1].[SensorId] = @p__linq__2)
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

There are generally about 10 to 40 distinct keys per SensorId per time period. But we obviously store those 10 to 40 keys thousands of times a day per SensorId.

Answer 1

Have you thought about using ADX (Azure Data Explorer) to store this table? Such queries are a breeze for ADX. ADX is a time series optimized cluster on Azure. For me, it seems to be the natural solution for this type of problem.

It will manage the partitions, indexes and you can create a retention policy automatically.

If you are planning to consume the data with Power BI (or Grafana), you could also do Direct Query on ADX, so no need to import a huge amount of data when processing your data model. Also, the connector for ADX supports very well query folding, so if you are using Power BI (for example) the queries for your visuals will be translated to Kusto on the fly.

If your sensor data comes from Event Hubs or IoT Hub, you can ingest the data directly to ADX (if it is a supported file type), or even use Stream Analytics to create a custom deserializer to ingest diverse data.

Please, let me know if you need more context.

Answer 2

Your current clustered index definition is (SensorId, Key, DateTimeUtc) which will cover queries with predicates on all three of those fields. It also should cover any predicates using any sequential subset of that definition (reading from left to right), e.g. a predicate using SensorId and Key or even a predicate just using SensorId. This is because the order that you list the fields in an index definition is the order that the B-Tree of that index is sorted on.

In your case the B-Tree is currently sorted by SensorId then by Key and finally by DateTimeUtc. A more helpful index design to support the additional use case you mentioned would be to re-arrange the columns in the definition so it's (DateTimeUtc, SensorId, Key) instead (as mentioned in the comments). That single clustered index will cover your use case where you're using all three fields to filter on, and it supports the use case where you just want to know if there's any data for a given date range for a given sensor. It even also covers you if you wanted to know if there was any data at all for any given date range.

As discussed in the comments, if you're planning to do range filtering on DateTimeUtc, you may find better performance still leading with the SensorId that you're filtering on with an equality match. But you could still arrange the columns in the clustered index definition as (SensorId, DateTimeUtc, Key) that way it covers both your aforementioned use cases.

So just by re-arranging the columns in your clustered index definition, you're able to accomplish your goal without introducing any new indexes and without taking up any additional disk space.

Side note: you can use the system stored procedure sp_spaceused to almost immediately find out how big (by rows and consumed disk size) a given table is. For example EXEC sp_spaceused 'dbo.SensorData';.

Answer 3

Does adding Where [Key] is not null make SQL hit that index?

No, because "is not null" is an inequality.

Rearranging the keys of the clustered index is unlikely to bring you the benefits you are after because equality tests must come before any inequalities for a b-tree index. (SensorId, DateTimeUtc, Key) would speed up your exists query, but slow down queries that also provide an equality test for the Key column. All rows that match the seek on SensorId and the DateTimeUtc range would need a residual predicate for the Key test.

Solution

Nevertheless, it is possible to speed up your exists query using only the existing clustered index, given there are relatively few unique Key values:

There are generally about 10 to 40 distinct keys per SensorId per time period.

The technique is to simulate an index "skip scan" as I describe in my article, Finding Distinct Values Quickly. There is a slight complication due to the partitioning scheme, which adds the partition id as an implied leading key to the index, but the general idea is the same.

We can use the existing index to find unique Key values by starting with the lowest Key, then finding the next value recursively. This is very efficient because it uses the first two columns of the existing index, an equality test on SensorId and an inequality on Key.

The list of distinct Key values then allows us to use the existing index efficiently, since we have SensorId, Key, and DateTimeUtc values.

I have assumed your partitioning function is named PF_Daily and wrapped the skip scan and index search in the following inline table-valued function:

CREATE OR ALTER FUNCTION dbo.GetSensorDataUsingSkipScan
(
    @SensorId integer,
    @ForDate datetime2(2)
)
RETURNS table
AS
RETURN
    WITH 
        SkipScan AS
        (
            -- Anchor:
            -- Lowest Key value for the given SensorId and date partition
            SELECT TOP (1) SD.[Key]
            FROM dbo.SensorData AS SD
            WHERE SD.SensorId = @SensorId
            AND $PARTITION.PF_Daily(SD.DateTimeUtc) = $PARTITION.PF_Daily(@ForDate)
            ORDER BY SD.[Key] ASC

            UNION ALL 

            -- Recursive:
            -- Next highest Key value for the given SensorId and date partition
            SELECT NextKey.[Key]
            FROM 
            (
                SELECT 
                    SD.[Key], 
                    rn = ROW_NUMBER() OVER (
                        ORDER BY SD.[Key]) 
                FROM SkipScan AS SS
                JOIN dbo.SensorData AS SD
                    ON SD.[Key] > SS.[Key]
                WHERE SD.SensorId = @SensorId
                AND $PARTITION.PF_Daily(SD.DateTimeUtc) = $PARTITION.PF_Daily(@ForDate)
            ) AS NextKey
            WHERE NextKey.rn = 1
        )
    SELECT SD.*
    FROM SkipScan AS SS
    CROSS APPLY 
    (
        -- Find the sensor data using the SensorId, Key, and Date
        SELECT TOP (1) SD.* 
        FROM dbo.SensorData AS SD
        WHERE 
            $PARTITION.PF_Daily(SD.DateTimeUtc) = $PARTITION.PF_Daily(@ForDate)
            AND SD.SensorId = @SensorId
            AND SD.[Key] = SS.[Key]
            AND SD.DateTimeUtc >= @ForDate
    ) AS SD;

The exists test can then be rewritten to use the function as:

-- Search parameters
DECLARE 
    @SensorId integer = 1234,
    @StartDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME()),
    @EndDate datetime2(2) = SYSUTCDATETIME();

SELECT
    CASE
        WHEN EXISTS 
        (
            SELECT * FROM dbo.GetSensorDataUsingSkipScan(@SensorId, @StartDate)
        )
        OR EXISTS 
        (
            SELECT * FROM dbo.GetSensorDataUsingSkipScan(@SensorId, @EndDate)
        )
        THEN CONVERT(bit, 'true')
        ELSE CONVERT(bit, 'false')
    END
OPTION (MAXRECURSION 0);

The code can be simplified to a single skip scan if you are happy to check only the most recent partition. I assumed your 12-hour range could span daily partitions.