0

Seeing slowness in insert/select after truncating a table that has over 6 million rows.

I daily insert 5 to 6 millions records into a table and I was able to insert/select data without any issue for some 7 or 8 days but when the table size went above 10 GB / 30 million rows, there were a few timeout issues.

So I thought of truncating the table daily before data uploading since 1 day's data is enough for me. Now I am seeing extreme slowness after truncating. I am using sql server 2008.

do i need to update table stats? or is it some thing related to ghost rows?

Improve this question
4
  • You should look at lock escalations and blocking if any. Also updating stats would be very good idea since your scenario has huge data change
    – Shanky
    Jul 26, 2014 at 19:35
  • thx shanky , there is no locking / blocking , since only one process uploads also no process is reading data till upload finishes. when i was uploading data with out truncate the process(calculation/upload) would complete in 1hours , now after truncate today it took 6 or 7 hours.
    – mohan
    Jul 26, 2014 at 19:42
  • 3
    When the query is running slow, run sp_WhoIsActive and see what the wait types are for the query. brentozar.com/go/active
    – Brent Ozar
    Jul 26, 2014 at 20:37
  • As others pointed out, truncating basically leaves all statistics and indexes all outdated and fragmented. Also, if your table is basically a dump table for data, you might want to alter it as a heap instead of clustered (which I assume it is). Heaps are ideal for pretty much nothing, except for huge bulk inserts. Either way, to read data from there you'll need to update the statistics for the query planner to have any clue what the data looks like and how it's ordered.
    – Kahn
    Jul 28, 2014 at 8:16

1 Answer 1

Reset to default
0

update statistics tablename. Enjoy.

Improve this answer
1
  • After enabling auto stats update , every thing went normal. from now After truncating big tables i will makes sure table stats get updated if DB auto stats update is disabled.. thanks every one for all your valuable suggestions.
    – mohan
    Jul 28, 2014 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.