Sql-server – Optimize index on a 2,135,044,521 row table

azure-sql-databaseindex-tuningperformancequery-performancesql server

I have an I/O problem with a large table.

General stats

The table has the following main characteristics:

environment: Azure SQL Database (tier is P4 Premium (500 DTUs))
rows: 2,135,044,521
1,275 used partitions
clustered and partitioned index

Model

This is the table implementation:

CREATE TABLE [data].[DemoUnitData](
    [UnitID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Value1] [decimal](18, 2) NULL,
    [Value2] [decimal](18, 2) NULL,
    [Value3] [decimal](18, 2) NULL,
    CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED 
    (
        [UnitID] ASC,
        [Timestamp] ASC
    )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

The partitioning is related to this:

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

Quality of service

I think the indexes and statistics are well maintained every night by incremental rebuild/reorganize/update.

These are the current index stats of the most heavily used index partitions:

These are the current statistics properties of the most heavily used partitions:

Problem

I run a simple query on a high frequency against the table.

SELECT [UnitID]
    ,[Timestamp]
    ,[Value1]
    ,[Value2]
    ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

The execution plan looks like this: https://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

My problem is that these queries produce an extremely high amount of I/O operations resulting in a bottleneck of PAGEIOLATCH_SH waits.

Question

I have read that PAGEIOLATCH_SH waits are often related to not well-optimized indexes. Are there any recommendations you have for me how to reduce I/O operations? Maybe by adding a better index?

Answer 1 – related to comment from @S4V1N

The posted query plan was from a query I executed in SSMS. After your comment I do some research on the server history. The accual query exceuted from the service looks a bit different (EntityFramework related).

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)) 

SELECT 1 AS [C1], [Extent1] 
   .[Timestamp] AS [Timestamp], [Extent1] 
   .[Value1] AS [Value1], [Extent1] 
   .[Value2] AS [Value2], [Extent1] 
   .[Value3] AS [Value3]  
FROM [data].[DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1)

Also, the plan looks different:

https://www.brentozar.com/pastetheplan/?id=H1fhALpKG

https://www.brentozar.com/pastetheplan/?id=S1DFQvpKz

And like you can see here, our DB performance is hardly influenced by this query.

Answer 2 – related to answer from @Joe Obbish

For testing the solution I replaced Entity Framework with a simple SqlCommand.
The result was an amazing performance boost!

The query plan is now the same as in SSMS and the logical reads and writes drop to ~8 per execution.

The overall I/O load drop to almost 0!

It also explains why I get a big performance drop after I changed the partition range from monthly to daily. The missing of partition elimination resulted in more partitions to scan.

Best Answer

You might be able to reduce PAGEIOLATCH_SH waits for this query if you're able to change the data types generated by the ORM. The Timestamp column in your table has a data type of DATETIME but the parameters @p__linq__1 and @p__linq__2 have data types of DATETIME2(7). That difference is why the query plan for the ORM queries is so much more complicated than the first query plan that you posted that had hardcoded search filters. You can get a hint of this in the XML as well:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))">

As is, with the ORM query you cannot get any partition elimination. You will get at least a few logical reads for every partition that's defined in the partition function, even if you're just searching for a day of data. Within each partition you get an index seek so it doesn't take long for SQL Server to move on to the next partition, but perhaps all of that IO is adding up.

I did a simple reproduction to be sure. There are 11 partitions defined within the partition function. For this query:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

Here's what IO looks like:

Table 'DemoUnitData'. Scan count 11, logical reads 40

When I fix the data types:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

IO is reduced as a result of partition elimination:

Table 'DemoUnitData'. Scan count 2, logical reads 8

Related Solutions

Sql-server – Index scan of table with one record with 2.2 billion executions

Let's start by looking at the top right of the plan. That part calculates the OperatingDate column:

Since we get back 1.72 M rows for the outer row set we can expect around 1.72 M index seeks against ix_days. That is indeed what happens. There are 478k rows for which o.[CreationDate] as time) > '16:00:00' so the CASE statement sends 478k seeks to one branch and the rest to the other.

Note that the index that you have isn't the most efficient one possible for this query. We can only do a seek predicate against PKDate. The rest of the filters are applied as a predicate. This means that the seek might traverse many rows before finding a match. I assume that most days in your calendar table aren't weekends or holidays so it may not make a practical difference for this query. However, you could define an index on is_weekend, is_holiday, PKDate. That should let you immediately seek to the first row that you want.

To make the point more clear let's go through a simple example:

-- does a scan
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend <> 1 AND is_holiday <> 1
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

-- does a seek, reads 3 rows to return 1
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend = 0 AND is_holiday = 0
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

-- create new index
CREATE NONCLUSTERED INDEX [ix_days_2] ON [dbo].[days]
(
    [is_weekend],
    [is_holiday],
    PkDate
)

-- does a seek, reads 1 row to return 1
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend = 0 AND is_holiday = 0
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

DROP INDEX [days].[ix_days_2];

Let's get to the more interesting part which is the branch to calculate the DeliveryDate column. I'll only include half of it:

I suspect that what you hoped the optimizer would do is to calculate this as a scalar:

dateadd(day,isnull(
                  (select top 1 [operatingdays]
                  from [dbo].[CS]
                  where DefaultService = 1)
                 ,2)+1,Cast(o.[CreationDate] as date))

And to use the value of that to do an index seek using ix_days. Unfortunately, the optimizer does not do that. It instead applies a row goal against the index and does a scan. For each row returned from the scan it checks to see if the value matches the filter against [dbo].[CS]. The scan stops as soon as it finds one row that matches. SQL Server estimated that it would only pull back 3.33 rows on average from the scan until it found a match. If that were true then you'd see around 1.5 M executions against [dbo].[CS]. Instead the optimizer did 2 billion executions against the table, so the estimate was off by over 1000 times.

As a general rule you should carefully examine any scans on the inner side of a nested loop. Of course, there are some queries for which that is what you want. And just because you have a seek doesn't mean that the query will be efficient. For example, if a seek returns many rows there may not be that much difference from doing a scan. You didn't post the full query here, but I'll go over a few ideas which could help.

This query is a bit odd:

select top 1 [operatingdays]
from [dbo].[CS]
where DefaultService = 1

It is non-deterministic because you have TOP without ORDER BY. However, the table itself has 1 row and you always pull back the same value for each row from o. If possible, I would just try saving off the value of this query into a local variable and using that in the query instead. That should save you a total of 8 billion scans again [dbo].[CS] and I would expect to see an index seek instead of an index scan against ix_days. I was able to mock up some data on my machine. Here is part of the query plan:

Now we have all seeks and those seeks shouldn't process too many extra rows. However, the real query may be more complicated than that so perhaps you can't use a variable.

Let's say I write a different filter condition that doesn't use TOP. Instead I'll use MIN. SQL Server is able to process that subquery in a more efficient way. TOP can prevent certain query transformations. Here is my subquery:

WHERE PKDate > dateadd(day,isnull(
                      (select MIN([operatingdays])
                       from [dbo].[CS]
                       where DefaultService = 1)
                      ,2), Cast(o.[CreationDate] as date))

Here is what the plan might look like:

Now we'll only do around 1.5 million scans against the CS table. we also get a much more efficient index seek against the ix_days index which is able to use the results of the subquery:

Of course, I'm not saying that you should rewrite your code to use that. It'll probably return incorrect results. The important point is that you can get the index seeks that you want with a subquery. You just need to write your subquery in the right way.

For one more example, let's assume that you absolutely need to keep the TOP operator in the subquery. It might be possible to add a redundant filter against PkDate to get better performance. I'm going to assume that the results of the subquery are non-negative and small. That means that this query will be equivalent:

  PKDate > Cast(o.[CreationDate] as date) AND 
  PKDate > dateadd(day,isnull(
      (select top 1 [operatingdays]
      from [dbo].[CS]
      where DefaultService = 1)
     ,2)+1,Cast(o.[CreationDate] as date))

This changes the plan to use seeks:

It's important to realize that the seeks may return more just one row. The important point is that SQL Server can start seeking at o.[CreationDate]. If there's a large gap in the dates then the index seek will process many extra rows and the query will not be as efficient.

Sql-server – Tuning a query with temp table join

Why does the inner join to a one record temp table make the query take so much longer time?

Without the join, the optimizer is smart enough to work out that it can find the minimum value by reading one row from the end of the index.

Unfortunately, it is not currently equipped to apply the same sort of logic when the query is more complicated (with a join or grouping clause, for example). To work around this limitation, you can rewrite the query to compute local minimums per row in the temporary table, then find the global minimum.

Perhaps the easiest way to express this in T-SQL is to use the APPLY operator:

SELECT
    -- Global minimum
    @tenor_from = MIN(MinMaturityPerCurveID.maturity_date)
FROM #source_price_curve_list AS SPCL
CROSS APPLY
(
    -- Minimum maturity_date per price_curve_id
    SELECT TOP (1) 
        SPC.maturity_date
    FROM  dbo.source_price_curve AS SPC
    WHERE
        SPC.source_curve_def_id = SPCL.price_curve_id
         and as_of_date >= @as_of_date_from 
    ORDER BY
        SPC.maturity_date ASC
) AS MinMaturityPerCurveID;

Good performance relies on there being many rows per price_curve_id. You may need an index of the form:

CREATE NONCLUSTERED INDEX
    [IX dbo.source_price_curve source_curve_def_id, maturity_date, as_of_date]
ON dbo.source_price_curve 
(
    source_curve_def_id,
    maturity_date,
    as_of_date
);