Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rapid Growth Of Data and Purging Issues


Rapid Growth Of Data and Purging Issues

Author
Message
chandan_jha18
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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
SrcName
SrcName
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 394
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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
Vinod Pal
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 527
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
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search