SQL Server Performance Blog where milliseconds matter

Hi Guys, During the last post we saw that data are not physically deleted from the table where you execute a delete statement. Data are marked as deleted only. It is a scheduled task called Ghost cleanup task that physically removes data from the table. We have also seen that we can disable this process via global traceflag number 611. So, if you enable flag 611, the records will no longer be automatically removed from the table. (In reality this is not true  for the heap tables where the traceflag 661 has no effect ) Sure, they are not of those configurations to try in production! But what would that entail? The positive thing is that in the presence of many cancellations the workload of the server should be less. The bad thing is that the disk space is not freed up and that as the tables are physically larger, the table scan should take longer. But let's see if that's true or not with an example!   What happens if you disable the Ghost cleanup task? Before running the exam...

Hi Guys, Since the previous post received a lot of reviews, today we continue with the series " omg I deleted the wrong record! " Then let us immerse ourselves in the dense atmosphere of a server room. This time you have dared too much. You didn't do your nice "begin tran" command before doing that damn delete .. and you deleted the wrong row . In the previous post we saw that we can recover the data. But if you have just made the cancellation, do you have any more hope of recovering the data on the fly?   Enjoy the reading!   Deleted data or Ghost record? The first important fact is that records are not physically deleted when you execute a delete . In fact, for performance reasons when deleting data SQL Server only marks the data as ghost record . Therefore, the deleted row stays on the database. In the row header it will be written that that the row is now a ghost row. ...and if you think about it, this is a very useful mechanism because it prevents us fr...