| Author |
Slow tempdb in Sql2005
|
|
|
| Hi,
I have some performance problems with Sql2005.
So I wrote a simple stored procedure with the folowing actions:
- create new table
- insert 1.000.000 records
- update that 1.000.000 records
- drop the new table
The required time to execute this sp depends on the database.
- a new database 5 minuten (average cpu 80%)
- the tempdb 35 minutes (average cpu 10%)
I tried several setting:
- update statistics
- simple recovery model
- boost cpu
I can't explain why the tempdb is that slow and is only using 10%
cpu?!?!
Does anyone has an explanation why the tempdb is behaving like this???
Regards...
| |
| Uri Dimant 2006-11-06, 5:13 am |
| pl
Is it restored database from a perevious version?
Do you run
ALLOW_SNAPSHOT_ISOLA
TION {ON | OFF }
| READ_COMMITTED_SNAPS
HOT {ON | OFF } on the database level?
Run this and take a look at NumberWrites column changes the value up
dramaticaly
select * from ::fn_virtualfilestat
s( 2, 2 )
union all
select * from ::fn_virtualfilestat
s( 2, 1 )
"pl" <pjlatten@gmail.com> wrote in message
news:1162806849.027417.204960@h48g2000cwc.googlegroups.com...
> Hi,
>
> I have some performance problems with Sql2005.
>
> So I wrote a simple stored procedure with the folowing actions:
> - create new table
> - insert 1.000.000 records
> - update that 1.000.000 records
> - drop the new table
>
> The required time to execute this sp depends on the database.
> - a new database 5 minuten (average cpu 80%)
> - the tempdb 35 minutes (average cpu 10%)
>
> I tried several setting:
> - update statistics
> - simple recovery model
> - boost cpu
>
> I can't explain why the tempdb is that slow and is only using 10%
> cpu?!?!
> Does anyone has an explanation why the tempdb is behaving like this???
>
> Regards...
>
| |
| Tibor Karaszi 2006-11-06, 5:13 am |
| Perhaps you have autogrow occurring in the tempdb database?
Perhaps you don't have the same physical disk layout for the user database compared to tempdb (RAID
level, data-log separation etc)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1162806849.027417.204960@h48g2000cwc.googlegroups.com...
> Hi,
>
> I have some performance problems with Sql2005.
>
> So I wrote a simple stored procedure with the folowing actions:
> - create new table
> - insert 1.000.000 records
> - update that 1.000.000 records
> - drop the new table
>
> The required time to execute this sp depends on the database.
> - a new database 5 minuten (average cpu 80%)
> - the tempdb 35 minutes (average cpu 10%)
>
> I tried several setting:
> - update statistics
> - simple recovery model
> - boost cpu
>
> I can't explain why the tempdb is that slow and is only using 10%
> cpu?!?!
> Does anyone has an explanation why the tempdb is behaving like this???
>
> Regards...
>
| |
|
| I'm using SQL 2005 express, the tempdb is created after installation of
sql 2005 express.
| |
|
| The system has only 1 harddisk; I tested the io with SQLIO tool. It
looks like the harddisk isn't the problem.
| |
|
|
|
| autogrow for the tempdb (data+log): by 10% unrestricted grow
| |
|
|
| Tony Rogerson 2006-11-07, 7:12 pm |
| Show us the script you are using to do this.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"pl" <pjlatten@gmail.com> wrote in message
news:1162806849.027417.204960@h48g2000cwc.googlegroups.com...
> Hi,
>
> I have some performance problems with Sql2005.
>
> So I wrote a simple stored procedure with the folowing actions:
> - create new table
> - insert 1.000.000 records
> - update that 1.000.000 records
> - drop the new table
>
> The required time to execute this sp depends on the database.
> - a new database 5 minuten (average cpu 80%)
> - the tempdb 35 minutes (average cpu 10%)
>
> I tried several setting:
> - update statistics
> - simple recovery model
> - boost cpu
>
> I can't explain why the tempdb is that slow and is only using 10%
> cpu?!?!
> Does anyone has an explanation why the tempdb is behaving like this???
>
> Regards...
>
| |
|
| Here's the script:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_PerformanceTest AS
DECLARE @DATE1 AS DATETIME
DECLARE @DATE2 AS DATETIME
DECLARE @DATE3 AS DATETIME
DECLARE @LOOP AS INT
DECLARE @MAX AS INT
--SET NR OF LOOP
SET @MAX = 1000000
--CREATE TABLE
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;performancetest]
') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].& #91;performancetest]
CREATE TABLE [dbo].& #91;performancetest]
(
[testid] [int] IDENTITY (1, 1) NOT NULL ,
[testvalue] [varchar] (50) COLLATE Latin1_General_CI_AS
NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].& #91;performancetest]
WITH NOCHECK ADD
CONSTRAINT & #91;PK_performancete
st] PRIMARY KEY CLUSTERED
(
[testid]
) ON [PRIMARY]
SET @DATE1 = GETDATE()
--LOOP INSERT
SET @LOOP = 0
WHILE @LOOP < @MAX
BEGIN
SET @LOOP = @LOOP + 1
INSERT INTO & #91;performancetest]
VALUES (CAST(@LOOP AS VARCHAR(50)))
END
SET @DATE2 = GETDATE()
--LOOP UPDATE
SET @LOOP = 0
WHILE @LOOP < @MAX
BEGIN
SET @LOOP = @LOOP + 1
UPDATE & #91;performancetest]
SET [testvalue] = 'UPDATE' WHERE testid =
@LOOP
END
SET @DATE3 = GETDATE()
--CHECK
SET @LOOP = (SELECT COUNT(*) FROM & #91;performancetest]
)
PRINT @LOOP
--TIMES
PRINT CAST(DATEDIFF(S,@DAT
E1,@DATE2) AS VARCHAR(10)) + ' SECONDS FOR
INSERT'
PRINT CAST(DATEDIFF(S,@DAT
E2,@DATE3) AS VARCHAR(10)) + ' SECONDS FOR
UPDATE'
--DELETE TABLE
drop table [dbo].& #91;performancetest]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
| |
| Tibor Karaszi 2006-11-07, 7:12 pm |
| Perhaps I should expand on my thoughts. My theory is that for the tempdb, SQL Server has to grow the
tempdb database files during the execution of the job/batch, where for your other database the files
are big enough to handle the needed space allocations. Growing a file is a time consuming operation,
and that can contribute to why running the job in tempdb takes longer time compared to running it in
the other database. In other words: try to expend the tempdb database to a comfortable size, run the
job and check if tempdb has grown during execution of your job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1162811929.022684.15450@i42g2000cwa.googlegroups.com...
> autogrow for the tempdb (data+log): by 10% unrestricted grow
>
| |
|
| Tibor,
That did the job;
- I increased the init size of the tempdb to 100Mb (data+log); the sp
was ready in just 2 minutes
- After shrink the tempdb I tried again; it took 36 minutes
I could not imagine it would be that much difference.
Thanks very much...
| |
| Tony Rogerson 2006-11-07, 7:12 pm |
| On the autogrow SQL Server needs to initialise the transaction log and then
write to the file; autogrow serialises writing to the log; I'm suprised it
made such a difference myself - i'll have a play.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"pl" <pjlatten@gmail.com> wrote in message
news:1162845589.545489.203930@k70g2000cwa.googlegroups.com...
> Tibor,
>
> That did the job;
>
> - I increased the init size of the tempdb to 100Mb (data+log); the sp
> was ready in just 2 minutes
> - After shrink the tempdb I tried again; it took 36 minutes
>
> I could not imagine it would be that much difference.
>
> Thanks very much...
>
| |
|
|
|
| The story continues:
I tried another thing:
- make a new database with the same settings as the tempdb
- run the sp
- it takes about 6 minutes to run (tempdb only 2 minutes)
Why is the tempdb 3 times faster than another database with the same
settings???
Regards...
| |
| Tibor Karaszi 2006-11-08, 5:16 am |
| > Why is the tempdb 3 times faster than another database with the same
> settings???
There is less logging in tempdb. SQL Server doesn't have to log REDO operations for tempdb, as it is
re-created every time SQL Server starts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1162976698.647510.132230@f16g2000cwb.googlegroups.com...
> The story continues:
>
> I tried another thing:
> - make a new database with the same settings as the tempdb
> - run the sp
> - it takes about 6 minutes to run (tempdb only 2 minutes)
>
> Why is the tempdb 3 times faster than another database with the same
> settings???
>
> Regards...
>
| |
|
| Tibor,
Ok, thanks very much.
I started these tests because I have a basis problem with a new
installation of SqlServer2005 at a customer.
His Dell server (4x Xeon | 3 Ghz CPU | 3 Gb RAM):
- sp on tempdb (on local scsi drive): 2 minutes
- sp on empty database (on local scsi drive): 10 minutes
- sp on empty database (on san): 30 minutes!!!!!
When I test the IO with SQLIO these are the average values of basic io
tests:
- lokal drive: 40.828 IOs/sec / 1.482 Mb/sec
- san drive: 89.289 IOs/sec / 2.498 Mb/sec
On my workstation (AMD 3200+ | 1 Gb RAM):
- sp on tempdb (just a basic hd): 2 minutes
- sp on empty database (just a basic hd): 6 minutes
When I test the IO with SQLIO these are the average values of basic io
tests:
- lokal drive: 15.138 IOs/sec / 639 Mb/sec
So the io/sytem on the server is much faster.
But the sql performance is worse...
Any idea???
| |
| Tibor Karaszi 2006-11-08, 7:12 pm |
| I assume you had enough space when you ran all these tests, so that autogrow is no longer a factor.
One possible reason for the local machine to be a bit faster compared to SCASI on the server is
write caching enabled by default, and not on the server.
As for the SAN, I guess you have talk to the SAN vendor about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1162979111.525096.205630@h54g2000cwb.googlegroups.com...
> Tibor,
>
> Ok, thanks very much.
>
> I started these tests because I have a basis problem with a new
> installation of SqlServer2005 at a customer.
>
> His Dell server (4x Xeon | 3 Ghz CPU | 3 Gb RAM):
> - sp on tempdb (on local scsi drive): 2 minutes
> - sp on empty database (on local scsi drive): 10 minutes
> - sp on empty database (on san): 30 minutes!!!!!
> When I test the IO with SQLIO these are the average values of basic io
> tests:
> - lokal drive: 40.828 IOs/sec / 1.482 Mb/sec
> - san drive: 89.289 IOs/sec / 2.498 Mb/sec
>
> On my workstation (AMD 3200+ | 1 Gb RAM):
> - sp on tempdb (just a basic hd): 2 minutes
> - sp on empty database (just a basic hd): 6 minutes
> When I test the IO with SQLIO these are the average values of basic io
> tests:
> - lokal drive: 15.138 IOs/sec / 639 Mb/sec
>
> So the io/sytem on the server is much faster.
> But the sql performance is worse...
>
> Any idea???
>
| |
|
| All the databases I created with an initial size of 100Mb; during the
test they didn't grow so autogrow should be no factor.
I haven't contact the SAN vendor because I want to be 100% sure about
my testing before I contact them.
Question 1:
How can I enable/disable/view the caching settings on the server???
Question 2:
How can you explain the good performance with the SQLIO tool; that
would assume the IO is fast enough???
Question 3:
How can I test the caching; is there a tool???
| |
| Tibor Karaszi 2006-11-08, 7:12 pm |
| > Question 1:
> How can I enable/disable/view the caching settings on the server???
Probably in the hardware setup, possibly in the BIOS setup. Ask the HW vendor or the techie that
handles the machine.
> Question 2:
> How can you explain the good performance with the SQLIO tool; that
> would assume the IO is fast enough???
My guess is that you have a different I/O characteristics when you run your job compared to the
SQLIO test. Differences can be read/write ratio, sequential vs. random access etc. I haven't used
SQLIO, but I would assume that you can configure the I/O characteristics you want to test.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1163002057.695664.165700@i42g2000cwa.googlegroups.com...
> All the databases I created with an initial size of 100Mb; during the
> test they didn't grow so autogrow should be no factor.
>
> I haven't contact the SAN vendor because I want to be 100% sure about
> my testing before I contact them.
>
> Question 1:
> How can I enable/disable/view the caching settings on the server???
>
> Question 2:
> How can you explain the good performance with the SQLIO tool; that
> would assume the IO is fast enough???
>
> Question 3:
> How can I test the caching; is there a tool???
>
| |
|
| I think I found an explanation;
The 1000000 inserts in the beginning of my test sp result in a lot of
512 Bytes written to the ldf file (thanks to sysinternal filemon).
The san is slower than the local drive for these small packages, above
4K Bytes the san is getting faster (thanks to sqlio and iomonitor).
| |
| Tibor Karaszi 2006-11-09, 7:13 pm |
| Thanks for sharing your findings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"pl" <pjlatten@gmail.com> wrote in message
news:1163088110.505203.220670@i42g2000cwa.googlegroups.com...
>I think I found an explanation;
>
> The 1000000 inserts in the beginning of my test sp result in a lot of
> 512 Bytes written to the ldf file (thanks to sysinternal filemon).
>
> The san is slower than the local drive for these small packages, above
> 4K Bytes the san is getting faster (thanks to sqlio and iomonitor).
>
|
|
|
|