Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million).

We're working to re-factor it, however as a short term solution, is 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.

share|improve this question
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. –  Martin Smith 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. –  Alex Key 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. –  Martin Smith 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? –  Alex Key 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. –  Martin Smith Jan 24 '13 at 14:11

3 Answers 3

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 disquise. 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, a '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 in 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: is impossible. The bad news is that you have to fix the application now.

share|improve this answer
    
+1 Hadn't considered that issue. –  Martin Smith Jan 24 '13 at 16:55
    
But how do you "fix the application now"? –  Aaron Bertrand 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. –  Alex Key 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). –  Remus Rusanu 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? –  Alex Key Jan 29 '13 at 14:42

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:

CREATE SCHEMA fake   AUTHORIZATION dbo;
CREATE SCHEMA shadow AUTHORIZATION dbo;

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];

BEGIN TRANSACTION;

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

COMMIT TRANSACTION;

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:

http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo


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.

share|improve this answer

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

share|improve this answer
    
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? –  Alex Key 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). –  Simon Osborne 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. –  Alex Key Jan 24 '13 at 15:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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