Rapid Growth Of Data and Purging Issues

Question

Rapid Growth Of Data and Purging Issues

chandan_jha18

SSChampion

Points: 11330
More actions
September 20, 2013 at 1:56 am

#280006

Hi,
I tried a few different things which though work well but needs atleast few minutes of downtime so I am coming to my favorite place to seek opinion.
We have a gaming website which is open 24*7. The data in the tables grow rapidly. The problem is that when I try to delete the data, it does work and in 15 minutes or so, I can delete the data accumulated for over 24 hours but this does hamper transactions and because delete locks down the table, this process although working well does not suit my current requirements. A senior developer tried deleting row by row so that only rowlocks are taken but that is not purging data fast.
If somebody has worked in such an environment before, please give your suggestions and inputs on how to take this further.
Thanks
Chandan Jha

Viewing 9 posts - 1 through 8 (of 8 total)

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

SrcName Default port Points: 1460 More actions · Answer 1

use truncate statement. it has many adventages

Because truncate has not where clause and you need some data, then you can save them in another temp table, truncate base table and restore data.

for example

select * into #temp from TABLENAME where [ SOME CONDITION ]

truncate table TABLENAME

insert into TABLENAME select * From #temp

-----------

But there are some restriction, you can't truncate table that participate in index view, are referenced by foreign key, if table is published by transactional replication e.t.c

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

SrcName (9/20/2013)
use truncate statement. it has many adventages
Because truncate has not where clause and you need some data, then you can save them in another temp table, truncate base table and restore data.

NO. i think Srcname you havent read the OP issue completely. its a case of purging the data.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

chandan_jha18 (9/20/2013)
If somebody has worked in such an environment before, please give your suggestions and inputs on how to take this further.

YOu need to go with batch approach where bunch of rows get deleted . see http://www.sqlservercentral.com/articles/Top/63301/

There could be another factors which make the delettion slow like

--Foreigh key cascade deletion.

--Trigger.

--Purging the data at peak time.

--delete sql must get supported by index.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

Purging data in batches can be considered but considering that the website is a 24*7 environment, there will be locking issues at times plus high CPU and log writes.

I am mulling over the concept of partitioning where data can be divided into partitions and each partition can be operated upon without affecting the other partitions. If somebody has better suggestions, please let me know. There has to have a way of achieving this because a lot of websites have to work without downtime and this being my first venture into such an application where not even 15 minutes of windows is there, I am looking for expert suggestions on how to tackle this.

Thanks

Chandan

Vinod Pal Old Hand Points: 345 More actions · Answer 5

Read this article, I have the same dilemma (my boss calls it an opportunity) and will be looking to partition the 'log' tables by date, once partitioned then you can use the partition features to switch data out and be a hero! 😉

https://www.simple-talk.com/sql/t-sql-programming/painless-management-of-a-logging-table-in-sql-server/

chandan_jha18 SSChampion Points: 11330 More actions · Answer 6

Vinod Pal (9/24/2013)
Read this article, I have the same dilemma (my boss calls it an opportunity) and will be looking to partition the 'log' tables by date, once partitioned then you can use the partition features to switch data out and be a hero! 😉
https://www.simple-talk.com/sql/t-sql-programming/painless-management-of-a-logging-table-in-sql-server/

I am not that lucky. As soon as people find out that enterprise license is costlier than standard, they want me to 'fix it' in 2 word email by staying in standard edition.

Thanks

Chandan

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

chandan_jha18 (9/23/2013)
Purging data in batches can be considered but considering that the website is a 24*7 environment, there will be locking issues at times plus high CPU and log writes.

i will contradict here (as per my knowledge ) batch processing helps to manage the the resources (definitely cache not sure about CPU ) plus log.

smaller chunk will get updated; using less log and resources.

See link http://www.tek-tips.com/faqs.cfm?fid=3141

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandan_jha18 SSChampion Points: 11330 More actions · Answer 8

Bhuvnesh (9/25/2013)
chandan_jha18 (9/23/2013)
Purging data in batches can be considered but considering that the website is a 24*7 environment, there will be locking issues at times plus high CPU and log writes.
i will contradict here (as per my knowledge ) batch processing helps to manage the the resources (definitely cache not sure about CPU ) plus log.
smaller chunk will get updated; using less log and resources.
See link http://www.tek-tips.com/faqs.cfm?fid=3141

I agree that it is a nice solution but not for my database where more than 8-9k users are playing game concurrently. The delete process does make them wait and as an end user I won't like to get delays in online internet gaming where I am stuck for a second and keep thinking if it is me or everybody else who faced this.