We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million).

We're working to re-factor it. However, as a short term solution, are there any query hints that could help in this situation?

Is there a way of making it so if reads are occurring on the table being truncated/repopulated that it ignores this fact i.e. it'll just read the data before the truncation began?

We're trying to avoid locks (we think).

I realise this isn't a long term solution, but looking into possible solutions.

  • 3
    Sounds like you would be better off populating a new table then when the new version is ready either renaming them or using ALTER TABLE ... SWITCH to replace the old with the new. Jan 24 '13 at 13:36
  • Thanks Martin, that's an interesting idea. I've not heard of that before. Looking at MSDN the receiving table needs to be empty first, but perhaps used with Simon's answer it could be good. Jan 24 '13 at 14:05
  • You would first switch out the old table (T) to a temporary table (Ttemp) to empty it then switch in the new table (Tnew) to the original table (both metadata switches in the same transaction) then drop Tnew and Ttemp as T now contains the new data. Jan 24 '13 at 14:08
  • Ah ok, so a double switch. Good idea. How does this effect reads? Will the reads still go to the original table whilst this is happening? Jan 24 '13 at 14:10
  • 3
    Reads will be blocked for the duration of the switch but as this is a metadata only operation and all the data changes happen in separate transactions this period of blocking should be very short. Jan 24 '13 at 14:11

Any solution proposed, or any solution you could envision for the mater, would suffer from the same issue: coalesce point around the schema modification lock.

Whether you do a truncate, or an alter table ...switch, or sp_rename, alter schema whateverver, it doesn't matter. They're all the same solution in disguise. And all will have a point in time when the table that is being truncated/ switched/ renamed/ transfered must be locked with SCH-M. This is, in theory, an 'instantaneous metadata only operation', but in practice the SCH-M cannot be granted until all the other existing locks on the table are released, which means any query that is already running on the table will have to finish first. Fine, the operation will wait until the existing queries 'drain'. The issue is that no other query can get in until the pending SCH-M is granted and released. This is a well know lock starvation issue mitigation. So all of the sudden all your new queries will freeze until the very last old query finishes, then the truncate/ switch/ transfer occurs, and the new queries finally resume. This is not noticeable if all queries last 1 second, but if the queries are reports/ analysis that last on average minutes the effect is very visible, specially so if you have a long tail.

The good news is that you can stop worrying about how to do what you're trying to do: it is impossible. The bad news is that you have to fix the application now.

  • But how do you "fix the application now"? Jan 25 '13 at 14:59
  • Thanks @Remus a handy shunt back to reality. It's good to know the possible side effects. Would it be possible to use table hints on the reads to help mitigate this - maybe allowing dirty reads (dirty reads are ok for the table's use)? We certainly have to fix the overall problem, but looking for workarounds to by time to a proper refactor. Jan 29 '13 at 14:36
  • 1
    Unfortunately no. The SCH-M is incompatible with everything, including dirty reads (which must acquire at least SCH-S). Jan 29 '13 at 14:38
  • Interesting, thanks for the link @Remus - I've not come across that page before. Did you see Aaron's edited post, what do you think? Jan 29 '13 at 14:42
  • Any switch-a-roo that has DDL (and sp_rename is DDL) will suffer from the SCH-M issue. To pull it off you need to transform the 'switch' from DDL into DML, and thus allow non blocking dirty-read vs. write. This works by creating a table that has one row, with the name of the 'current' data table in it. All reads must first get the current table name (in a dirty read or in snapshot), then go and read the data. The 'switch' creates and populates a new table and then updates this one row. Further reads go to the new table. No blocking occurs during update. Old tables must be garbage collected. Jan 29 '13 at 15:22

Going off of Martin's comment, you could have a second copy of the table that acts as a shadow. First create two schemas to facilitate round robin:


Now in the shadow schema create an identical table that you are currently truncating and re-populating:

CREATE TABLE shadow.whatever(cols);
-- add PK, indexes etc.

Then change your process so that, while users are querying the primary, it:

  • empties the copy
  • populates the empty copy
  • swaps the primary and copy

Sample code:

TRUNCATE TABLE shadow.whatever;

INSERT shadow.whatever([cols]) 
  SELECT [cols] FROM [source];


  ALTER SCHEMA fake TRANSFER     dbo.whatever;
  ALTER SCHEMA dbo  TRANSFER  shadow.whatever;


ALTER SCHEMA shadow TRANSFER fake.whatever;

This essentially swaps out the table under users' noses, but it is instantaneous - like ALTER TABLE / SWITCH, it is just a metadata change, so the transaction will wait until its turn and be done in a matter of microseconds, causing no visible blocking whatsoever (as long as none of your users are running transactions that require schema changes to whatever.

We've done similar things at my previous job, and I go into a lot of detail in this blog post:


With Remus' observation in mind, and assuming that your reporting queries do take a long time, and that you can enforce data access through stored procedures, you could augment this a little by keeping two copies of the data, and alternating between the current "active" table on a schedule that is a bit longer than the longest query typically takes. By the time you populate the backup copy, all of the queries on the current copy should be done. You mark in a table when the backup copy is complete, and the stored procedure checks the table to determine which copy it will use. This will reduce (but not completely eliminate) the possibility for contention around the Sch-M lock, of course at the cost of storing multiple copies of the data and having a slightly more complex process. You would think stats and plans would go to hell but this is already the case in your current approach and the simpler approach I suggested above.


Are you using TRUNCATE or DELETE? If you're using DELETE you could used the WITH (ROWLOCK) hint. We had to do that on an audit table of ours. We keep 3 months of data in there but it's a 24/7 used DB so without the ROWLOCK hint we had too much blocking.



  • Thanks Simon, good idea. How did you find that effected your transaction log? Was it ok? We may have to do this an number of times a day. How many rows were you deleting? Jan 24 '13 at 14:17
  • It had no significant affect on our transaction log but our log is 180 GB anyway. The database is around 1TB in size and we do regular index rebuilds (hence the large log). Jan 24 '13 at 14:41
  • Wowzer, that's a different realm of size of DB compared to ours (tiny in comparison)! But good to know the transaction logs is ok, thanks for the additional detail. Jan 24 '13 at 15:34
  • For this one you probably want to disable lock escalation on the table. SQL Server isn't required to respect the ROWLOCK hint.
    – Joe Obbish
    Feb 18 '17 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.