Email Notifications

Hi All,
I have a sql2k running on w2k adv. svr and have a stored procedure which
sends email notifications to clients using xp_sendmail.
Out of the 25 email notifications 1 or 2 fails every other day which is
unacceptable to my clients.
Checking all my logs, I see that the xp_sendmail query runs successfully all
the time.
I need to understand why the query runs successfully all the time and yet
get 1 or 2 email failures?
ThanksIt can a problem with the mail system. Since SQL Server using MAPI to send a
mail, you should check your mail transport.
There are a lot of issues can happen during mail delivery. First of all try
to check is there everything is Ok with
your MS Outlook during the failure time. Then you can check MS Exchange
Server logs if you are using it.
It also can be network issue.
Regards
---
All information provided above AS IS.
"caddo65590" <caddo65590@.hotmail.com> wrote in message
news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have a sql2k running on w2k adv. svr and have a stored procedure which
> sends email notifications to clients using xp_sendmail.
> Out of the 25 email notifications 1 or 2 fails every other day which is
> unacceptable to my clients.
> Checking all my logs, I see that the xp_sendmail query runs successfully
all
> the time.
> I need to understand why the query runs successfully all the time and yet
> get 1 or 2 email failures?
> Thanks
>|||Thanks Sky,
I also forgot to add the error generated by sql, it might help.
The message below is generated anytime the email fails.
Msg 18025, Sev 16: xp_sendmail: failed with mail error 0x80004005
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:Or72cUKpDHA.2444@.TK2MSFTNGP09.phx.gbl...
> It can a problem with the mail system. Since SQL Server using MAPI to send
a
> mail, you should check your mail transport.
> There are a lot of issues can happen during mail delivery. First of all
try
> to check is there everything is Ok with
> your MS Outlook during the failure time. Then you can check MS Exchange
> Server logs if you are using it.
> It also can be network issue.
>
> Regards
> ---
> All information provided above AS IS.
>
> "caddo65590" <caddo65590@.hotmail.com> wrote in message
> news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> > I have a sql2k running on w2k adv. svr and have a stored procedure which
> > sends email notifications to clients using xp_sendmail.
> > Out of the 25 email notifications 1 or 2 fails every other day which is
> > unacceptable to my clients.
> > Checking all my logs, I see that the xp_sendmail query runs successfully
> all
> > the time.
> > I need to understand why the query runs successfully all the time and
yet
> > get 1 or 2 email failures?
> > Thanks
> >
> >
>|||Did you search KB for that return code. I found at least two articles for
that return code that referred to xp_sendmail.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"caddo65590" <caddo65590@.hotmail.com> wrote in message
news:OKuLYdKpDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks Sky,
> I also forgot to add the error generated by sql, it might help.
> The message below is generated anytime the email fails.
> Msg 18025, Sev 16: xp_sendmail: failed with mail error 0x80004005
> "SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
> news:Or72cUKpDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > It can a problem with the mail system. Since SQL Server using MAPI to
send
> a
> > mail, you should check your mail transport.
> > There are a lot of issues can happen during mail delivery. First of all
> try
> > to check is there everything is Ok with
> > your MS Outlook during the failure time. Then you can check MS Exchange
> > Server logs if you are using it.
> > It also can be network issue.
> >
> >
> >
> > Regards
> > ---
> > All information provided above AS IS.
> >
> >
> > "caddo65590" <caddo65590@.hotmail.com> wrote in message
> > news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > > I have a sql2k running on w2k adv. svr and have a stored procedure
which
> > > sends email notifications to clients using xp_sendmail.
> > > Out of the 25 email notifications 1 or 2 fails every other day which
is
> > > unacceptable to my clients.
> > > Checking all my logs, I see that the xp_sendmail query runs
successfully
> > all
> > > the time.
> > > I need to understand why the query runs successfully all the time and
> yet
> > > get 1 or 2 email failures?
> > > Thanks
> > >
> > >
> >
> >
>

email alerts

I was wondering if there was a nice easy way to use a simple tool to email
me when my sql2k server is having some issues? I have been looking at how to
configure SQLAgentMail and SQL Mail but do I really need a mapi client? Or
what is the easiest way to do this? Thanks for the info.
Jason
You can use xp_smtp_sendmail, just use smtp and not have to
deal with the MAPI issues. You can find the download and
information at:
http://www.sqldev.net/xp/xpsmtp.htm
In terms of alerts, you can run a job in response to alerts.
So if you had a job that sends an email notification using
xp_smtp_sendmail, you could just fire that job in response
to the alert.
-Sue
On Mon, 29 Mar 2004 12:32:38 -0600, "JasonMeyer"
<jason.meyer@.nospam.isd623.nospam.org> wrote:

>I was wondering if there was a nice easy way to use a simple tool to email
>me when my sql2k server is having some issues? I have been looking at how to
>configure SQLAgentMail and SQL Mail but do I really need a mapi client? Or
>what is the easiest way to do this? Thanks for the info.
>
>Jason
>
|||... and here are some other options:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:ur0h60p3vl3i5es10df9dtud60jp2bvpb9@.4ax.com...
> You can use xp_smtp_sendmail, just use smtp and not have to
> deal with the MAPI issues. You can find the download and
> information at:
> http://www.sqldev.net/xp/xpsmtp.htm
> In terms of alerts, you can run a job in response to alerts.
> So if you had a job that sends an email notification using
> xp_smtp_sendmail, you could just fire that job in response
> to the alert.
> -Sue
> On Mon, 29 Mar 2004 12:32:38 -0600, "JasonMeyer"
> <jason.meyer@.nospam.isd623.nospam.org> wrote:
>

Effect of adding additional CPUs

We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
Enterprise.
What will be the effect on SQL server performance if we add more CPUs ?
Are there any calulations (or numbers available) we can do to determine the
effect of additional CPUs on SQLl server performace ( no of threads, no of
user connections etc)
--
RKIt has a lot to do with what it is you are doing and how you are doing it.
Since that varies so much there is little a std calculation will achieve.
With more CPU's you have the potential to handle more individual
transactions concurrently. You can also potentially do multi-threaded
operations faster such as DBREINDEX and CHECKDB etc. But that does not
guarantee it will. Do you have processor queue issues now?
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> Enterprise.
> What will be the effect on SQL server performance if we add more CPUs ?
> Are there any calulations (or numbers available) we can do to determine
> the
> effect of additional CPUs on SQLl server performace ( no of threads, no of
> user connections etc)
> --
> RK|||no I do not have preocessor queue issues as of now. Ours is a web applicatio
n
with lot of selects but few insert/updates (hotel reservations). The reason
I
am doing this is for future growth. The product has to be able to support
five times the current load. I am guessing increasing the load by 5 time wil
l
definately cause CPU queue issues. But Increasing the CPUs- is it a good
route to take to handle more load ? Any good reading material on this
subject.
RK
"Andrew J. Kelly" wrote:

> It has a lot to do with what it is you are doing and how you are doing it.
> Since that varies so much there is little a std calculation will achieve.
> With more CPU's you have the potential to handle more individual
> transactions concurrently. You can also potentially do multi-threaded
> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> guarantee it will. Do you have processor queue issues now?
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>
>|||Increasing the CPU's is always a good way to deal with increased load but
you need to do a few things first.
Make sure all the code and tables are fully optimized. A bad query or lack
of index can drag down a good server fast.
Make sure you have enough ram to keep the relevant data in cache.
Have a properly configured disk subsystem.
Set the MAXDOP at the server level to less than the total number of procs to
allow concurrent queries in peak times.
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...[vbcol=seagreen]
> no I do not have preocessor queue issues as of now. Ours is a web
> application
> with lot of selects but few insert/updates (hotel reservations). The
> reason I
> am doing this is for future growth. The product has to be able to support
> five times the current load. I am guessing increasing the load by 5 time
> will
> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> route to take to handle more load ? Any good reading material on this
> subject.
> --
> RK
>
> "Andrew J. Kelly" wrote:
>|||What server do you have? We were running 500 users on a Dell 8450 with 4
900Mhz cpus.
It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
3.2ghz hyperthreading cpu's.
The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
perc3/dc controllers.
The compaq has only 6 disks but just wipes the floor with the old dell.
While I've had the dell down I tried replacing the Dell Perc3's with the
latest compaq controllers. Dispite the controllers being U320 they have to
run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
sustained disk throughput increased by a factor of 2 both for reads and
writes. I was never happy with the perc3's but now the compaq controllers
proved the point. Note also that dell array manager does not set up raid 10
correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
have to set up the raid 10 arrays through the dell controller bios to
achieve true raid 10.
Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
channels (dual channel controllers). Write back cache enabled. all stats
unbuffered by windows.
Test tool Sisoft Sandra.
Perc3/DC. Set up by array manager
Sequential Read 33 Mb/sec
Random Read 11 Mb/sec
Sequential Write 22 Mb/sec
Random Write 19 Mb/sec
Perc3/DC. Set up bios
Sequential Read 120 Mb/sec
Random Read 115 Mb/sec
Sequential Write 27 Mb/sec
Random Write 25 Mb/sec
Compaq Smart Array 6402
Sequential Read 228 Mb/sec
Random Read 161 Mb/sec
Sequential Write 47 Mb/sec
Random Write 36 Mb/sec
Shocking.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Increasing the CPU's is always a good way to deal with increased load but
> you need to do a few things first.
> Make sure all the code and tables are fully optimized. A bad query or
> lack of index can drag down a good server fast.
> Make sure you have enough ram to keep the relevant data in cache.
> Have a properly configured disk subsystem.
> Set the MAXDOP at the server level to less than the total number of procs
> to allow concurrent queries in peak times.
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
>|||Thank you both your comments.
I have SQL Server 2k/ Windows 2003 EE on DELL PE 6850, 8 GB RAM, 4x3.33Ghz
hyperthreaded CPUs two DELL PERC 4/DC controllers. Curreently i am zooming
along. It is only for future I am worried about.
Thanks for the true RAID 10 tip.
RK
"Paul Cahill" wrote:

> What server do you have? We were running 500 users on a Dell 8450 with 4
> 900Mhz cpus.
> It failed a month ago and we had to quickly swap in a modern Compaq with 2
x
> 3.2ghz hyperthreading cpu's.
> The dell had 24 disks running raid 10 arrays in two split bus PV200s off t
wo
> perc3/dc controllers.
> The compaq has only 6 disks but just wipes the floor with the old dell.
> While I've had the dell down I tried replacing the Dell Perc3's with the
> latest compaq controllers. Dispite the controllers being U320 they have to
> run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. Th
e
> sustained disk throughput increased by a factor of 2 both for reads and
> writes. I was never happy with the perc3's but now the compaq controllers
> proved the point. Note also that dell array manager does not set up raid 1
0
> correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
> have to set up the raid 10 arrays through the dell controller bios to
> achieve true raid 10.
> Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
> channels (dual channel controllers). Write back cache enabled. all stats
> unbuffered by windows.
> Test tool Sisoft Sandra.
> Perc3/DC. Set up by array manager
> Sequential Read 33 Mb/sec
> Random Read 11 Mb/sec
> Sequential Write 22 Mb/sec
> Random Write 19 Mb/sec
> Perc3/DC. Set up bios
> Sequential Read 120 Mb/sec
> Random Read 115 Mb/sec
> Sequential Write 27 Mb/sec
> Random Write 25 Mb/sec
> Compaq Smart Array 6402
> Sequential Read 228 Mb/sec
> Random Read 161 Mb/sec
> Sequential Write 47 Mb/sec
> Random Write 36 Mb/sec
> Shocking.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>

Effect of adding additional CPUs

We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
Enterprise.
What will be the effect on SQL server performance if we add more CPUs ?
Are there any calulations (or numbers available) we can do to determine the
effect of additional CPUs on SQLl server performace ( no of threads, no of
user connections etc)
RK
It has a lot to do with what it is you are doing and how you are doing it.
Since that varies so much there is little a std calculation will achieve.
With more CPU's you have the potential to handle more individual
transactions concurrently. You can also potentially do multi-threaded
operations faster such as DBREINDEX and CHECKDB etc. But that does not
guarantee it will. Do you have processor queue issues now?
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> Enterprise.
> What will be the effect on SQL server performance if we add more CPUs ?
> Are there any calulations (or numbers available) we can do to determine
> the
> effect of additional CPUs on SQLl server performace ( no of threads, no of
> user connections etc)
> --
> RK
|||no I do not have preocessor queue issues as of now. Ours is a web application
with lot of selects but few insert/updates (hotel reservations). The reason I
am doing this is for future growth. The product has to be able to support
five times the current load. I am guessing increasing the load by 5 time will
definately cause CPU queue issues. But Increasing the CPUs- is it a good
route to take to handle more load ? Any good reading material on this
subject.
RK
"Andrew J. Kelly" wrote:

> It has a lot to do with what it is you are doing and how you are doing it.
> Since that varies so much there is little a std calculation will achieve.
> With more CPU's you have the potential to handle more individual
> transactions concurrently. You can also potentially do multi-threaded
> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> guarantee it will. Do you have processor queue issues now?
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>
>
|||Increasing the CPU's is always a good way to deal with increased load but
you need to do a few things first.
Make sure all the code and tables are fully optimized. A bad query or lack
of index can drag down a good server fast.
Make sure you have enough ram to keep the relevant data in cache.
Have a properly configured disk subsystem.
Set the MAXDOP at the server level to less than the total number of procs to
allow concurrent queries in peak times.
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...[vbcol=seagreen]
> no I do not have preocessor queue issues as of now. Ours is a web
> application
> with lot of selects but few insert/updates (hotel reservations). The
> reason I
> am doing this is for future growth. The product has to be able to support
> five times the current load. I am guessing increasing the load by 5 time
> will
> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> route to take to handle more load ? Any good reading material on this
> subject.
> --
> RK
>
> "Andrew J. Kelly" wrote:
|||What server do you have? We were running 500 users on a Dell 8450 with 4
900Mhz cpus.
It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
3.2ghz hyperthreading cpu's.
The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
perc3/dc controllers.
The compaq has only 6 disks but just wipes the floor with the old dell.
While I've had the dell down I tried replacing the Dell Perc3's with the
latest compaq controllers. Dispite the controllers being U320 they have to
run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
sustained disk throughput increased by a factor of 2 both for reads and
writes. I was never happy with the perc3's but now the compaq controllers
proved the point. Note also that dell array manager does not set up raid 10
correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
have to set up the raid 10 arrays through the dell controller bios to
achieve true raid 10.
Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
channels (dual channel controllers). Write back cache enabled. all stats
unbuffered by windows.
Test tool Sisoft Sandra.
Perc3/DC. Set up by array manager
Sequential Read 33 Mb/sec
Random Read 11 Mb/sec
Sequential Write 22 Mb/sec
Random Write 19 Mb/sec
Perc3/DC. Set up bios
Sequential Read 120 Mb/sec
Random Read 115 Mb/sec
Sequential Write 27 Mb/sec
Random Write 25 Mb/sec
Compaq Smart Array 6402
Sequential Read 228 Mb/sec
Random Read 161 Mb/sec
Sequential Write 47 Mb/sec
Random Write 36 Mb/sec
Shocking.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Increasing the CPU's is always a good way to deal with increased load but
> you need to do a few things first.
> Make sure all the code and tables are fully optimized. A bad query or
> lack of index can drag down a good server fast.
> Make sure you have enough ram to keep the relevant data in cache.
> Have a properly configured disk subsystem.
> Set the MAXDOP at the server level to less than the total number of procs
> to allow concurrent queries in peak times.
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
>
|||Thank you both your comments.
I have SQL Server 2k/ Windows 2003 EE on DELL PE 6850, 8 GB RAM, 4x3.33Ghz
hyperthreaded CPUs two DELL PERC 4/DC controllers. Curreently i am zooming
along. It is only for future I am worried about.
Thanks for the true RAID 10 tip.
RK
"Paul Cahill" wrote:

> What server do you have? We were running 500 users on a Dell 8450 with 4
> 900Mhz cpus.
> It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
> 3.2ghz hyperthreading cpu's.
> The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
> perc3/dc controllers.
> The compaq has only 6 disks but just wipes the floor with the old dell.
> While I've had the dell down I tried replacing the Dell Perc3's with the
> latest compaq controllers. Dispite the controllers being U320 they have to
> run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
> sustained disk throughput increased by a factor of 2 both for reads and
> writes. I was never happy with the perc3's but now the compaq controllers
> proved the point. Note also that dell array manager does not set up raid 10
> correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
> have to set up the raid 10 arrays through the dell controller bios to
> achieve true raid 10.
> Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
> channels (dual channel controllers). Write back cache enabled. all stats
> unbuffered by windows.
> Test tool Sisoft Sandra.
> Perc3/DC. Set up by array manager
> Sequential Read 33 Mb/sec
> Random Read 11 Mb/sec
> Sequential Write 22 Mb/sec
> Random Write 19 Mb/sec
> Perc3/DC. Set up bios
> Sequential Read 120 Mb/sec
> Random Read 115 Mb/sec
> Sequential Write 27 Mb/sec
> Random Write 25 Mb/sec
> Compaq Smart Array 6402
> Sequential Read 228 Mb/sec
> Random Read 161 Mb/sec
> Sequential Write 47 Mb/sec
> Random Write 36 Mb/sec
> Shocking.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>

Effect of adding additional CPUs

We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
Enterprise.
What will be the effect on SQL server performance if we add more CPUs ?
Are there any calulations (or numbers available) we can do to determine the
effect of additional CPUs on SQLl server performace ( no of threads, no of
user connections etc)
--
RKIt has a lot to do with what it is you are doing and how you are doing it.
Since that varies so much there is little a std calculation will achieve.
With more CPU's you have the potential to handle more individual
transactions concurrently. You can also potentially do multi-threaded
operations faster such as DBREINDEX and CHECKDB etc. But that does not
guarantee it will. Do you have processor queue issues now?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> Enterprise.
> What will be the effect on SQL server performance if we add more CPUs ?
> Are there any calulations (or numbers available) we can do to determine
> the
> effect of additional CPUs on SQLl server performace ( no of threads, no of
> user connections etc)
> --
> RK|||no I do not have preocessor queue issues as of now. Ours is a web application
with lot of selects but few insert/updates (hotel reservations). The reason I
am doing this is for future growth. The product has to be able to support
five times the current load. I am guessing increasing the load by 5 time will
definately cause CPU queue issues. But Increasing the CPUs- is it a good
route to take to handle more load ? Any good reading material on this
subject.
--
RK
"Andrew J. Kelly" wrote:
> It has a lot to do with what it is you are doing and how you are doing it.
> Since that varies so much there is little a std calculation will achieve.
> With more CPU's you have the potential to handle more individual
> transactions concurrently. You can also potentially do multi-threaded
> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> guarantee it will. Do you have processor queue issues now?
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> > Enterprise.
> >
> > What will be the effect on SQL server performance if we add more CPUs ?
> > Are there any calulations (or numbers available) we can do to determine
> > the
> > effect of additional CPUs on SQLl server performace ( no of threads, no of
> > user connections etc)
> > --
> > RK
>
>|||Increasing the CPU's is always a good way to deal with increased load but
you need to do a few things first.
Make sure all the code and tables are fully optimized. A bad query or lack
of index can drag down a good server fast.
Make sure you have enough ram to keep the relevant data in cache.
Have a properly configured disk subsystem.
Set the MAXDOP at the server level to less than the total number of procs to
allow concurrent queries in peak times.
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> no I do not have preocessor queue issues as of now. Ours is a web
> application
> with lot of selects but few insert/updates (hotel reservations). The
> reason I
> am doing this is for future growth. The product has to be able to support
> five times the current load. I am guessing increasing the load by 5 time
> will
> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> route to take to handle more load ? Any good reading material on this
> subject.
> --
> RK
>
> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs ?
>> > Are there any calulations (or numbers available) we can do to determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads, no
>> > of
>> > user connections etc)
>> > --
>> > RK
>>|||What server do you have? We were running 500 users on a Dell 8450 with 4
900Mhz cpus.
It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
3.2ghz hyperthreading cpu's.
The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
perc3/dc controllers.
The compaq has only 6 disks but just wipes the floor with the old dell.
While I've had the dell down I tried replacing the Dell Perc3's with the
latest compaq controllers. Dispite the controllers being U320 they have to
run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
sustained disk throughput increased by a factor of 2 both for reads and
writes. I was never happy with the perc3's but now the compaq controllers
proved the point. Note also that dell array manager does not set up raid 10
correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
have to set up the raid 10 arrays through the dell controller bios to
achieve true raid 10.
Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
channels (dual channel controllers). Write back cache enabled. all stats
unbuffered by windows.
Test tool Sisoft Sandra.
Perc3/DC. Set up by array manager
Sequential Read 33 Mb/sec
Random Read 11 Mb/sec
Sequential Write 22 Mb/sec
Random Write 19 Mb/sec
Perc3/DC. Set up bios
Sequential Read 120 Mb/sec
Random Read 115 Mb/sec
Sequential Write 27 Mb/sec
Random Write 25 Mb/sec
Compaq Smart Array 6402
Sequential Read 228 Mb/sec
Random Read 161 Mb/sec
Sequential Write 47 Mb/sec
Random Write 36 Mb/sec
Shocking.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Increasing the CPU's is always a good way to deal with increased load but
> you need to do a few things first.
> Make sure all the code and tables are fully optimized. A bad query or
> lack of index can drag down a good server fast.
> Make sure you have enough ram to keep the relevant data in cache.
> Have a properly configured disk subsystem.
> Set the MAXDOP at the server level to less than the total number of procs
> to allow concurrent queries in peak times.
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
>> no I do not have preocessor queue issues as of now. Ours is a web
>> application
>> with lot of selects but few insert/updates (hotel reservations). The
>> reason I
>> am doing this is for future growth. The product has to be able to support
>> five times the current load. I am guessing increasing the load by 5 time
>> will
>> definately cause CPU queue issues. But Increasing the CPUs- is it a good
>> route to take to handle more load ? Any good reading material on this
>> subject.
>> --
>> RK
>>
>> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will
>> achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs
>> > ?
>> > Are there any calulations (or numbers available) we can do to
>> > determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads,
>> > no of
>> > user connections etc)
>> > --
>> > RK
>>
>|||Thank you both your comments.
I have SQL Server 2k/ Windows 2003 EE on DELL PE 6850, 8 GB RAM, 4x3.33Ghz
hyperthreaded CPUs two DELL PERC 4/DC controllers. Curreently i am zooming
along. It is only for future I am worried about.
Thanks for the true RAID 10 tip.
--
RK
"Paul Cahill" wrote:
> What server do you have? We were running 500 users on a Dell 8450 with 4
> 900Mhz cpus.
> It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
> 3.2ghz hyperthreading cpu's.
> The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
> perc3/dc controllers.
> The compaq has only 6 disks but just wipes the floor with the old dell.
> While I've had the dell down I tried replacing the Dell Perc3's with the
> latest compaq controllers. Dispite the controllers being U320 they have to
> run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
> sustained disk throughput increased by a factor of 2 both for reads and
> writes. I was never happy with the perc3's but now the compaq controllers
> proved the point. Note also that dell array manager does not set up raid 10
> correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
> have to set up the raid 10 arrays through the dell controller bios to
> achieve true raid 10.
> Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
> channels (dual channel controllers). Write back cache enabled. all stats
> unbuffered by windows.
> Test tool Sisoft Sandra.
> Perc3/DC. Set up by array manager
> Sequential Read 33 Mb/sec
> Random Read 11 Mb/sec
> Sequential Write 22 Mb/sec
> Random Write 19 Mb/sec
> Perc3/DC. Set up bios
> Sequential Read 120 Mb/sec
> Random Read 115 Mb/sec
> Sequential Write 27 Mb/sec
> Random Write 25 Mb/sec
> Compaq Smart Array 6402
> Sequential Read 228 Mb/sec
> Random Read 161 Mb/sec
> Sequential Write 47 Mb/sec
> Random Write 36 Mb/sec
> Shocking.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> > Increasing the CPU's is always a good way to deal with increased load but
> > you need to do a few things first.
> >
> > Make sure all the code and tables are fully optimized. A bad query or
> > lack of index can drag down a good server fast.
> > Make sure you have enough ram to keep the relevant data in cache.
> > Have a properly configured disk subsystem.
> > Set the MAXDOP at the server level to less than the total number of procs
> > to allow concurrent queries in peak times.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "RK73" <RK73@.discussions.microsoft.com> wrote in message
> > news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> >> no I do not have preocessor queue issues as of now. Ours is a web
> >> application
> >> with lot of selects but few insert/updates (hotel reservations). The
> >> reason I
> >> am doing this is for future growth. The product has to be able to support
> >> five times the current load. I am guessing increasing the load by 5 time
> >> will
> >> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> >> route to take to handle more load ? Any good reading material on this
> >> subject.
> >>
> >> --
> >> RK
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> It has a lot to do with what it is you are doing and how you are doing
> >> it.
> >> Since that varies so much there is little a std calculation will
> >> achieve.
> >> With more CPU's you have the potential to handle more individual
> >> transactions concurrently. You can also potentially do multi-threaded
> >> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> >> guarantee it will. Do you have processor queue issues now?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> >> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> >> > Enterprise.
> >> >
> >> > What will be the effect on SQL server performance if we add more CPUs
> >> > ?
> >> > Are there any calulations (or numbers available) we can do to
> >> > determine
> >> > the
> >> > effect of additional CPUs on SQLl server performace ( no of threads,
> >> > no of
> >> > user connections etc)
> >> > --
> >> > RK
> >>
> >>
> >>
> >
> >
>
>