Consolidation Query: context

Showing posts with label context. Show all posts

Wednesday, March 7, 2012

Context Switches Greater 10,000

I have a server that has SQL Server 2000 Enterprise
Edition with SP3A and Windows 2000 Advanced Server as the
operating system.
This is a new system and I checked my context switches
they average 9600 Context Switches /sec over the was 3
weeks. During the busy part of the day they average
14,000 Context Switches / sec.
Should I enable the fiber-based schedule in SQL Server by
setting the lightweight pooling configuration option to 1?
Please help me with this task.
Thanks,
Mike
That's most likely not the answer. You need to determine why you get rates
such as those. It's quite possible you have other issues that youcan address
to cut this down. See if these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:cd0301c43943$9824b0f0$a101280a@.phx.gbl...
> I have a server that has SQL Server 2000 Enterprise
> Edition with SP3A and Windows 2000 Advanced Server as the
> operating system.
> This is a new system and I checked my context switches
> they average 9600 Context Switches /sec over the was 3
> weeks. During the busy part of the day they average
> 14,000 Context Switches / sec.
> Should I enable the fiber-based schedule in SQL Server by
> setting the lightweight pooling configuration option to 1?
> Please help me with this task.
> Thanks,
> Mike
>

Context Switches Greater 10,000

I have a server that has SQL Server 2000 Enterprise
Edition with SP3A and Windows 2000 Advanced Server as the
operating system.
This is a new system and I checked my context switches
they average 9600 Context Switches /sec over the was 3
weeks. During the busy part of the day they average
14,000 Context Switches / sec.
Should I enable the fiber-based schedule in SQL Server by
setting the lightweight pooling configuration option to 1?
Please help me with this task.
Thanks,
MikeThat's most likely not the answer. You need to determine why you get rates
such as those. It's quite possible you have other issues that youcan address
to cut this down. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:cd0301c43943$9824b0f0$a101280a@.phx.gbl...
> I have a server that has SQL Server 2000 Enterprise
> Edition with SP3A and Windows 2000 Advanced Server as the
> operating system.
> This is a new system and I checked my context switches
> they average 9600 Context Switches /sec over the was 3
> weeks. During the busy part of the day they average
> 14,000 Context Switches / sec.
> Should I enable the fiber-based schedule in SQL Server by
> setting the lightweight pooling configuration option to 1?
> Please help me with this task.
> Thanks,
> Mike
>

Context Switches Greater 10,000

I have a server that has SQL Server 2000 Enterprise
Edition with SP3A and Windows 2000 Advanced Server as the
operating system.
This is a new system and I checked my context switches
they average 9600 Context Switches /sec over the was 3
weeks. During the busy part of the day they average
14,000 Context Switches / sec.
Should I enable the fiber-based schedule in SQL Server by
setting the lightweight pooling configuration option to 1?
Please help me with this task.
Thanks,
MikeThat's most likely not the answer. You need to determine why you get rates
such as those. It's quite possible you have other issues that youcan address
to cut this down. See if these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
on_24u1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:cd0301c43943$9824b0f0$a101280a@.phx.gbl...
> I have a server that has SQL Server 2000 Enterprise
> Edition with SP3A and Windows 2000 Advanced Server as the
> operating system.
> This is a new system and I checked my context switches
> they average 9600 Context Switches /sec over the was 3
> weeks. During the busy part of the day they average
> 14,000 Context Switches / sec.
> Should I enable the fiber-based schedule in SQL Server by
> setting the lightweight pooling configuration option to 1?
> Please help me with this task.
> Thanks,
> Mike
>

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..
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>

Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.

context in trigger

Hi All,
I need to know if there is any methode to detect within a trigger, that it
was fired because of merge agent update of the table and not because of the
application's update.
Thanks.
query sessionproperty('replication_agent') to see if its value is 0. If so
a replication agent is making the update, if it is 1, it is another user
process.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Oussama Albairat" <OussamaAlbairat@.discussions.microsoft.com> wrote in
message news:990BF6F4-FF7A-4F76-B560-D9A02261FA3D@.microsoft.com...
> Hi All,
> I need to know if there is any methode to detect within a trigger, that
it
> was fired because of merge agent update of the table and not because of
the
> application's update.
> Thanks.
|||Hi Hilary,
Thank you for the indication. But I noticed that the condition should be
evaluated as in replication triggers : if (
sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) =
1) the first part alone is not enough to detect that the trigger has been
fired by replication agent.
Thanks.
"Hilary Cotter" wrote:

> query sessionproperty('replication_agent') to see if its value is 0. If so
> a replication agent is making the update, if it is 1, it is another user
> process.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Oussama Albairat" <OussamaAlbairat@.discussions.microsoft.com> wrote in
> message news:990BF6F4-FF7A-4F76-B560-D9A02261FA3D@.microsoft.com...
> it
> the
>
>

Context Connection Transaction

Hello Guys,

I need some some clarifications on how Context connections and transactions inter operate in CLR.

The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.

SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);

I guess my ambiguity comes from the fact that the Transaction is not specifically specified.

In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.

Thank you,

Lubomir

Hi Lubomir!

I see that in your post you ask questions and give right answers to them yourself

Thank you,

Context connection and transactions

All -
Facts:
(a) SqlConnection allows only one "Context connection" to be opened at a tim
e.
(b) SqlConnection doesnt support parallel transactions. (calling a second
BeginTransaction before committing the first BeginTransaction throws
exception)
Problem:
Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
registered as SQLProcedure, is invoked.
Inside this code (which is now in SqlServer context) - i open a
SqlConnection as "context connection".
All the SqlCommand objects that uses this context connection, need the same
transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
different transaction. (lets say TransB)
How to do this?
Calling BeginTransaction twice blows up.
Creating 2 seperate instances of SqlConnection with context connection =
true, also blows up.
Remember that somehow i might want to rollback TransA but always committ
TransB. For instance
try
{
// Do some actions
// SqlCommands that use TransA and TransB will be executed here
transA.Committ(); // things are fine - committ A
}
catch
{
transA.Rollback(); // things are incorrect - rollback A
}
finally
{
transB.Committ(); // Always comitt the logs...
}
How to do this when we are in context connection?
Regardz
Grafix."Grafix" <Grafix@.discussions.microsoft.com> wrote in message
news:54F45F4C-4F6B-44D7-8B13-0D55F13BB75C@.microsoft.com...
> All -
> Facts:
> (a) SqlConnection allows only one "Context connection" to be opened at a
> time.
> (b) SqlConnection doesnt support parallel transactions. (calling a second
> BeginTransaction before committing the first BeginTransaction throws
> exception)
> Problem:
> Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
> registered as SQLProcedure, is invoked.
> Inside this code (which is now in SqlServer context) - i open a
> SqlConnection as "context connection".
> All the SqlCommand objects that uses this context connection, need the
> same
> transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
> different transaction. (lets say TransB)
> How to do this?
> Calling BeginTransaction twice blows up.
> Creating 2 seperate instances of SqlConnection with context connection =
> true, also blows up.
> Remember that somehow i might want to rollback TransA but always committ
> TransB. For instance
>
You will have exactly the same issue in TSQL. SQL Server does not support
autonomous transactions.
A common workaround is to write data into a table variable (or List<T> ), and
do something with it after the transaction is rolled back.
David|||I haven't gone into CLR stored procedures too much. But can't you add this
one SqlCommand (LogCmd) that needs a different transaction
into a seperate class and use the transaction option as requiresNew and with
the method haveing an autocommit tag to be true. and then call it from the
parent transaction.|||> one SqlCommand (LogCmd) that needs a different transaction
> into a seperate class and use the transaction option as requiresNew
There is the problem.
I am not able to have 2 active transaction scopes at the same time.
Remember LogCmd will be executed multiple times inbetween other regular
xxxCmd.
Which means i want 2 active transaction scopes (each with Option
"RequiresNew")
Unfortunately when i am in context connection (C# sp), i am not allowed to
create 2 connections that i can enlist with the TransactionScope.
I tried ur suggestion with the new .NET 2.0's
System.Transactions.TransactionScope class (Promotable transaction) for
seeing if i can enlist a single connection with multiple transactions - but
the problem is the same. So bad that they have introduced new Options like
TransactionScopeOption.Suppress and i am not able to use it still.
With one SqlConnection - u cannot associate with 2 different transaction
scopes.
And this C# sps allow only one connection at a time.
Seems i have to follow David Browne's suggestion of in-memory datastructure
to hold the logs and committ at the end.

Wednesday, March 7, 2012

Context Switches Greater 10,000

Context Switches Greater 10,000

Context Switches Greater 10,000

Context Swiitching

Context Swiitching

Context Swiitching

Context Search

context in trigger

Context Connection Transaction

Context connection and transactions

Consolidation Query

Blog Archive

About Me