2

I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not willing to create any additional index due to Very large table size and about 8 indexes already existing.

Actual table is partitioned and has over 4 billion rows and is 5 TB in size. I am using the demo to reproduce the behavior for below table def:--

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
    [DemoID] [int] IDENTITY(1,1) NOT NULL,
    [SomeData] [sysname] NOT NULL,
    [Lastseen] [datetime] NULL,
    [DataKey1] [char] NOT NULL,
    [DataKey2] [char] NOT NULL,
    [RandomColumn] [nvarchar] NOT NULL,
    [CaptureDate] [datetime] NULL,
    CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED 
    (
        [DemoID] ASC,
        [CaptureDate] ASC
    )
    ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

None of the available indexes in the database has the columns as key columns or in included part of the index.

Query run is below

SELECT distinct RandomColumn,Lastseen from  [dbo].[DemoPartitionedTable]
WHERE
Lastseen >='2019-09-26 00:00:00.000'
and Lastseen<'2019-10-04 00:00:00.000'

The plan for above is

enter image description here

The plan actually on my server with massive data on that large table makes the query go parallel and has Re partition stream before hash match operator in above plan and gather streams after that. From estimated plan i see estimated rows is over 65 million .

I tested in dev with index which definitely make this faster by creating leading column as last seen and random column in included.

But is there a way i can make this faster without adding index, changing anything to query?

  • If you know any correlation between CaptureDate and Lastseen try to add a condition for CaptureDate. For example if Lastseen of this year can be found only in records with CaptureDate of the same year add "and CaptureDate >= '20190101' – sepupic Oct 4 at 6:49
  • Have you tried WHERE [Lastseen] BETWEEN '2019-09-26 00:00:00.000' AND '2019-10-04 00:00:00.000'; – James Jenkins Oct 4 at 16:52
  • You could always have an indexed view on the table that only includes the date range that you need in the query, if this date range doesn't change too often. – Danielle Paquette-Harvey Oct 4 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.