0

We are working on a Microsoft APS / PDW Appliance which consists of numerous huge tables. Most of these tables feature a Clustered Columnstore Index. For reasons of maintenance we implemented a process, which evaluates the number of compressed and uncompressed rows in the partitions of the CCI. Only if there uncomprerssed rows, the corresponding partition is rebuilt (ALTER INDEX... REBUILD PARTITION = ...). The evaluation of the partitions which have to be rebuilt is performed via several tables, one of which is pdw_nodes_column_store_row_groups. However, for one reason or the other querying this table is (meanwhile) extremely slow:

SELECT *
  FROM sys.pdw_nodes_column_store_row_groups
  WHERE object_id = 123456
    AND pdw_node_id = 789 
    AND index_id = 1

The further calculation look like this (excerpt):

ISNULL(SUM(CASE 
             WHEN State_description = 'Compressed'
               THEN CAST(total_rows AS DECIMAL(12, 2))
           END)
       , 0) / (SUM(CAST(ISNULL(total_rows, 1) AS DECIMAL(12, 2)))) AS CompRelation

Only rows with a CompRelation < 1 are considered as "to be rebuilt".

But considering that the query without the calculation already takes that long, I doubt that it is related to the calculation itself.

For an unpartitioned table with CCI this query took 4 minutes for a single row to return.

Is there any other way to evaluate which CCI Partitions need to be rebuilt? Or is there anything we can do in order to increase the performance of the query mentioned above?

Tyron78
  • 4,117
  • 2
  • 17
  • 32
  • Probably will get more traction with this one on https://dba.stackexchange.com/. I voted to move it there. – squillman Feb 02 '22 at 14:52
  • @squillman oh, OK... thought that here would be the right spot to ask for DMVs, Indexes and other SQL related stuff... don't know how a DBA would help me here.. – Tyron78 Feb 02 '22 at 15:00
  • There is overlap for sure, and others here might know. But with DMV's and things related to the db engine it's usually right in the audience's wheelhouse at DBA. – squillman Feb 02 '22 at 15:04

0 Answers0