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...
> > 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..
> >
> >
> >
> >
>|||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...
>> 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..
>> >
>> >
>> >
>> >
>>
>|||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...
> > 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...
> >> > 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..
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||What about disk system and where are located tempdb, data and transaction log.
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 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...
> > > 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..
> > >
> > >
> > >
> > >
> >
> >
>
>|||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...
>> 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...
>> >> 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..
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||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...
>> 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...
>> > 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...
>> >> > 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..
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>|||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...
> > 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...
> >> 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...
> >> >> 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..
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||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.
>
> 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
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...
> > > > 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..
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >|||It's really hard to say how much of it is due to CPU speed alone without
actually being there and taking into account everything. But going to a
system with processors that are almost half as slow as before is certainly
not a step up in my opinion.
--
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:e%23do4ncQFHA.3496@.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...
>> > 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...
>> >> 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...
>> >> >> 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..
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>