question

RaikarNiranjan-1486 avatar image
0 Votes"
RaikarNiranjan-1486 asked NavtejSaini-MSFT commented

insert performance degraded after dropping and re creating columnstore index on a partitioned table

The table was partitioned but the range was set to 20, lately the range was exhausted so we increased the range from 20 to 5000. The table had clustered column store index on it so we had to follow these below steps

  1. Drop Clustered column store index on the table

  2. Extended the range by following statement, this was done in loop till 4999 range
    ALTER PARTITION FUNCTION partitionfunctionname() SPLIT RANGE (21);
    ALTER PARTITION SCHEME partitionschemaname NEXT USED [PRIMARY]

  3. Created the Clustered column store index back on the table

Now we see the insertion is taking more time the same operation which would be completed within 10 mins is taking 60 mins. I read about fragmentation does this has to do anything with it

Note: Table is on azure sql database

sql-server-generalazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RaikarNiranjan-1486

Thank you for using Microsoft Q&A. Please check this document which explains the details on rebuilding the index. Also mentions the performance gets better with time.

Having said that we would recommend here that you raise a ticket with support as they will need to check your workload and set up to check this issue better. Please share the ticket number and we can get traction for the same.

Please let us know if you need any further help.

Regards
Navtej S


0 Votes 0 ·

@RaikarNiranjan-1486 Were you able to check this and raise the ticket with support.

0 Votes 0 ·

0 Answers