Slow deletes

Question

Slow deletes

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

MVDBA (Mike Vessey) wrote:

one of the things we were taught(more than 20 years ago) was how to do capacity planning, how many rows per page, work out what was out of row etc etc - it seems to be a missing skill these days.

Heh... one of many missing skills.

MVDBA (Mike Vessey) wrote:

no one seems to care because we "just buy more disks"

I've learned to never turn down hardware. 😀 But, on that same note, I shoot myself in the head in that area because I do exercise the skill you speak of and train others to understand the underlying advantages when it comes to performance and so we very infrequently have to buy extra disk space. The good part is, I work with a group of people that actually "get it" and they'll actually approach me during the design phases of things now. A lot of them now come with table designs with the lessons they've learned already built in. They're really good folks that are getting really good at it.

MVDBA (Mike Vessey) wrote:

we have a project on the go at the minute (not mine) and it's "code first" the database design it put out will not last a week

We all know what happens after such designs make it to production. 🙁 All I can do is offer my sincere condolences and, at the same time, congratulate you on the job security they're building in for you. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

jeff- you just made me laugh out loud and spill my tea all over my new keyboard about job security. I was chatting to a guy I work with last night.. I think I was a bit insecure (departmental moves etc) and he was saying " don't worry, we need a bloody DBA, as much as we don't like them" - it was a joke

MVDBA

vsamantha35 SSChampion Points: 11529 More actions · Answer 3

John Mitchell-245523 wrote:

Sam
Space from deleted data is not automatically released to the operating system. You have to shrink the data files to get the space back.
Please post the code that your App team is using to do the deletes.
John

Hi John,

Thanks for the response. I can clearly 500 mb free space but the shrinkfile command doesnt release the space.

I will try to get the code from app team (if possible).

Sam

vsamantha35 SSChampion Points: 11529 More actions · Answer 4

Yes. I tried the shrinkfile but it doesnt release free space which is around 500MB. I dont know why!!

vsamantha35 SSChampion Points: 11529 More actions · Answer 5

vsamantha35

SSChampion

Points: 11529

December 4, 2019 at 6:38 pm

#3704448

I am using shringfile with truncateonly option.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

vsamantha35 wrote:

I am using shrink file with truncate only option.

That will only clear space if it happens to be free at the end of the file. As the data you have deleted will be spread across the whole file it won't work. You need to shrink without the "truncate only" option which will move data from the end of the datafile to the unused parts of the datafile then knock the end off. But this can have the effect of reducing the performance of the database by fragmenting tables and indexes so unless you have a good SAN drive it might be better to migrate the data to another datafile then shrink the original file.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

vsamantha35 wrote:

Yes. I tried the shrinkfile but it doesnt release free space which is around 500MB. I dont know why!!

Ok... I'm humbled. NICE CALL MIKE!!! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

MVDBA (Mike Vessey) wrote:

jeff- you just made me laugh out loud and spill my tea all over my new keyboard about job security. I was chatting to a guy I work with last night.. I think I was a bit insecure (departmental moves etc) and he was saying " don't worry, we need a bloody DBA, as much as we don't like them" - it was a joke

Gosh... I hope it's not a joke... 😀 Everyone should know by now that the best medicine tastes the worst. 😀 😀 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Sam, I hope you didn't miss the following because that SHRINK FILE stuff isn't going to solve your slow deletes.

https://www.sqlservercentral.com/forums/topic/slow-deletes#post-3704281

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

Jeff Moden wrote:

vsamantha35 wrote:
Yes. I tried the shrinkfile but it doesnt release free space which is around 500MB. I dont know why!!
Ok... I'm humbled. NICE CALL MIKE!!! 😀

I got one right for a change...? amazing - it's been a while 🙂

MVDBA

vsamantha35 SSChampion Points: 11529 More actions · Answer 11

Thanks everyone for sharing your valuable input.

Finally, we move the data into a separate table. We retained some which is required in the newly created table and drop the existing table.

App team agreed for it.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 12

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

December 6, 2019 at 8:49 am

#3705259

just make sure the new table is not a heap 🙂

MVDBA