SQL Server 2017 – How to Fix Slow Query on a Partitioned Table

partitioningsql-server-2017

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.

Best Answer

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

Related Solutions

Sql-server – getting Table scan on a partitioned table query

The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.

Sql-server – Expanding a partitioned table

Here are two best practices for partitioning that pertain to the question:

Keep an empty staging partition at the leftmost and rightmost ends of the partition range to ensure that the partitions split when loading in new data, and merge, after unloading old data, do not cause data movement.
Do not split or merge a partition already populated with data because this can cause severe locking and explosive log growth.

http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5

If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.

To check if the leftmost partition is empty, use a query like the following:

DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';

SELECT 
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;

If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.

Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.

Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.

Best Answer

Related Solutions

Sql-server – getting Table scan on a partitioned table query

Sql-server – Expanding a partitioned table

Related Question