Monday, March 19, 2012

Explanation for performance difference?

Greetings,
I'm puzzled about a huge performance difference we're seeing during a
database upgrade. Details: We have two tables, A and B, with the
following structures:
A:
PKCol uniqueidentifier (pk)
UnqCol1 uniqueidentifier
DateCol1 datetime
NumCol1 numeric(20,0)
VCCol1 varchar(64)
VCCol2 varchar(255)
VCCol3 varchar(32)
UnqCol2 uniqueidentifier (fk) (index)
VCCol4 varchar(64)
VCCol5 varchar(80)
VCCol6 varchar(32)
VCCol7 varchar(16)
VCCol8 varchar(32)
IntCol1 integer
IntCol2 integer
IntCol3 integer
IntCol4 integer
B:
PkCol uniqueidentifier (pk)
UnqCol1 uniqueidentifier (fk) (index)
UnqCol2 uniqueidentifier (fk) (clustered index)
IntCol1 integer
IntCol2 integer
IntCol3 integer
IntCol4 integer
To upgrade table A, we add a smallint column, and set it to zero.
To upgrade table B, we add a uniqueidentifier column and a datetime
column, and populate the datetime column with a computed value.
Table A has 6.3 million rows; upgrading it takes 22 hours. Table B
has 39 million rows; upgrading it takes 90 minutes. Why?
My best guess is that it's because the size of a row in table B (88
bytes) is much smaller than the average row size in table A (~370
bytes), and so much more disk i/o is required to take care of table A
(the process activity always shows a wait type of "pageiolatch_sh").
Am I on the right track here?
SQL Server 2000, sp3a, log and data files on separate physical
devices.
Thanks,
Tom C.TC
Have you tried to run UPDATE STATISTICS after the upgrade?
"TC" <cleavet@.yahoo.com> wrote in message
news:991c5d53.0410290937.39fdf7df@.posting.google.com...
> Greetings,
> I'm puzzled about a huge performance difference we're seeing during a
> database upgrade. Details: We have two tables, A and B, with the
> following structures:
> A:
> PKCol uniqueidentifier (pk)
> UnqCol1 uniqueidentifier
> DateCol1 datetime
> NumCol1 numeric(20,0)
> VCCol1 varchar(64)
> VCCol2 varchar(255)
> VCCol3 varchar(32)
> UnqCol2 uniqueidentifier (fk) (index)
> VCCol4 varchar(64)
> VCCol5 varchar(80)
> VCCol6 varchar(32)
> VCCol7 varchar(16)
> VCCol8 varchar(32)
> IntCol1 integer
> IntCol2 integer
> IntCol3 integer
> IntCol4 integer
>
> B:
> PkCol uniqueidentifier (pk)
> UnqCol1 uniqueidentifier (fk) (index)
> UnqCol2 uniqueidentifier (fk) (clustered index)
> IntCol1 integer
> IntCol2 integer
> IntCol3 integer
> IntCol4 integer
> To upgrade table A, we add a smallint column, and set it to zero.
> To upgrade table B, we add a uniqueidentifier column and a datetime
> column, and populate the datetime column with a computed value.
> Table A has 6.3 million rows; upgrading it takes 22 hours. Table B
> has 39 million rows; upgrading it takes 90 minutes. Why?
> My best guess is that it's because the size of a row in table B (88
> bytes) is much smaller than the average row size in table A (~370
> bytes), and so much more disk i/o is required to take care of table A
> (the process activity always shows a wait type of "pageiolatch_sh").
> Am I on the right track here?
> SQL Server 2000, sp3a, log and data files on separate physical
> devices.
> Thanks,
> Tom C.|||sp_updatestats will run UPDATE STATISTICS on every table in a database, and
is always recommended after an upgrade.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$unu5yvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> TC
> Have you tried to run UPDATE STATISTICS after the upgrade?
>
> "TC" <cleavet@.yahoo.com> wrote in message
> news:991c5d53.0410290937.39fdf7df@.posting.google.com...
>> Greetings,
>> I'm puzzled about a huge performance difference we're seeing during a
>> database upgrade. Details: We have two tables, A and B, with the
>> following structures:
>> A:
>> PKCol uniqueidentifier (pk)
>> UnqCol1 uniqueidentifier
>> DateCol1 datetime
>> NumCol1 numeric(20,0)
>> VCCol1 varchar(64)
>> VCCol2 varchar(255)
>> VCCol3 varchar(32)
>> UnqCol2 uniqueidentifier (fk) (index)
>> VCCol4 varchar(64)
>> VCCol5 varchar(80)
>> VCCol6 varchar(32)
>> VCCol7 varchar(16)
>> VCCol8 varchar(32)
>> IntCol1 integer
>> IntCol2 integer
>> IntCol3 integer
>> IntCol4 integer
>>
>> B:
>> PkCol uniqueidentifier (pk)
>> UnqCol1 uniqueidentifier (fk) (index)
>> UnqCol2 uniqueidentifier (fk) (clustered index)
>> IntCol1 integer
>> IntCol2 integer
>> IntCol3 integer
>> IntCol4 integer
>> To upgrade table A, we add a smallint column, and set it to zero.
>> To upgrade table B, we add a uniqueidentifier column and a datetime
>> column, and populate the datetime column with a computed value.
>> Table A has 6.3 million rows; upgrading it takes 22 hours. Table B
>> has 39 million rows; upgrading it takes 90 minutes. Why?
>> My best guess is that it's because the size of a row in table B (88
>> bytes) is much smaller than the average row size in table A (~370
>> bytes), and so much more disk i/o is required to take care of table A
>> (the process activity always shows a wait type of "pageiolatch_sh").
>> Am I on the right track here?
>> SQL Server 2000, sp3a, log and data files on separate physical
>> devices.
>> Thanks,
>> Tom C.
>|||Karen & Uri,
Thanks for your responses. That's a valuable tip but I don't think
I've explained clearly. We're not seeing a performance difference
after the upgrade, but between two different portions of the upgrade.
The upgrade script looks something like this:
--Begin SQL text
alter table A add SmIntCol1 smallint null
go
alter table B add datecol1 datetime null, UnqCol3 uniqueidentifier
null
go
-- This takes 22 hours (6.3M rows):
update table A set SmIntCol1 = 0
go
-- This takes 90 minutes (39M rows):
update table B set DateCol1 = {computed value}
go
--End SQL text
Are you saying it would help to run UPDATE STATISTICS on table A after
adding the column, but before setting it to zero?
Thanks again,
Tom C.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:<e$phqb2vEHA.3580@.TK2MSFTNGP10.phx.gbl>...
> sp_updatestats will run UPDATE STATISTICS on every table in a database, and
> is always recommended after an upgrade.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$unu5yvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > TC
> > Have you tried to run UPDATE STATISTICS after the upgrade?|||Hi Tom
I'm not sure at all what this has to do with upgrading.
These are two completely different operations you're doing on two different
tables. Why do you think they shouldn't have different performance?
What are the execution plans for the two queries? I would bet they are very
different.
--
HTH
--
Kalen (not Karen) Delaney
SQL Server MVP
www.SolidQualityLearning.com
"TC" <cleavet@.yahoo.com> wrote in message
news:991c5d53.0411010847.654399aa@.posting.google.com...
> Karen & Uri,
> Thanks for your responses. That's a valuable tip but I don't think
> I've explained clearly. We're not seeing a performance difference
> after the upgrade, but between two different portions of the upgrade.
> The upgrade script looks something like this:
> --Begin SQL text
> alter table A add SmIntCol1 smallint null
> go
> alter table B add datecol1 datetime null, UnqCol3 uniqueidentifier
> null
> go
> -- This takes 22 hours (6.3M rows):
> update table A set SmIntCol1 = 0
> go
> -- This takes 90 minutes (39M rows):
> update table B set DateCol1 = {computed value}
> go
> --End SQL text
> Are you saying it would help to run UPDATE STATISTICS on table A after
> adding the column, but before setting it to zero?
> Thanks again,
> Tom C.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:<e$phqb2vEHA.3580@.TK2MSFTNGP10.phx.gbl>...
>> sp_updatestats will run UPDATE STATISTICS on every table in a database,
>> and
>> is always recommended after an upgrade.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:u$unu5yvEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> > TC
>> > Have you tried to run UPDATE STATISTICS after the upgrade?|||Hi Kalen,
First off, I apologize for getting your name wrong. As to the
operations, my question is why it is taking such a *huge* amount of
time to perform the UPDATE on table A, when that table has merely
1/6th the rows of table B. I know that "number of rows" isn't the
only thing that drives performance--that's why I speculated on row
size in my original post.
I haven't looked at the execution plans but I'll do so later. My
expectation is that they'll both show full table scans (showing my
Oracle roots, I know) since there's no WHERE clause on either UPDATE.
Thanks,
Tom C.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:<uGkDbFowEHA.1988@.TK2MSFTNGP12.phx.gbl>...
> Hi Tom
> I'm not sure at all what this has to do with upgrading.
> These are two completely different operations you're doing on two different
> tables. Why do you think they shouldn't have different performance?
> What are the execution plans for the two queries? I would bet they are very
> different.
> --
> HTH|||The number of indexes that have to be updated, and foreign keys that have to
be validated, can also have a major impact.
There might also be triggers on the table. The pages from one table might
already be in cache, and the others might not be.
And yes, the row size could also make a difference.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"TC" <cleavet@.yahoo.com> wrote in message
news:991c5d53.0411051149.5a68cd08@.posting.google.com...
> Hi Kalen,
> First off, I apologize for getting your name wrong. As to the
> operations, my question is why it is taking such a *huge* amount of
> time to perform the UPDATE on table A, when that table has merely
> 1/6th the rows of table B. I know that "number of rows" isn't the
> only thing that drives performance--that's why I speculated on row
> size in my original post.
> I haven't looked at the execution plans but I'll do so later. My
> expectation is that they'll both show full table scans (showing my
> Oracle roots, I know) since there's no WHERE clause on either UPDATE.
> Thanks,
> Tom C.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:<uGkDbFowEHA.1988@.TK2MSFTNGP12.phx.gbl>...
>> Hi Tom
>> I'm not sure at all what this has to do with upgrading.
>> These are two completely different operations you're doing on two
>> different
>> tables. Why do you think they shouldn't have different performance?
>> What are the execution plans for the two queries? I would bet they are
>> very
>> different.
>> --
>> HTH

No comments:

Post a Comment