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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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 voted first

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

avatar image

Håkan Winther
16.5k 36 45 57

  • 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

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

avatar image

26.2k 18 34 48

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

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
 --    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)) + '
     set @i=@i+1
     if LEN(@s)>2000
         print @s
         set @s=''
 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

avatar image

Magnus Ahlkvist
21.1k 19 39 42

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Apr 04, 2011 at 02:48 AM

Seen: 1584 times

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

Copyright 2016 Redgate Software. Privacy Policy