0

We recently applied index compression to one of our critical tables that contains a substantial amount of data using the following query:

ALTER INDEX IX_OrderItem_ProductId ON dbo.[OrderItem] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE, MAXDOP = 8);

However, after enabling compression, we observed a noticeable decline in performance. To mitigate this, we reverted the compression using the following command:

ALTER INDEX IX_OrderItem_ProductId ON dbo.[OrderItem] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE, MAXDOP = 8);

While this did offer some improvement, we are still experiencing higher-than-normal CPU usage, and many queries that previously ran smoothly are now timing out.

Has anyone encountered a similar issue or have insights on why CPU usage remains elevated even after compression has been disabled? Any guidance or potential troubleshooting steps would be greatly appreciated.

Thank you!

Improve this question

1 Answer 1

Reset to default
2

After rebuilding your index the plans, related to this index, are flushed from the cache and some statistics are also rebuild. It is possible that you have now a complete other execution plan. If possible I would suggest that you compare the old plan with the new one.

Improve this answer
1
  • Hi Ludo, Thank you for your response. We tried below and this seems to be working fine so far. 1. DBCC FREEPROCCACHE 2. PURGE QUERY STORE 3. REBUILD INDEXES 4. UPDATE STATISTICS FOR KEY TABLES Commented Nov 5, 2024 at 15:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.