>Based on the first thread you list, you still get logging unless you use workarounds that endanger backups or database integrity in the event of a failure. Batching deletes 500 or 2000 at a time will reduce logged transactions by that factor (which could be a big win) but at the cost of having to execute the statement 1000 or 4000 times to delete 2 million rows. Have you tested that scenario? Maybe the time is quick enough.
>
>Actually, looking at your post again, if you need to delete 2 million rows every 30 minutes ( i.e. 1800 seconds ) that's over 1000 rows/sec. That implies that somewhere else you're also adding at least that many rows per second.
Yes, that is a scenario that I would like to test. For now, we are at 8 hits per seconds. I delete them every 15 minutes, for those older than 30 days. This is the incoming so this table is the most active in the application and is constantly being used.
>To me that sounds like a lot of continuous IOPs. If that's the case you're probably running on a strong server with a fast disk subsystem and some effort may have already been expended in setting up the server and databases for high performance. You might want to ask whoever did that for their recommendations.
Yes, it is a very powerful environment. I just want to be sure our application is maximized as far as performance.
>- Need to permanently retain certain rows, but periodically delete other rows that become invalidated, or
It is the Hit table. Every 15 minutes, I remove everything older than 30 days. This is receiving some incoming at the same time I delete. I moved it to 15 minutes as once this process was executed at every hour, it was too long. I will see however with removing 2000 records at a time.
>- Want to maintain a log file of, say the 10 million most recent rows (like Windows event logs)? If so then this may actually be a good candidate for row recycling as long as the overhead to do so isn't too much in your environment
The problem with recycling is that the locking mechanism to be sure one of those 8 hits per second select its own record to be reused will end up to be costly. This is at every hit. The average response time is 0.6 second. I do not want to jeopardize that. The recyling mecanism in record to the locking infrastructure will have an impact on that.
