Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
server[vbcol=seagreen]
application[vbcol=seagreen]
40%[vbcol=seagreen]
>|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
think[vbcol=seagreen]
sec[vbcol=seagreen]
the[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
as[vbcol=seagreen]
configuration[vbcol=seagreen]
I[vbcol=seagreen]
performance..Context[vbcol=seagreen]
>|||What about disk system and where are located tempdb, data and transaction lo
g.
AMB
"Nik" wrote:

> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases resi
de
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
>|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> the
> out
> a
> a
> a
> think
> sec
> the
> than
> If
> I/O
> context
> as
> configuration
> I
> performance..Context
>|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
different[vbcol=seagreen]
ghz[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
17[vbcol=seagreen]
bring[vbcol=seagreen]
databases[vbcol=seagreen]
will[vbcol=seagreen]
more[vbcol=seagreen]
goes.[vbcol=seagreen]
your[vbcol=seagreen]
line[vbcol=seagreen]
sql[vbcol=seagreen]
>|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.[vbcol=seagreen]
>
> AMB
> "Nik" wrote:
>
think[vbcol=seagreen]
sec to[vbcol=seagreen]
the[vbcol=seagreen]
reside[vbcol=seagreen]
have[vbcol=seagreen]
are[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
while it[vbcol=seagreen]
causes[vbcol=seagreen]
is[vbcol=seagreen]
as[vbcol=seagreen]
much[vbcol=seagreen]
configuration[vbcol=seagreen]
migrated[vbcol=seagreen]
around[vbcol=seagreen]
9000/sec, I[vbcol=seagreen]
thread[vbcol=seagreen]
performance..Context[vbcol=seagreen]