| Author |
Topic |
|
hari_sql
Starting Member
22 Posts |
 Posted - 2010-03-05 : 02:21:52
|
| Hi all,I need to delete older data from a table (which continiously is getting populated heavily and there are other applications using the latest data in the table). I have a date field in the table and need to delete those rows which are older than the specified date in the query. Sample query: DELETE FROM dbo.WeaterCondition WHERE WeatherPeriodStart < '2010-01-01 00:00:00.000'But the main factor to be considered here is that the delete operation at no cost should affect the performance of the database or slow down the processing of queries used by the applications or put in some sort of lock where by the continious insert operation is tampered, leading to data loss.What is the correct way to do the delete operation (something like a bulk delete) taking into consideration the above factors?Thank you in advance!Thanks & Regards,Hari. |
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2010-03-05 : 08:12:15
|
| Hari,My understanding of DELETE is that it only generates a lock on the pages which it is performing the delete operation. It does not lock the entire table. Remember that a SQL Server DB is not a flat file like an Excel spread sheet. The data is actually stored in "pages."However, any sort of bulk delete operation *IS* going to have a cost. What that will be only you can answer. It will depend on how stressed your server already is, what the I/O subsystem is like, how much processor/memory utilization is already going on, etc. For example, I have tuned my SQL Server to the point where something like this would not really degrade performance enough for my users to notice. 6 months ago when I started at this job it would have been different. It would have caused a major slowdown as processor utilization and disk activity were always high.Is there a way you can schedule this to be done at a time/day when it would have minimal impact? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 09:30:29
|
| also you could have an index on WeatherPeriodStart for speeding up the query if you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
hari_sql
Starting Member
22 Posts |
Posted - 2010-03-07 : 23:34:23
|
| Hi Robert,Thank you very much for the response. The delete operation can be scheduled during off peak hours (when reading the DB tables is minimal, but writing into these tables is a continuous process). Can you also please suggest which is the best way to do the delete process?Thanks & Regards,Hari. |
|
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2010-03-10 : 22:24:33
|
| Sorry for the delay. I imagine your DELET statement is the most straightforward method. Just make sure you backup the DB first.If you are really worried about the performance you could limit the delte to x amount of rows:DELET TOP 100 FROM dbo.WeaterCondition WHERE WeatherPeriodStart < '2010-01-01 00:00:00.000'If this is a big concern for you and you are purging data like this frequently consider partitioned tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-11 : 00:35:21
|
quote:
Originally posted by RobertKaucher
Hari,My understanding of DELETE is that it only generates a lock on the pages which it is performing the delete operation. It does not lock the entire table.
It certainly can lock the entire table. Lock escalation can occur from row level locking all the way up to an exclusive lock on the table. It just depends on what percentage of the data in the table the query is affecting. Starting with SQL 2000, locking starts at the row level. Before that version, the lowest was page level locking.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 03:09:11
|
| My answer would be the same as the previous question you asked:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141061Note that for string-dates you should not useWHERE WeatherPeriodStart < '2010-01-01 00:00:00.000'but instead this format:WHERE WeatherPeriodStart < '20100101'because SQL will treat the second one unambiguously, but the rules for parsing the first one change according to Locale setting on the server, user preferences, language and so on. |
|
|
|
|
Deleting older data from table