Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rapid Growth Of Data and Purging Issues Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 1:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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
Post #1496740
Posted Friday, September 20, 2013 2:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 111, Visits: 341
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
Post #1496749
Posted Friday, September 20, 2013 4:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1496775
Posted Friday, September 20, 2013 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1496778
Posted Monday, September 23, 2013 3:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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
Post #1497301
Posted Tuesday, September 24, 2013 10:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 4:48 AM
Points: 57, Visits: 431
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/

Post #1497980
Posted Wednesday, September 25, 2013 3:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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
Post #1498210
Posted Wednesday, September 25, 2013 3:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1498216
Posted Wednesday, September 25, 2013 4:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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.
Post #1498229
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse