asked
I need to drop a table or two. Will it affect performance?
I have a database where an automated system creates tables for a data transfer process and then never uses them again. I assume that it should also remove these tables but it would appear that it has stopped doing the tidy up step. As a result I have a few tables that are totally empty and I have the go ahead from the vendor to drop them. Does anyone have any experience of dropping a lot of tables from a database and whether it will bend the server out of shape at all? I think I am going to do them in batches and I will try it in test first, I am just really wondering if anyone else has ever had to drop 1662 tables from a database before?!?!
I have never quite done 1662, but I have dropped several hundred working tables before without any problem.
It should be okay to drop them because they are empty. You probably will have an exclusive lock on the DB a short while
@WilliamD, True, but if it's a very busy OLTP system which creates/drops objects, it may affect the other queries, or you may be blocked as long as the other queries are holding schema locks on the database during the transaction. (depending on how well the code is written)
If you aren't querying them they will not be in cache, so that should not be affected. You may see internal fragmentation of the data file(s) where these tables resided, but that would be expected. You may consider cleaning that up, but it shouldn't be a problem to begin with. I have dropped about 50 tables in a go, plus indexes that belonged to them. The drop, as long as constraints etc. are not there, should be pretty much instantaneous. As the tables are empty, the effects should be minimal though. Go for it on test, then prod - it'll be fine.
so far as I know the tables are used on the day and then never more. Should be simples to remove them I reckon. Great spot on the fragmentation, will be checking that.
I've create a script which creates 1662 create table statements (all with one identity column and nothing more) and one script which creates drop table statements for them all. declare @i int set @i=0 declare @s nvarchar(max) set @s='' while @i<1662 BEGIN -- set @s=@s + N'CREATE TABLE t' + CAST(@i as nvarchar(10)) + ' (id int identity(1,1)) -- GO -- ' set @s=@s + 'DROP TABLE t' + CAST(@i as varchar(10)) + ' GO ' set @i=@i+1 if LEN(@s)>2000 begin print @s set @s='' end END print @s Running on my crappy laptop, they both run in less then five seconds. I guess there's some more overhead to it if you have wider tables, but this at least shows that the magnitude is not that high.
its a busy production server so I would expect it to take a bit longer here. I have a script to put a `waitfor delay` in every 50 tables so it will let normal business carry on. Thanks for testing it! +1
