1

We have a large database that has several tables with 10-50 million of rows each. But in reality we just need the data for the past 3 years only. So we created new tables to contain only the latest data. They tables are exactly like the original ones e.g. contains the same indexes on same partition ... same everything.

And everything went perfect. The table records count is now ~10-15 times smaller than the original size. Initial perf. measures showed significant gain, but then we found that some other stored procedures perform worst than previous - now they take 100% more time e.g. from ~2 minutes to ~4.

The table swapping was done via sp_rename.

We rebuilt all the indexes, rebuilt even the statistics, but the effect was actually very small.

Update: we cleared all execution plans via:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

Fortunatelly for me when I get back to the original tables, the problematic Stored Procedures starts to work fast as before. Right now I am comparing the execution plans, but it is pain because those SPs are huge.

Any help will be appreciate.

Kiko
  • 319
  • 1
  • 4
  • 16
  • Did you try clearing the execution plan for those SPs? Perhaps this can give some help? https://stackoverflow.com/questions/30597429/how-to-clear-cache-of-1-stored-procedure-in-sql-server – Culme Oct 19 '21 at 12:16
  • Hello, Yes I did. I actually cleared all executionplans: DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO – Kiko Oct 19 '21 at 12:27
  • 1
    Without seeing the execution plans how can we possibly help? Please share via https://brentozar.com/pastetheplan. Please also show us the relevant table and index definitions – Charlieface Oct 19 '21 at 12:34
  • What you're looking for are row estimates that are wildly disparate with reality. If you have execution plans from before the data drop, or you can restore to a different server and generate those plans, comparing the row estimates for the problem queries will likely give you a lot of information to solve this. – Grant Fritchey Oct 19 '21 at 13:40
  • This is exaclty what I am doing right now. But this is taking lots of time, because execution plan is about ~7 MBs. – Kiko Oct 19 '21 at 14:04

0 Answers0