x

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?!?!
more ▼

asked Apr 04, 2011 at 02:48 AM in Default

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

I have never quite done 1662, but I have dropped several hundred working tables before without any problem.
Apr 04, 2011 at 09:42 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
It should be okay to drop them because they are empty. You probably will have an exclusive lock on the DB a short while
more ▼

answered Apr 04, 2011 at 02:54 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

+1 - The lock would be very short lived though.
Apr 04, 2011 at 02:58 AM WilliamD
@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)
Apr 04, 2011 at 04:56 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 04, 2011 at 03:06 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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
Apr 04, 2011 at 03:29 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 04, 2011 at 02:58 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Apr 04, 2011 at 03:30 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1950
x33
x29
x9

asked: Apr 04, 2011 at 02:48 AM

Seen: 1360 times

Last Updated: Apr 04, 2011 at 02:48 AM