back again with my server performance issue...

Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>

back again with my server performance issue...

Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.
Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>
|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>

AWE SQL Performance Problem

We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB R
AM. The setting seems to have made the difference in RAM usage, but we are e
xperiencing extremely high CPU utilization and overall the system does not s
eem to be any faster. If an
ything, it seems to have slowed down some. There are a lot of variables, but
I was wondering of anyone has had adverse effects with the AWE setting.From BOL.
<<
To enable AWE, set awe enabled to 1. SQL Server will reserve almost all
available memory, leaving 128 megabytes (MB) or less, unless a value has
been specified for max server memory.
What is your max memory setting? Leaving 128M for the OS probably isn't a
good idea.
BTW... Profiler may be helpful in understanding the CPU usage.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||We had some CPU spin and server instability issues that were instantaneously
and permenantly solved by turning off the /3GB switch and reducing max
server mem to 1.5GB less than physical. PSS said there was potential kernel
memory starvation with /3GB and max server memory set to near physical. Not
sure if this applies to your scenario.
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||Hi Phil,
I am viewing your post and since we have not heard from you for some time
and I wonder how things go on your side. To disable the AWE to check if the
performance issue is related to the AWE is the method to narrow down
performance problems to AWE. Also, you could refer to the link of the
followings for reference:
How To Gather Information for Effective Troubleshooting of Performance
Issues
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q175/6/58.ASP&NoWebContent=1
http://support.microsoft.com/defaul...kb;en-us;298475
Also, if the case is critical and it should be troubleshot by some advanced
tools which are not supported in the newsgroup. Therefore, we probably will
not be able to resolve the issue through the newsgroups. I'd recommend
opening a Support incident with Microsoft Support Services, so that a
dedicated Support Professional can assist with your advisory case. Please
be advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thanks.
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

AWE memory performance counters

Where can I get a definition of what the Performance Monitor counter
relating to AWE memory mean? Also, what are normal ranges for these
counters, what would be considered "high"
The counters I'm referring to are:
SQLServer:Buffer Manager\AWE lookup maps/sec
SQLServer:Buffer Manager\AWE stolen maps/sec
SQLServer:Buffer Manager\AWE stolen maps/sec
SQLServer:Buffer Manager\AWE unmap calls/sec
SQLServer:Buffer Manager\AWE unmap pages/sec
SQLServer:Buffer Manager\AWE write maps/sec
Any input is appreciated.The definition can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_0ugk.asp.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Ed Thibert" <ethibert@.dlbabson.com> wrote in message
news:OdUd0D4rDHA.732@.TK2MSFTNGP09.phx.gbl...
> Where can I get a definition of what the Performance Monitor counter
> relating to AWE memory mean? Also, what are normal ranges for these
> counters, what would be considered "high"
> The counters I'm referring to are:
> SQLServer:Buffer Manager\AWE lookup maps/sec
> SQLServer:Buffer Manager\AWE stolen maps/sec
> SQLServer:Buffer Manager\AWE stolen maps/sec
> SQLServer:Buffer Manager\AWE unmap calls/sec
> SQLServer:Buffer Manager\AWE unmap pages/sec
> SQLServer:Buffer Manager\AWE write maps/sec
>
> Any input is appreciated.
>

AWE /3GB performance

I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
concern when using the 3gb option is that I force the OS into 1gb of memory
and causing the OS to be memory starved. Is it possible to create this
condition and what would be the minimum set of services that should be
running on the SQL Server box. For example, print spooler, application layer
gateway, etc.
"Uri Dimant" wrote:

> John
> If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use /3GB (AWE
> support isn't used)
> You can provide more info to get more accurate advice
> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
> news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
>
>
John
>The /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>the base 4GB of RAM that Windows 2000/3 supports natively. If you don't
>specify >this option, then SQL Server will only take advantage of 2GB of
>the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
http://www.sql-server-performance.com/awe_memory.asp
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...[vbcol=seagreen]
>I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
> concern when using the 3gb option is that I force the OS into 1gb of
> memory
> and causing the OS to be memory starved. Is it possible to create this
> condition and what would be the minimum set of services that should be
> running on the SQL Server box. For example, print spooler, application
> layer
> gateway, etc.
>
> "Uri Dimant" wrote:
|||Uri, thanks for the info. I have read the doc and understand AWE and the 3gb
option. Also I noticed some more advanced boot.ini options to control the
use of the memory above 1g.
I have a sql server that is running slow. I have not been able to profile
the server. What I want to understand is "Is it possible to cause the OS to
be memory starved using the 3gb option causing sql server to appear slow?"
For example if the OS is forced to swap to disk for normal OS activities.
If it is possible what would be the telling signs?
thanks
"Uri Dimant" wrote:

> John
> http://www.sql-server-performance.com/awe_memory.asp
>
>
> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
> news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...
>
>
|||John
Is it SQL Server running dedicated on the server?
Do you have an OLTP applications connect to the SQL Server?
I'd strat to to identify perfomance problems from the application side (
checking indexes, database design)
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:DD6405A3-56C5-4394-81D5-121D204420F3@.microsoft.com...[vbcol=seagreen]
> Uri, thanks for the info. I have read the doc and understand AWE and the
> 3gb
> option. Also I noticed some more advanced boot.ini options to control the
> use of the memory above 1g.
> I have a sql server that is running slow. I have not been able to profile
> the server. What I want to understand is "Is it possible to cause the OS
> to
> be memory starved using the 3gb option causing sql server to appear slow?"
> For example if the OS is forced to swap to disk for normal OS activities.
> If it is possible what would be the telling signs?
> thanks
> "Uri Dimant" wrote:

AWE /3GB performance

Is it possible to use AWE and /3gb option and cause the OS to become
constrained? Is so what key OS services are critical and which ones should
be stopped?
thank
-JohnJohn
If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use /3GB (AWE
support isn't used)
You can provide more info to get more accurate advice
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
> Is it possible to use AWE and /3gb option and cause the OS to become
> constrained? Is so what key OS services are critical and which ones
> should
> be stopped?
> thank
> -John|||Hello John,
You can also use \PAE option if memory is more on the server e.g.8 GB and
SQL Server 2005 will manage the memory dynamically after the enabling the
AWE option.
Good Luck!
MB
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
> Is it possible to use AWE and /3gb option and cause the OS to become
> constrained? Is so what key OS services are critical and which ones
> should
> be stopped?
> thank
> -John|||John
>The /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>the base 4GB of RAM that Windows 2000/3 supports natively. If you don't
>specify >this option, then SQL Server will only take advantage of 2GB of
>the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
http://www.sql-server-performance.com/awe_memory.asp
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...
>I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
> concern when using the 3gb option is that I force the OS into 1gb of
> memory
> and causing the OS to be memory starved. Is it possible to create this
> condition and what would be the minimum set of services that should be
> running on the SQL Server box. For example, print spooler, application
> layer
> gateway, etc.
>
> "Uri Dimant" wrote:
>> John
>> If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use /3GB
>> (AWE
>> support isn't used)
>> You can provide more info to get more accurate advice
>> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
>> > Is it possible to use AWE and /3gb option and cause the OS to become
>> > constrained? Is so what key OS services are critical and which ones
>> > should
>> > be stopped?
>> >
>> > thank
>> > -John
>>|||John
Is it SQL Server running dedicated on the server?
Do you have an OLTP applications connect to the SQL Server?
I'd strat to to identify perfomance problems from the application side (
checking indexes, database design)
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:DD6405A3-56C5-4394-81D5-121D204420F3@.microsoft.com...
> Uri, thanks for the info. I have read the doc and understand AWE and the
> 3gb
> option. Also I noticed some more advanced boot.ini options to control the
> use of the memory above 1g.
> I have a sql server that is running slow. I have not been able to profile
> the server. What I want to understand is "Is it possible to cause the OS
> to
> be memory starved using the 3gb option causing sql server to appear slow?"
> For example if the OS is forced to swap to disk for normal OS activities.
> If it is possible what would be the telling signs?
> thanks
> "Uri Dimant" wrote:
>> John
>> >The /3GB switch is used to tell SQL Server to take advantage of 3GB out
>> >of
>> >the base 4GB of RAM that Windows 2000/3 supports natively. If you don't
>> >specify >this option, then SQL Server will only take advantage of 2GB of
>> >the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
>> http://www.sql-server-performance.com/awe_memory.asp
>>
>>
>> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...
>> >I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
>> > concern when using the 3gb option is that I force the OS into 1gb of
>> > memory
>> > and causing the OS to be memory starved. Is it possible to create this
>> > condition and what would be the minimum set of services that should be
>> > running on the SQL Server box. For example, print spooler, application
>> > layer
>> > gateway, etc.
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> John
>> >>
>> >> If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use
>> >> /3GB
>> >> (AWE
>> >> support isn't used)
>> >> You can provide more info to get more accurate advice
>> >>
>> >> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> >> news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
>> >> > Is it possible to use AWE and /3gb option and cause the OS to become
>> >> > constrained? Is so what key OS services are critical and which ones
>> >> > should
>> >> > be stopped?
>> >> >
>> >> > thank
>> >> > -John
>> >>
>> >>
>> >>
>>

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:
> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreas
yes.
Greg Jackson
PDX, Oregon
|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>
|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers
.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

auto-summary columns

In a lot of cases for the sake of performance I use UPDATE, INSERT, and
DELETE triggers for maintaining a denormalized column in another table that
stores summary information (such as an inventory transaction table and a
total on-hand balance column in an item master table).
I was thinking, since I've made lots of variations of this all of which are
basically the same in form, it would be convenient for MS to supply a
special 'summary' column type that automatically monitors the other table's
column being summarized and stays up to date so I don't have to create
triggers every time. I know you can get summaries just by writing an SP or
view to retrieve them, but that's really inefficient when the table being
summarized gets large.
I'm posting this idea on the off chance I've missed a feature in SS2K that
does something like this already... also, does anyone know if this is a
feature known to be coming in Yukon?
TIA,
BobHi
Have you looked at Computed Columns?
BOL has info on it.
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/
"Bob" <noone@.nowhere.com> wrote in message
news:OHRHI$CIFHA.2136@.TK2MSFTNGP14.phx.gbl...
> In a lot of cases for the sake of performance I use UPDATE, INSERT, and
> DELETE triggers for maintaining a denormalized column in another table
that
> stores summary information (such as an inventory transaction table and a
> total on-hand balance column in an item master table).
> I was thinking, since I've made lots of variations of this all of which
are
> basically the same in form, it would be convenient for MS to supply a
> special 'summary' column type that automatically monitors the other
table's
> column being summarized and stays up to date so I don't have to create
> triggers every time. I know you can get summaries just by writing an SP or
> view to retrieve them, but that's really inefficient when the table being
> summarized gets large.
> I'm posting this idea on the off chance I've missed a feature in SS2K that
> does something like this already... also, does anyone know if this is a
> feature known to be coming in Yukon?
> TIA,
> Bob
>|||Have you considered using indexed views?
Always maintain a healthy degree of skepticism in the face of arguments
that favour denormalization "for the sake of performance".
Denormalization is a trade off. One query's performance is improved but
elsewhere performance and integrity suffers. Denormalization can also
be a slippery slope toward more denormalization. Certainly if you
denormalize "in a lot of cases" then I suggest you take a long hard
look at whether you have the correct design and implementation. There
are usually better solutions.
David Portas
SQL Server MVP
--|||Second that, You should only denormalize AFTER a performance problem has
surfaced in a normalized data structure, and then only after examining all
the other options. There's a;most always a way to improve performance in a
normalized database schema, using properly designed and optimized indices.
And even if that approach isn;t sufficient to deal with the problem,
denormalization applied to a fully normalized schema will always be more
effective and successful than denormalization done out of the gate.
"David Portas" wrote:

> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||>> I use UPDATE, INSERT, and DELETE triggers for maintaining a
denormalized column in another table that stores summary information
(such as an inventory transaction table and a
total on-hand balance column in an item master table). <<
The only reason to store summary infomation is that this is a data
warehouse, that is so big that the recomputation would be too
expensive. But the data is static in a DW.
I would stick with nice, portable and always correct VIEWs instead of
proprietary triggers that fire everytime the table is touched.|||The funny thing is that for the cost of the coding you could probably
upgrade hardware enough to avoid the whole denormalization thing :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109888063.340480.52940@.g14g2000cwa.googlegroups.com...
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||All of my practical experience indicates to me that denormalization is a
good thing for storing aggregate information; I've seen it to be simple,
nothing but reliable, and dramatically increase read performance without
significantly impacting write performance.
As such, I don't find your argument compelling (partly because I'm not
concerned with portability). I'm clearly not one of such distinction and
experience as yourself; I would ask you to elaborate your point of view so I
can better understand it.
Bob
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109898958.939621.87460@.o13g2000cwo.googlegroups.com...
> denormalized column in another table that stores summary information
> (such as an inventory transaction table and a
> total on-hand balance column in an item master table). <<
> The only reason to store summary infomation is that this is a data
> warehouse, that is so big that the recomputation would be too
> expensive. But the data is static in a DW.
> I would stick with nice, portable and always correct VIEWs instead of
> proprietary triggers that fire everytime the table is touched.
>|||>> All of my practical experience indicates to me that denormalization
is a good thing for storing aggregate information; I've seen it to be
simple, nothing but reliable, and dramatically increase read
performance without significantly impacting write performance. <<
I have seen the opposite. Triggers constantly firing slow things down.
The time to write a value is orders of magnitude greater than the time
to compute it. The extra storage starts to add up. Etc. But more
than that, data integrity gets shot in the foot. Example: Orders carry
the total in a column that is supposed to equal the sum of the order
details. I put a trigger on OrderDetails to modify Orders. But I have
no trigger on Orders, so someone can change that total directly -- and
they will. As I start to use only triggers for data integrity, I find
that more and more business rules need more than one trigger apiece.
That is a little hard in SQL Server and a serious problem in more
powerful SQL products that have BEFORE and AFTER, as well as multiple
trigger options.
not concerned with portability). I'm clearly not one of such
distinction and experience as yourself; I would ask you to elaborate
your point of view so I can better understand it. <<
Portability and standard code are always issues. You port from one
release of the same software to another. You hire programmers who do
not know your local dialect. Unless the company business plan is to
stagnate and die, you will port and maintain code -- this is 80% of the
total cost of a system over its lifetime. Pros write code for other
people and amateurs write code to amuse themselves.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
> <...>
Points taken, thank you.

> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
I have no problem telling my customers, 'Microsoft only'. So far I've
received nothing but nods of approval. I will port and maintain code, but
only to other MS products.
I know, I'm going to hell...
Bob|||I have never found anything I couldn't do using Microsoft's triggers. I
agree completely that keeping summary data is usually wrong, and I have only
had one case where it was necessary. We had a manufacturing application
that calculated stuff that took the last fifty readings and the last fifty
calculated values into consideration (one SQL Statement was 200+lines.)
Needless to say that it took way too long to recalculate these values on
demand. So we had a trigger call the summary procedure when values were
entered.
Either way, it is always my advice to never denormalize your data for
performance until you have exhausted all of the usual tips. Indexing, views
(indexed too,) correct hardware, well built apps, etc.first. If it is
needed, it is needed, but seldom is that true.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110035642.897731.285480@.z14g2000cwz.googlegroups.com...
> is a good thing for storing aggregate information; I've seen it to be
> simple, nothing but reliable, and dramatically increase read
> performance without significantly impacting write performance. <<
> I have seen the opposite. Triggers constantly firing slow things down.
> The time to write a value is orders of magnitude greater than the time
> to compute it. The extra storage starts to add up. Etc. But more
> than that, data integrity gets shot in the foot. Example: Orders carry
> the total in a column that is supposed to equal the sum of the order
> details. I put a trigger on OrderDetails to modify Orders. But I have
> no trigger on Orders, so someone can change that total directly -- and
> they will. As I start to use only triggers for data integrity, I find
> that more and more business rules need more than one trigger apiece.
> That is a little hard in SQL Server and a serious problem in more
> powerful SQL products that have BEFORE and AFTER, as well as multiple
> trigger options.
>
> not concerned with portability). I'm clearly not one of such
> distinction and experience as yourself; I would ask you to elaborate
> your point of view so I can better understand it. <<
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
>