May 20, 2010 at 1:30 am
I have two tables. TableA is the table that holds the current records and TableB that holds the new records.
TableA has about 1.5 million records and TableB has about 22 million records. TableA and TableB have an identical structure.
I start a simple select * from TableA and right after that i run
exec sp_rename 'TableA' , 'TableC'
exec sp_rename 'TableB' , 'TableA'
exec sp_rename 'TableC' , 'TableB'.
After this TableA should have the records from TableB and viceversa.
When i run the first time (TableA has 1.5 million records) the names are changed without waiting for the select to finish. The second time (TableA has 22 million records) the rename is blocked by the select.
I have monitor the tempdb and it seems that in both cases there is nothing written there.
There is the same behavior even if there is or there is no clusterd index on the tables.
Can some one explain me what happens and how can I make it work in both cases?
...I use sql server 2008, but the database is in the compatibility mode with 2005.
May 20, 2010 at 6:55 am
When I run this:
SELECT * FROM dbo.a;
exec sp_rename 'A' , 'C';
exec sp_rename 'B' , 'A';
exec sp_rename 'C' , 'B';
It works fine, with no blocking.
Are there multiple connections running this at the same time? That would explain the contention.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2010 at 7:13 am
you have run all the scripts in the same batch...and it will execute the queries in order. The problem appears if you run the select in a different batch.
I saw that on a 2005 server the rename waits for the select to end. So...could it be a 2008 enhancement?
I have also observed that the rename is executed after some time after the select begins. The many are the records, the longer it takes. So could the server load all the records in the memory (i have monitored the tempdb and nothing is written there) and after that it makes the rename?
How could i achieve something like this on 2005?
May 20, 2010 at 7:26 am
I guess I'm still confused by what you're trying to do. In SQL Server 7,2000,2005,2008, (and I'd say 6.5, but I don't remember it that well) if you are trying to rename an object that has active shared locks on it, the rename will have to wait until those shared locks are released.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply