Hi,
I have a problem explaining why a tempdb (#) table is 4
to 5 times faster than a local (user database) table.
To illustrate the performance differences I have the
following script:
================================================== =====
USE TEMPDB
GO
CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1
INSERT #TEST_INSERT_20050211 ( LIG , DES )
VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec,DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
Server_Base
SET NOCOUNT OFF
GO
DROP TABLE #TEST_INSERT_20050211
GO
-- second test
USE Northwind
GO
DROP TABLE TEST_INSERT_20050211
GO
CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL , )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1 INSERT
TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec,DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
Server_Base
SET NOCOUNT OFF
GO
================================================== ====
The differences are 2(or 3) secs vs. 10 secs!
I turned Physical Disk Perf Counters on and found out
that, on the tempdb example, the disk activity is very,
very small. For the local (user database) table, the disk
activity is consequent with the differences in the
execution time.
One of our clients has a real life process that takes 35
minutes to finish if he creates his table in tempdb and
almost 5 hours with a local table.
I would need to >know why< this happens to be able to
explain, because he insists this difference of execution
time comes from a bad parameter in SQL Server! We have
already tested on three different servers and the results
always differ in the same way.
Could anyone explain this?
Thanks a lot in advance!
Flor
I wouldn't expect such a big difference, but for temp object in tempdb, REDO operations are not
logged to the transaction log.
Do you have tempdb on same drive etc as the northwind database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in message
news:159001c5104c$264d82a0$a401280a@.phx.gbl...
> Hi,
> I have a problem explaining why a tempdb (#) table is 4
> to 5 times faster than a local (user database) table.
> To illustrate the performance differences I have the
> following script:
> ================================================== =====
> USE TEMPDB
> GO
> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1
> INSERT #TEST_INSERT_20050211 ( LIG , DES )
> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> DROP TABLE #TEST_INSERT_20050211
> GO
> -- second test
> USE Northwind
> GO
> DROP TABLE TEST_INSERT_20050211
> GO
> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL , )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1 INSERT
> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
> (varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> ================================================== ====
> The differences are 2(or 3) secs vs. 10 secs!
> I turned Physical Disk Perf Counters on and found out
> that, on the tempdb example, the disk activity is very,
> very small. For the local (user database) table, the disk
> activity is consequent with the differences in the
> execution time.
> One of our clients has a real life process that takes 35
> minutes to finish if he creates his table in tempdb and
> almost 5 hours with a local table.
> I would need to >know why< this happens to be able to
> explain, because he insists this difference of execution
> time comes from a bad parameter in SQL Server! We have
> already tested on three different servers and the results
> always differ in the same way.
> Could anyone explain this?
> Thanks a lot in advance!
> Flor
>
|||Yes, they're on the same drive on my XP office computer
(where I tested).
As for the server my client works on (the big long
process) his user database and tempdb are on two
different drives (different controllers). Tempdb on a
RAID1 and the user database on a RAID5.
Thanks
Flor
>--Original Message--
>I wouldn't expect such a big difference, but for temp
object in tempdb, REDO operations are not
>logged to the transaction log.
>Do you have tempdb on same drive etc as the northwind
database?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
message[vbcol=seagreen]
>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
Convert[vbcol=seagreen]
disk[vbcol=seagreen]
35[vbcol=seagreen]
execution[vbcol=seagreen]
results
>
>.
>
|||OK, so REDO can still explain part of the difference. On yours client machine, RAID 1 vs RAID 5 can
also explain some difference. I'm still surprised that you see such a difference on the machine
where you have on same drive, I wouldn't expect the less logging in tempdb have such big impact.
Have you checked the execution plans?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <anonymous@.discussions.microsoft.com> wrote in message
news:064a01c51051$15f01c60$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, they're on the same drive on my XP office computer
> (where I tested).
> As for the server my client works on (the big long
> process) his user database and tempdb are on two
> different drives (different controllers). Tempdb on a
> RAID1 and the user database on a RAID5.
> Thanks
> Flor
> object in tempdb, REDO operations are not
> database?
> message
> Convert
> disk
> 35
> execution
> results
|||It's possible that the difference has to do with the transaction logging
involved. RAID 1 is fast at writing. RAID 5 is the slowest
implementation possible (and many DBAs these days recommend staying away
from RAID 5 altogether). You are inserting 30,000 rows. For tempdb, you
are on the RAID 1 array and running in simple recovery model. So you
have pretty fast writing and auto-truncation of the log file as needed.
For the use database, you are on a RAID 5 array (and I assume the t-log
is on the same array, which is not good). That means very slow writes
and the possiblity of auto-grow operations, which are also slow. Make
sure that AutoShrink is not on in the user database.
You should consider moving the t-log for the user database to the RAID 1
array and test again.
David Gugick
Imceda Software
www.imceda.com
|||I tried to but memory ressources began getting lower and
lower and the query was already taking more than 5
minutes, I had to stop it.
I tried to see the Execution Plan through Profiler an I
would say that for each loop in the while cycle it
generated a plan, and the ressources were also getting
lower.
Flor
>--Original Message--
>OK, so REDO can still explain part of the difference. On
yours client machine, RAID 1 vs RAID 5 can
>also explain some difference. I'm still surprised that
you see such a difference on the machine
>where you have on same drive, I wouldn't expect the less
logging in tempdb have such big impact.
>Have you checked the execution plans?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:064a01c51051$15f01c60$a501280a@.phx.gbl...
is 4[vbcol=seagreen]
================================================== =====[vbcol=seagreen]
AS[vbcol=seagreen]
================================================== ====[vbcol=seagreen]
very,[vbcol=seagreen]
and[vbcol=seagreen]
have
>
>.
>
Showing posts with label tempdb. Show all posts
Showing posts with label tempdb. Show all posts
Wednesday, March 28, 2012
Performance differences local vs. tempdb table
Hi,
I have a problem explaining why a tempdb (#) table is 4
to 5 times faster than a local (user database) table.
To illustrate the performance differences I have the
following script:
======================================================= USE TEMPDB
GO
CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1
INSERT #TEST_INSERT_20050211 ( LIG , DES )
VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
Server_Base
SET NOCOUNT OFF
GO
DROP TABLE #TEST_INSERT_20050211
GO
-- second test
USE Northwind
GO
DROP TABLE TEST_INSERT_20050211
GO
CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL , )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1 INSERT
TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
Server_Base
SET NOCOUNT OFF
GO
======================================================
The differences are 2(or 3) secs vs. 10 secs!
I turned Physical Disk Perf Counters on and found out
that, on the tempdb example, the disk activity is very,
very small. For the local (user database) table, the disk
activity is consequent with the differences in the
execution time.
One of our clients has a real life process that takes 35
minutes to finish if he creates his table in tempdb and
almost 5 hours with a local table.
I would need to >know why< this happens to be able to
explain, because he insists this difference of execution
time comes from a bad parameter in SQL Server! We have
already tested on three different servers and the results
always differ in the same way.
Could anyone explain this?
Thanks a lot in advance!
FlorI wouldn't expect such a big difference, but for temp object in tempdb, REDO operations are not
logged to the transaction log.
Do you have tempdb on same drive etc as the northwind database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in message
news:159001c5104c$264d82a0$a401280a@.phx.gbl...
> Hi,
> I have a problem explaining why a tempdb (#) table is 4
> to 5 times faster than a local (user database) table.
> To illustrate the performance differences I have the
> following script:
> =======================================================> USE TEMPDB
> GO
> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1
> INSERT #TEST_INSERT_20050211 ( LIG , DES )
> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> DROP TABLE #TEST_INSERT_20050211
> GO
> -- second test
> USE Northwind
> GO
> DROP TABLE TEST_INSERT_20050211
> GO
> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL , )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1 INSERT
> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
> (varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> ======================================================> The differences are 2(or 3) secs vs. 10 secs!
> I turned Physical Disk Perf Counters on and found out
> that, on the tempdb example, the disk activity is very,
> very small. For the local (user database) table, the disk
> activity is consequent with the differences in the
> execution time.
> One of our clients has a real life process that takes 35
> minutes to finish if he creates his table in tempdb and
> almost 5 hours with a local table.
> I would need to >know why< this happens to be able to
> explain, because he insists this difference of execution
> time comes from a bad parameter in SQL Server! We have
> already tested on three different servers and the results
> always differ in the same way.
> Could anyone explain this?
> Thanks a lot in advance!
> Flor
>|||Yes, they're on the same drive on my XP office computer
(where I tested).
As for the server my client works on (the big long
process) his user database and tempdb are on two
different drives (different controllers). Tempdb on a
RAID1 and the user database on a RAID5.
Thanks
Flor
>--Original Message--
>I wouldn't expect such a big difference, but for temp
object in tempdb, REDO operations are not
>logged to the transaction log.
>Do you have tempdb on same drive etc as the northwind
database?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
message
>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
>> =======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> ======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is very,
>> very small. For the local (user database) table, the
disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
35
>> minutes to finish if he creates his table in tempdb and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
execution
>> time comes from a bad parameter in SQL Server! We have
>> already tested on three different servers and the
results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>
>.
>|||OK, so REDO can still explain part of the difference. On yours client machine, RAID 1 vs RAID 5 can
also explain some difference. I'm still surprised that you see such a difference on the machine
where you have on same drive, I wouldn't expect the less logging in tempdb have such big impact.
Have you checked the execution plans?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <anonymous@.discussions.microsoft.com> wrote in message
news:064a01c51051$15f01c60$a501280a@.phx.gbl...
> Yes, they're on the same drive on my XP office computer
> (where I tested).
> As for the server my client works on (the big long
> process) his user database and tempdb are on two
> different drives (different controllers). Tempdb on a
> RAID1 and the user database on a RAID5.
> Thanks
> Flor
>>--Original Message--
>>I wouldn't expect such a big difference, but for temp
> object in tempdb, REDO operations are not
>>logged to the transaction log.
>>Do you have tempdb on same drive etc as the northwind
> database?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
> message
>>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
>> =======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
> Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> ======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is very,
>> very small. For the local (user database) table, the
> disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
> 35
>> minutes to finish if he creates his table in tempdb and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
> execution
>> time comes from a bad parameter in SQL Server! We have
>> already tested on three different servers and the
> results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>>
>>.|||It's possible that the difference has to do with the transaction logging
involved. RAID 1 is fast at writing. RAID 5 is the slowest
implementation possible (and many DBAs these days recommend staying away
from RAID 5 altogether). You are inserting 30,000 rows. For tempdb, you
are on the RAID 1 array and running in simple recovery model. So you
have pretty fast writing and auto-truncation of the log file as needed.
For the use database, you are on a RAID 5 array (and I assume the t-log
is on the same array, which is not good). That means very slow writes
and the possiblity of auto-grow operations, which are also slow. Make
sure that AutoShrink is not on in the user database.
You should consider moving the t-log for the user database to the RAID 1
array and test again.
David Gugick
Imceda Software
www.imceda.com|||I tried to but memory ressources began getting lower and
lower and the query was already taking more than 5
minutes, I had to stop it.
I tried to see the Execution Plan through Profiler an I
would say that for each loop in the while cycle it
generated a plan, and the ressources were also getting
lower.
Flor
>--Original Message--
>OK, so REDO can still explain part of the difference. On
yours client machine, RAID 1 vs RAID 5 can
>also explain some difference. I'm still surprised that
you see such a difference on the machine
>where you have on same drive, I wouldn't expect the less
logging in tempdb have such big impact.
>Have you checked the execution plans?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:064a01c51051$15f01c60$a501280a@.phx.gbl...
>> Yes, they're on the same drive on my XP office computer
>> (where I tested).
>> As for the server my client works on (the big long
>> process) his user database and tempdb are on two
>> different drives (different controllers). Tempdb on a
>> RAID1 and the user database on a RAID5.
>> Thanks
>> Flor
>>--Original Message--
>>I wouldn't expect such a big difference, but for temp
>> object in tempdb, REDO operations are not
>>logged to the transaction log.
>>Do you have tempdb on same drive etc as the northwind
>> database?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
>> message
>>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table
is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
=======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]'
AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
>> Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>>
======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is
very,
>> very small. For the local (user database) table, the
>> disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
>> 35
>> minutes to finish if he creates his table in tempdb
and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
>> execution
>> time comes from a bad parameter in SQL Server! We
have
>> already tested on three different servers and the
>> results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>>
>>.
>
>.
>
I have a problem explaining why a tempdb (#) table is 4
to 5 times faster than a local (user database) table.
To illustrate the performance differences I have the
following script:
======================================================= USE TEMPDB
GO
CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1
INSERT #TEST_INSERT_20050211 ( LIG , DES )
VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
Server_Base
SET NOCOUNT OFF
GO
DROP TABLE #TEST_INSERT_20050211
GO
-- second test
USE Northwind
GO
DROP TABLE TEST_INSERT_20050211
GO
CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL , )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1 INSERT
TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
Server_Base
SET NOCOUNT OFF
GO
======================================================
The differences are 2(or 3) secs vs. 10 secs!
I turned Physical Disk Perf Counters on and found out
that, on the tempdb example, the disk activity is very,
very small. For the local (user database) table, the disk
activity is consequent with the differences in the
execution time.
One of our clients has a real life process that takes 35
minutes to finish if he creates his table in tempdb and
almost 5 hours with a local table.
I would need to >know why< this happens to be able to
explain, because he insists this difference of execution
time comes from a bad parameter in SQL Server! We have
already tested on three different servers and the results
always differ in the same way.
Could anyone explain this?
Thanks a lot in advance!
FlorI wouldn't expect such a big difference, but for temp object in tempdb, REDO operations are not
logged to the transaction log.
Do you have tempdb on same drive etc as the northwind database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in message
news:159001c5104c$264d82a0$a401280a@.phx.gbl...
> Hi,
> I have a problem explaining why a tempdb (#) table is 4
> to 5 times faster than a local (user database) table.
> To illustrate the performance differences I have the
> following script:
> =======================================================> USE TEMPDB
> GO
> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1
> INSERT #TEST_INSERT_20050211 ( LIG , DES )
> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> DROP TABLE #TEST_INSERT_20050211
> GO
> -- second test
> USE Northwind
> GO
> DROP TABLE TEST_INSERT_20050211
> GO
> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL , )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1 INSERT
> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
> (varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> ======================================================> The differences are 2(or 3) secs vs. 10 secs!
> I turned Physical Disk Perf Counters on and found out
> that, on the tempdb example, the disk activity is very,
> very small. For the local (user database) table, the disk
> activity is consequent with the differences in the
> execution time.
> One of our clients has a real life process that takes 35
> minutes to finish if he creates his table in tempdb and
> almost 5 hours with a local table.
> I would need to >know why< this happens to be able to
> explain, because he insists this difference of execution
> time comes from a bad parameter in SQL Server! We have
> already tested on three different servers and the results
> always differ in the same way.
> Could anyone explain this?
> Thanks a lot in advance!
> Flor
>|||Yes, they're on the same drive on my XP office computer
(where I tested).
As for the server my client works on (the big long
process) his user database and tempdb are on two
different drives (different controllers). Tempdb on a
RAID1 and the user database on a RAID5.
Thanks
Flor
>--Original Message--
>I wouldn't expect such a big difference, but for temp
object in tempdb, REDO operations are not
>logged to the transaction log.
>Do you have tempdb on same drive etc as the northwind
database?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
message
>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
>> =======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> ======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is very,
>> very small. For the local (user database) table, the
disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
35
>> minutes to finish if he creates his table in tempdb and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
execution
>> time comes from a bad parameter in SQL Server! We have
>> already tested on three different servers and the
results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>
>.
>|||OK, so REDO can still explain part of the difference. On yours client machine, RAID 1 vs RAID 5 can
also explain some difference. I'm still surprised that you see such a difference on the machine
where you have on same drive, I wouldn't expect the less logging in tempdb have such big impact.
Have you checked the execution plans?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <anonymous@.discussions.microsoft.com> wrote in message
news:064a01c51051$15f01c60$a501280a@.phx.gbl...
> Yes, they're on the same drive on my XP office computer
> (where I tested).
> As for the server my client works on (the big long
> process) his user database and tempdb are on two
> different drives (different controllers). Tempdb on a
> RAID1 and the user database on a RAID5.
> Thanks
> Flor
>>--Original Message--
>>I wouldn't expect such a big difference, but for temp
> object in tempdb, REDO operations are not
>>logged to the transaction log.
>>Do you have tempdb on same drive etc as the northwind
> database?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
> message
>>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
>> =======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
> Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> ======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is very,
>> very small. For the local (user database) table, the
> disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
> 35
>> minutes to finish if he creates his table in tempdb and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
> execution
>> time comes from a bad parameter in SQL Server! We have
>> already tested on three different servers and the
> results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>>
>>.|||It's possible that the difference has to do with the transaction logging
involved. RAID 1 is fast at writing. RAID 5 is the slowest
implementation possible (and many DBAs these days recommend staying away
from RAID 5 altogether). You are inserting 30,000 rows. For tempdb, you
are on the RAID 1 array and running in simple recovery model. So you
have pretty fast writing and auto-truncation of the log file as needed.
For the use database, you are on a RAID 5 array (and I assume the t-log
is on the same array, which is not good). That means very slow writes
and the possiblity of auto-grow operations, which are also slow. Make
sure that AutoShrink is not on in the user database.
You should consider moving the t-log for the user database to the RAID 1
array and test again.
David Gugick
Imceda Software
www.imceda.com|||I tried to but memory ressources began getting lower and
lower and the query was already taking more than 5
minutes, I had to stop it.
I tried to see the Execution Plan through Profiler an I
would say that for each loop in the while cycle it
generated a plan, and the ressources were also getting
lower.
Flor
>--Original Message--
>OK, so REDO can still explain part of the difference. On
yours client machine, RAID 1 vs RAID 5 can
>also explain some difference. I'm still surprised that
you see such a difference on the machine
>where you have on same drive, I wouldn't expect the less
logging in tempdb have such big impact.
>Have you checked the execution plans?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:064a01c51051$15f01c60$a501280a@.phx.gbl...
>> Yes, they're on the same drive on my XP office computer
>> (where I tested).
>> As for the server my client works on (the big long
>> process) his user database and tempdb are on two
>> different drives (different controllers). Tempdb on a
>> RAID1 and the user database on a RAID5.
>> Thanks
>> Flor
>>--Original Message--
>>I wouldn't expect such a big difference, but for temp
>> object in tempdb, REDO operations are not
>>logged to the transaction log.
>>Do you have tempdb on same drive etc as the northwind
>> database?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
>> message
>>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
>> Hi,
>> I have a problem explaining why a tempdb (#) table
is 4
>> to 5 times faster than a local (user database) table.
>> To illustrate the performance differences I have the
>> following script:
=======================================================>> USE TEMPDB
>> GO
>> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1
>> INSERT #TEST_INSERT_20050211 ( LIG , DES )
>> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]'
AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>> DROP TABLE #TEST_INSERT_20050211
>> GO
>> -- second test
>> USE Northwind
>> GO
>> DROP TABLE TEST_INSERT_20050211
>> GO
>> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
>> NULL , DES Varchar(50) NULL , )
>> GO
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET NOCOUNT On
>> DECLARE @.I INT
>> DECLARE @.B datetime
>> DECLARE @.E datetime
>> SET @.B = Getdate()
>> SET @.I = 0
>> WHILE @.I <= 30000
>> BEgin
>> SET @.I = @.I + 1 INSERT
>> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I ,
>> Convert
>> (varchar(10 ) , @.I ) )
>> End
>> SET @.E = Getdate()
>> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
>> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
>> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
>> Server_Base
>> SET NOCOUNT OFF
>> GO
>>
======================================================>> The differences are 2(or 3) secs vs. 10 secs!
>> I turned Physical Disk Perf Counters on and found out
>> that, on the tempdb example, the disk activity is
very,
>> very small. For the local (user database) table, the
>> disk
>> activity is consequent with the differences in the
>> execution time.
>> One of our clients has a real life process that takes
>> 35
>> minutes to finish if he creates his table in tempdb
and
>> almost 5 hours with a local table.
>> I would need to >know why< this happens to be able to
>> explain, because he insists this difference of
>> execution
>> time comes from a bad parameter in SQL Server! We
have
>> already tested on three different servers and the
>> results
>> always differ in the same way.
>> Could anyone explain this?
>> Thanks a lot in advance!
>> Flor
>>
>>.
>
>.
>
Performance differences local vs. tempdb table
Hi,
I have a problem explaining why a tempdb (#) table is 4
to 5 times faster than a local (user database) table.
To illustrate the performance differences I have the
following script:
========================================
===============
USE TEMPDB
GO
CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1
INSERT #TEST_INSERT_20050211 ( LIG , DES )
VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
Server_Base
SET NOCOUNT OFF
GO
DROP TABLE #TEST_INSERT_20050211
GO
-- second test
USE Northwind
GO
DROP TABLE TEST_INSERT_20050211
GO
CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL , )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1 INSERT
TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
Server_Base
SET NOCOUNT OFF
GO
========================================
==============
The differences are 2(or 3) secs vs. 10 secs!
I turned Physical Disk Perf Counters on and found out
that, on the tempdb example, the disk activity is very,
very small. For the local (user database) table, the disk
activity is consequent with the differences in the
execution time.
One of our clients has a real life process that takes 35
minutes to finish if he creates his table in tempdb and
almost 5 hours with a local table.
I would need to >know why< this happens to be able to
explain, because he insists this difference of execution
time comes from a bad parameter in SQL Server! We have
already tested on three different servers and the results
always differ in the same way.
Could anyone explain this?
Thanks a lot in advance!
FlorI wouldn't expect such a big difference, but for temp object in tempdb, REDO
operations are not
logged to the transaction log.
Do you have tempdb on same drive etc as the northwind database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in message
news:159001c5104c$264d82a0$a401280a@.phx.gbl...
> Hi,
> I have a problem explaining why a tempdb (#) table is 4
> to 5 times faster than a local (user database) table.
> To illustrate the performance differences I have the
> following script:
> ========================================
===============
> USE TEMPDB
> GO
> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1
> INSERT #TEST_INSERT_20050211 ( LIG , DES )
> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> DROP TABLE #TEST_INSERT_20050211
> GO
> -- second test
> USE Northwind
> GO
> DROP TABLE TEST_INSERT_20050211
> GO
> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL , )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1 INSERT
> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
> (varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> ========================================
==============
> The differences are 2(or 3) secs vs. 10 secs!
> I turned Physical Disk Perf Counters on and found out
> that, on the tempdb example, the disk activity is very,
> very small. For the local (user database) table, the disk
> activity is consequent with the differences in the
> execution time.
> One of our clients has a real life process that takes 35
> minutes to finish if he creates his table in tempdb and
> almost 5 hours with a local table.
> I would need to >know why< this happens to be able to
> explain, because he insists this difference of execution
> time comes from a bad parameter in SQL Server! We have
> already tested on three different servers and the results
> always differ in the same way.
> Could anyone explain this?
> Thanks a lot in advance!
> Flor
>|||Yes, they're on the same drive on my XP office computer
(where I tested).
As for the server my client works on (the big long
process) his user database and tempdb are on two
different drives (different controllers). Tempdb on a
RAID1 and the user database on a RAID5.
Thanks
Flor
>--Original Message--
>I wouldn't expect such a big difference, but for temp
object in tempdb, REDO operations are not
>logged to the transaction log.
>Do you have tempdb on same drive etc as the northwind
database?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
message
>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
Convert[vbcol=seagreen]
disk[vbcol=seagreen]
35[vbcol=seagreen]
execution[vbcol=seagreen]
results[vbcol=seagreen]
>
>.
>|||OK, so REDO can still explain part of the difference. On yours client machin
e, RAID 1 vs RAID 5 can
also explain some difference. I'm still surprised that you see such a differ
ence on the machine
where you have on same drive, I wouldn't expect the less logging in tempdb h
ave such big impact.
Have you checked the execution plans?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <anonymous@.discussions.microsoft.com> wrote in message
news:064a01c51051$15f01c60$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, they're on the same drive on my XP office computer
> (where I tested).
> As for the server my client works on (the big long
> process) his user database and tempdb are on two
> different drives (different controllers). Tempdb on a
> RAID1 and the user database on a RAID5.
> Thanks
> Flor
>
> object in tempdb, REDO operations are not
> database?
> message
> Convert
> disk
> 35
> execution
> results|||It's possible that the difference has to do with the transaction logging
involved. RAID 1 is fast at writing. RAID 5 is the slowest
implementation possible (and many DBAs these days recommend staying away
from RAID 5 altogether). You are inserting 30,000 rows. For tempdb, you
are on the RAID 1 array and running in simple recovery model. So you
have pretty fast writing and auto-truncation of the log file as needed.
For the use database, you are on a RAID 5 array (and I assume the t-log
is on the same array, which is not good). That means very slow writes
and the possiblity of auto-grow operations, which are also slow. Make
sure that AutoShrink is not on in the user database.
You should consider moving the t-log for the user database to the RAID 1
array and test again.
David Gugick
Imceda Software
www.imceda.com|||I tried to but memory ressources began getting lower and
lower and the query was already taking more than 5
minutes, I had to stop it.
I tried to see the Execution Plan through Profiler an I
would say that for each loop in the while cycle it
generated a plan, and the ressources were also getting
lower.
Flor
>--Original Message--
>OK, so REDO can still explain part of the difference. On
yours client machine, RAID 1 vs RAID 5 can
>also explain some difference. I'm still surprised that
you see such a difference on the machine
>where you have on same drive, I wouldn't expect the less
logging in tempdb have such big impact.
>Have you checked the execution plans?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:064a01c51051$15f01c60$a501280a@.phx.gbl...
is 4[vbcol=seagreen]
========================================
===============[vbcol=seagreen]
AS[vbcol=seagreen]
========================================
==============[vbcol=seagreen]
very,[vbcol=seagreen]
and[vbcol=seagreen]
have[vbcol=seagreen]
>
>.
>sql
I have a problem explaining why a tempdb (#) table is 4
to 5 times faster than a local (user database) table.
To illustrate the performance differences I have the
following script:
========================================
===============
USE TEMPDB
GO
CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1
INSERT #TEST_INSERT_20050211 ( LIG , DES )
VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
Server_Base
SET NOCOUNT OFF
GO
DROP TABLE #TEST_INSERT_20050211
GO
-- second test
USE Northwind
GO
DROP TABLE TEST_INSERT_20050211
GO
CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
NULL , DES Varchar(50) NULL , )
GO
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT On
DECLARE @.I INT
DECLARE @.B datetime
DECLARE @.E datetime
SET @.B = Getdate()
SET @.I = 0
WHILE @.I <= 30000
BEgin
SET @.I = @.I + 1 INSERT
TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
(varchar(10 ) , @.I ) )
End
SET @.E = Getdate()
SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
(@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
Server_Base
SET NOCOUNT OFF
GO
========================================
==============
The differences are 2(or 3) secs vs. 10 secs!
I turned Physical Disk Perf Counters on and found out
that, on the tempdb example, the disk activity is very,
very small. For the local (user database) table, the disk
activity is consequent with the differences in the
execution time.
One of our clients has a real life process that takes 35
minutes to finish if he creates his table in tempdb and
almost 5 hours with a local table.
I would need to >know why< this happens to be able to
explain, because he insists this difference of execution
time comes from a bad parameter in SQL Server! We have
already tested on three different servers and the results
always differ in the same way.
Could anyone explain this?
Thanks a lot in advance!
FlorI wouldn't expect such a big difference, but for temp object in tempdb, REDO
operations are not
logged to the transaction log.
Do you have tempdb on same drive etc as the northwind database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in message
news:159001c5104c$264d82a0$a401280a@.phx.gbl...
> Hi,
> I have a problem explaining why a tempdb (#) table is 4
> to 5 times faster than a local (user database) table.
> To illustrate the performance differences I have the
> following script:
> ========================================
===============
> USE TEMPDB
> GO
> CREATE TABLE #TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1
> INSERT #TEST_INSERT_20050211 ( LIG , DES )
> VALUES ( @.I , Convert(varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [ INSERT TEMPDB ]' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> DROP TABLE #TEST_INSERT_20050211
> GO
> -- second test
> USE Northwind
> GO
> DROP TABLE TEST_INSERT_20050211
> GO
> CREATE TABLE TEST_INSERT_20050211 ( LIG DEC(7,0)
> NULL , DES Varchar(50) NULL , )
> GO
> SET IMPLICIT_TRANSACTIONS OFF
> SET NOCOUNT On
> DECLARE @.I INT
> DECLARE @.B datetime
> DECLARE @.E datetime
> SET @.B = Getdate()
> SET @.I = 0
> WHILE @.I <= 30000
> BEgin
> SET @.I = @.I + 1 INSERT
> TEST_INSERT_20050211 ( LIG , DES ) VALUES ( @.I , Convert
> (varchar(10 ) , @.I ) )
> End
> SET @.E = Getdate()
> SELECT DATEDIFF ( ms, @.B, @.E ) AS Milli_sec, DATEDIFF
> ( SS, @.B, @.E ) AS Sec , @.I AS NB_Rows, Ltrim(Rtrim
> (@.@.SERVERNAME))+'-'+DB_NAME()+ ' [INSERT ] ' AS
> Server_Base
> SET NOCOUNT OFF
> GO
> ========================================
==============
> The differences are 2(or 3) secs vs. 10 secs!
> I turned Physical Disk Perf Counters on and found out
> that, on the tempdb example, the disk activity is very,
> very small. For the local (user database) table, the disk
> activity is consequent with the differences in the
> execution time.
> One of our clients has a real life process that takes 35
> minutes to finish if he creates his table in tempdb and
> almost 5 hours with a local table.
> I would need to >know why< this happens to be able to
> explain, because he insists this difference of execution
> time comes from a bad parameter in SQL Server! We have
> already tested on three different servers and the results
> always differ in the same way.
> Could anyone explain this?
> Thanks a lot in advance!
> Flor
>|||Yes, they're on the same drive on my XP office computer
(where I tested).
As for the server my client works on (the big long
process) his user database and tempdb are on two
different drives (different controllers). Tempdb on a
RAID1 and the user database on a RAID5.
Thanks
Flor
>--Original Message--
>I wouldn't expect such a big difference, but for temp
object in tempdb, REDO operations are not
>logged to the transaction log.
>Do you have tempdb on same drive etc as the northwind
database?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <f.bottazzi@.castsoftware.com> wrote in
message
>news:159001c5104c$264d82a0$a401280a@.phx.gbl...
Convert[vbcol=seagreen]
disk[vbcol=seagreen]
35[vbcol=seagreen]
execution[vbcol=seagreen]
results[vbcol=seagreen]
>
>.
>|||OK, so REDO can still explain part of the difference. On yours client machin
e, RAID 1 vs RAID 5 can
also explain some difference. I'm still surprised that you see such a differ
ence on the machine
where you have on same drive, I wouldn't expect the less logging in tempdb h
ave such big impact.
Have you checked the execution plans?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Flor Bottazzi" <anonymous@.discussions.microsoft.com> wrote in message
news:064a01c51051$15f01c60$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, they're on the same drive on my XP office computer
> (where I tested).
> As for the server my client works on (the big long
> process) his user database and tempdb are on two
> different drives (different controllers). Tempdb on a
> RAID1 and the user database on a RAID5.
> Thanks
> Flor
>
> object in tempdb, REDO operations are not
> database?
> message
> Convert
> disk
> 35
> execution
> results|||It's possible that the difference has to do with the transaction logging
involved. RAID 1 is fast at writing. RAID 5 is the slowest
implementation possible (and many DBAs these days recommend staying away
from RAID 5 altogether). You are inserting 30,000 rows. For tempdb, you
are on the RAID 1 array and running in simple recovery model. So you
have pretty fast writing and auto-truncation of the log file as needed.
For the use database, you are on a RAID 5 array (and I assume the t-log
is on the same array, which is not good). That means very slow writes
and the possiblity of auto-grow operations, which are also slow. Make
sure that AutoShrink is not on in the user database.
You should consider moving the t-log for the user database to the RAID 1
array and test again.
David Gugick
Imceda Software
www.imceda.com|||I tried to but memory ressources began getting lower and
lower and the query was already taking more than 5
minutes, I had to stop it.
I tried to see the Execution Plan through Profiler an I
would say that for each loop in the while cycle it
generated a plan, and the ressources were also getting
lower.
Flor
>--Original Message--
>OK, so REDO can still explain part of the difference. On
yours client machine, RAID 1 vs RAID 5 can
>also explain some difference. I'm still surprised that
you see such a difference on the machine
>where you have on same drive, I wouldn't expect the less
logging in tempdb have such big impact.
>Have you checked the execution plans?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Flor Bottazzi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:064a01c51051$15f01c60$a501280a@.phx.gbl...
is 4[vbcol=seagreen]
========================================
===============[vbcol=seagreen]
AS[vbcol=seagreen]
========================================
==============[vbcol=seagreen]
very,[vbcol=seagreen]
and[vbcol=seagreen]
have[vbcol=seagreen]
>
>.
>sql
Tuesday, March 20, 2012
Performance and TempDB
I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||My guess if that you have one or several queries that now uses a much worse plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
down is to work the query plans. Ideally, you would compared to before this happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that is
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:
>My guess if that you have one or several queries that now uses a much worse plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
>simply.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||New statistics will force recompilation. Reindexing will produce new statistics (INDEXDEFRAG will
not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
>>My guess if that you have one or several queries that now uses a much worse plan that it used to
>>do.
>>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track
>>this
>>down is to work the query plans. Ideally, you would compared to before this happened to track down
>>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan,
>>quite
>>simply.
>>I am seeing high CPU.
>>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||My guess if that you have one or several queries that now uses a much worse plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
down is to work the query plans. Ideally, you would compared to before this happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that is
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:
>My guess if that you have one or several queries that now uses a much worse plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
>simply.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||New statistics will force recompilation. Reindexing will produce new statistics (INDEXDEFRAG will
not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
>>My guess if that you have one or several queries that now uses a much worse plan that it used to
>>do.
>>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track
>>this
>>down is to work the query plans. Ideally, you would compared to before this happened to track down
>>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan,
>>quite
>>simply.
>>I am seeing high CPU.
>>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>
Performance and TempDB
I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%
.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:[vbcol=seagreen]
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% fr
ee.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier.
Only way to track this
down is to work the query plans. Ideally, you would compared to before this
happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, l
ack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a differe
nt execution plan, quite
simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...[vbcol
=seagreen]
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90
%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at
a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that i
s
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:[vbcol=seagreen]
>My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier
. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this
happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics,
lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a differ
ent execution plan, quite
>simply.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||New statistics will force recompilation. Reindexing will produce new statist
ics (INDEXDEFRAG will
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...[vbcol
=seagreen]
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%
.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:[vbcol=seagreen]
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% fr
ee.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier.
Only way to track this
down is to work the query plans. Ideally, you would compared to before this
happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, l
ack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a differe
nt execution plan, quite
simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...[vbcol
=seagreen]
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90
%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at
a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that i
s
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:[vbcol=seagreen]
>My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier
. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this
happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics,
lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a differ
ent execution plan, quite
>simply.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||New statistics will force recompilation. Reindexing will produce new statist
ics (INDEXDEFRAG will
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...[vbcol
=seagreen]
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]
Subscribe to:
Posts (Atom)