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>@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.