TempDB operations faster?
-
QA testing script below.
/*
Caution, CREATES TABLE Test64KInsertTemp in current db
Seems to me working on tables in TempDB is always faster.
Try this test script while TempDB is the current DB.
Then try while other than TempDB current.
My Results:
TempDB current
Insert 64K recs into #Temp in 216ms
Insert 64K recs into local Test64KInsertTemp in 220ms
Insert 64K recs into #Temp2 from #Temp in 186ms
Insert 64K recs into local Test64KInsertTemp from #Temp in 173ms
Pubs current
Insert 64K recs into #Temp in 233ms
Insert 64K recs into local Test64KInsertTemp in 296ms
Insert 64K recs into #Temp2 from #Temp in 186ms
Insert 64K recs into local Test64KInsertTemp from #Temp in 233ms
Master current
Insert 64K recs into #Temp in 216ms
Insert 64K recs into local Test64KInsertTemp in 330ms
Insert 64K recs into #Temp2 from #Temp in 186ms
Insert 64K recs into local Test64KInsertTemp from #Temp in 233ms
1. Why are operations like this so much faster (~30-60%) in TempDB?
2. Do others see same results?
3. Can the answer to above be used in other databases to better thier performance?
*/
Declare @Start DateTime
Set NoCount ON
if Object_ID('Test64KInsertTemp') Is Not NULL Begin
Print 'You already have a table named Test64KInsertTemp.'
Print 'This test script will not run'
Return
end
-- Timed insert into #temp
if Object_ID('TempDB..#Temp') Is Not NULL Drop Table #Temp
Select @Start = GetDate()
Select HB.Number * 256 + LB.Number as Number
Into #Temp
From Master.dbo.spt_Values HB
Cross Join Master.dbo.spt_Values LB
Where HB.Type = 'P' and LB.Type = 'P'
and HB.Number Between 0 and 256
Print 'Insert 64K recs into #Temp in ' + Cast(DateDiff(ms, @Start, GetDate()) as Varchar(5)) + 'ms'
-- Timed insert into local Test64KInsertTemp
if Object_ID('dbo.Test64KInsertTemp') Is Not NULL Drop Table dbo.Test64KInsertTemp
Select @Start = GetDate()
Select HB.Number * 256 + LB.Number as Number
Into dbo.Test64KInsertTemp
From Master.dbo.spt_Values HB
Cross Join Master.dbo.spt_Values LB
Where HB.Type = 'P' and LB.Type = 'P'
and HB.Number Between 0 and 256
Print 'Insert 64K recs into local Test64KInsertTemp in ' + Cast(DateDiff(ms, @Start, GetDate()) as Varchar(5)) + 'ms'
-- Timed insert into Test64KInsertTemp from #Temp
if Object_ID('TempDB..#Temp2') Is Not NULL Drop Table #Temp2
Select @Start = GetDate()
Select Number Into #Temp2 From #Temp
Print 'Insert 64K recs into #Temp2 from #Temp in ' + Cast(DateDiff(ms, @Start, GetDate()) as Varchar(5)) + 'ms'
-- Timed insert into local Test64KInsertTemp from #Temp
if Object_ID('dbo.Test64KInsertTemp') Is Not NULL Drop Table dbo.Test64KInsertTemp
Select @Start = GetDate()
Select Number Into dbo.Test64KInsertTemp From #Temp
Print 'Insert 64K recs into local Test64KInsertTemp from #Temp in ' + Cast(DateDiff(ms, @Start, GetDate()) as Varchar(5)) + 'ms'
-- cleanup
if Object_ID('TempDB..#Temp') Is Not NULL Drop Table #Temp
if Object_ID('TempDB..#Temp2') Is Not NULL Drop Table #Temp2
if Object_ID('dbo.Test64KInsertTemp') Is Not NULL Drop Table dbo.Test64KInsertTemp
Once you understand the BITs, all the pieces come together
-
Could your TEMPDB be in the fast I/O RAID disks than your user databases?
-
No, TempDB, Pubs, Master, all in same dir. Just a "stock" SQL install.
What are your results when running the script?
Once you understand the BITs, all the pieces come together -
Interesting issue.
My values are:
TempDB current
Insert 64K recs into #Temp in 220ms
Insert 64K recs into local Test64KInsertTemp in 216ms
Insert 64K recs into #Temp2 from #Temp in 173ms
Insert 64K recs into local Test64KInsertTemp from #Temp in 173ms
user DB current
Insert 64K recs into #Temp in 220ms
Insert 64K recs into local Test64KInsertTemp in 296ms
Insert 64K recs into #Temp2 from #Temp in 170ms
Insert 64K recs into local Test64KInsertTemp from #Temp in 203ms
I do not see the reason for this difference
Bye
Gabor
Bye
Gabor -
SQL doesn't has to recovery tempdb when restarted, so it doesn't need to log everything in the log file as much as in a user db.
So write operations should be faster.
-
Is there a way to create a single user, non logging database that can be used for "temp" operations?
Something like...
Declare @sql varchar(1000)
SET @sql = 'Create Database MyTempDB' + convert(Varchar(10), @@spid)
EXEC (@SQL)
..... then "do faster work with the new database"
Once you understand the BITs, all the pieces come together -
There are some possible answers...
It is possible that the #temp insert did not require and file growth for either the .mdf or .ldf files, but the insert into the local database did. All activity against a database is suspened while SQL Server gets more space from NT and formats it as database pages. This could give a large performance penalty, depending on the number of filegrowth operations performed.
It is also possible that the inserts into #temp went into consecutive pages in TempDB, while the inserts into the local database went into non-consecutive pages spread over many more extents. This will give a small performance penalty.
Likewise, inserts into the Test64KInsertTemp table in TempDB may have been into contiguous pages and without filegrowth, while the inserts into a local database may have have been affected by both issues.
The copy of data from TableA to TableB may have go through a longer code path when the tables are in different databases than when they are in the same DB.
Databases that have their files physically located near the disk edge will get faster performance than databases that have their files located near the disk centre. The performance differential can be over 15%, but this is unlikely to apply to your specific tests.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply