slow query on a partitioned table I have a huge table with 3B of rows containing around 6 months of data running on Microsoft SQL server 2017 (RTM), which is partitioned day by day on a date column (each day on a separate filegroup and each filegroup has 1 data file).
this table has an identity field which is bigint.
I have two indexes :
a clustered index on date and id
a non clustered index on date
I'm trying to run the following query :
select top 500000 * from table with(nolock) where id&gt;@certain_id order by id

but the query is taking a lot of time. I tried to create an non clustered index on id field , but nothing changes!
the weird part I was able to run the same query with no issues and with fast response in the past. But due to some circumstances  I had to format the server and re-attach the database containing the partitioned table, and I'm now having this issue.
any hint is much appreciated.
Let's try this query:
SELECT TOP 1000 A.id
FROM  sys.partitions AS P
   CROSS APPLY ( SELECT TOP 1000 T1.id
          FROM dbo.table AS T1
          WHERE $PARTITION.PF1(T1.date) = P.partition_number 
           AND T1.id &gt; @certain_id
           ORDER BY T1.id ) AS A
  WHERE P.object_id = OBJECT_ID('dbo.table')  
  AND P.index_id = INDEXPROPERTY( OBJECTID('dbo.table'), 'idx_c1', 'INDEXID')
    ORDER BY A.id;

from here: https://support.microsoft.com/en-in/help/2965553/decreased-performance-for-sql-server-when-you-run-a-top-max-or-min-agg