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.
ALTER TABLE ... SWITCH
to replace the old with the new. – Martin Smith Jan 24 '13 at 13:36T
) 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 dropTnew
andTtemp
asT
now contains the new data. – Martin Smith Jan 24 '13 at 14:08