February 18, 2009 at 9:59 pm
Hi All,
I have a server with 16 processors, 32 Gb of RAM and about 3500 databases. I believe this is huge number of db.
I have a store procedure which has to drop 30 global temporary table. The db size is about 55MB. Only the dropping of the global temporary tables take about 6-7 secs to execute.
Can this be optimised? Will local temporary tables go faster.... (i believe not)
I have written a test procedure which is as follows:
--CREATE TABLES
declare @i int
declare @sql varchar(200)
declare @name varchar(20)
set @name = '##table'
select @i= 0
while( @i < 50)
begin
set @sql = ('create table '+ @name + convert(varchar(10), @i) + ' (col1 varchar(10) )')
-- set @sql = 'Drop table '+ @name + convert(varchar(10), @i)
print @sql
exec (@sql)
set @i = @i +1
end
I have tried to create global temp tables and drop it (takes 18 sec). However, while creating the local temp table itself, it takes long (18 sec).
Can someone please help/guide me with how can i optimise?
February 19, 2009 at 6:48 am
What is it that you're actually trying to do?
Just so you know, the code you posted ran in 89ms on my desktop (not the fastest machine on earth either).
"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
February 19, 2009 at 8:03 am
This runs incredibly fast on my local machine as well (not remotely fast desktop).
I am guessing with 3500 DB's you may very well be having some contention issues on tempdb (especially if all 3500 DB's are sharing the same tempdb).
How many instances do you have running these databases and how are the tempdb files split out on those instances (all on the same logical drive, all on different drives, etc..).
February 19, 2009 at 8:35 am
Your question is not very clear. Did you check the size of Tempdb and free space on Disk ? Still I don't see any big issue such.
February 19, 2009 at 8:45 am
Why so many DBs?
You surely have a lot of contention on tempdb!
* Noel
February 19, 2009 at 9:25 am
noeld (2/19/2009)
Why so many DBs?You surely have a lot of contention on tempdb!
And everywhere else.
"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
February 19, 2009 at 11:54 am
Grant Fritchey (2/19/2009)
noeld (2/19/2009)
Why so many DBs?You surely have a lot of contention on tempdb!
And everywhere else.
You got that right! 😀
* Noel
February 23, 2009 at 9:45 pm
Truly said. The create statement run within a few milliseconds but the dropping of the global tables takes a lot of time. (If you uncomment the line with the drop statement, and comment the create statement).
I am simply trying to understand why dropping global temporary tables takes more time than creating them.
Thanks for your replies.
A.
February 24, 2009 at 5:33 am
Temporary tables can be dropped automatically as the process that created them leaves scope (the user logs off or breaks the connection). Global temp tables are dropped automatically when all processes that have referenced them leave scope. I don't have hard evidence to back this up because I've never done quite what you're doing, but I suspect that when you issue the drop statement on the global temp table, it's doing some kind of check against existing sessions(and with that many databases I'll bet you have an equally high number of sessions going) to see if anyone is referencing prior to dropping it. If you're not referencing the temp table by multiple sessions, there's no reason to make it a global temp table.
"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
February 24, 2009 at 8:01 am
I agree with the view. If the Global Temp table us not used in multiple sessions, we should use the local temp tables only. Even it is advisable to use the local temp table or derived table "Table" variable instead of Global temp tables.
It will always take time when you want to drop a table as it will check the validy of your statement and any reference made to the object. In case of Global temp table, the scope of validate increases and hence the time taken to validate.
Try to avoid the usage of Global Temp table whever possible.
Refer to http://www.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/ for more information.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply