performance degradation

I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
You have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server_...nce_tuning.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you

performance degradation

I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank youYou have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server...ance_tuning.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you

Performance decrease after OS upgrade

We were running a 4 processor server with 8 GB RAM and 800GB hard disk
space on Windows 2000 server and SQL Server 2000.
Recently we have upgraded the server to 8 processor and added another
800GB. The OS was upgradded to Windows 2003 enterprise edition.
All the processor are 2.5GHz Xeon preocessors.

After upgradation the performance of the server has gone down from what
it was giving before the upgrade.

It seems that multiprocessing is not ocurring.

The model of the server is HP DL740

The OS is installed in a built in array(5i controller) of the server
and the SQL server is installed in a external array(6400 controller).

I will really aprecite if anyone can give any clue to improve the
performance.

Thanks in advance.

Taw.Really need more details... One thought, when you added the 800 GB of
disk space -- I assumed you added a new array? How was it configured?
i.e. Was the existing array named drive E and then the new array named
drive F? Or did you span it into one giant 1.6T array?|||Before the upgrade the OS(Windows200) and the SQLServer was both in the
external array. OS was in one logical drive (C:) and Sqlserver in
another logical drive (D:). The built in array was not used.

For upgrading the system we have added 2X36.4 GB harddisk in the built
in array and clean installed the OS(Windows 2003). Now there is one
1.6TB (14X146 GB) external array,RAID 5. The external array is the
logical drive D: and the OS(RAID1) is the logical drive C:|||Taw,

From the limited information provided, your biggest performance hit here is
the use of RAID 5. RAID1 is much faster, especially for the transaction
log. If we assume that the configuration is generally the same just more
processors and more spindles, I would look into pulling back your max degree
of parallelism.

Also is SQL completely installed on the external array? The default install
is to the C: drive and this may be where tempdb is located.

<tawfiq.choudhury@.grameenphone.com> wrote in message
news:1108442363.222496.139900@.g14g2000cwa.googlegr oups.com...
> Before the upgrade the OS(Windows200) and the SQLServer was both in the
> external array. OS was in one logical drive (C:) and Sqlserver in
> another logical drive (D:). The built in array was not used.
> For upgrading the system we have added 2X36.4 GB harddisk in the built
> in array and clean installed the OS(Windows 2003). Now there is one
> 1.6TB (14X146 GB) external array,RAID 5. The external array is the
> logical drive D: and the OS(RAID1) is the logical drive C:|||My only guess is that you have a "HP Modular Smart Array 30". I
remember buried somewhere in it's documentation, the optimal number of
disks per logical drive is 8. But the array is top-of-the-line and
having 14 disks in one logical drive shouldn't impact it that much.
You can try playing with the Parallellism Query Plan Threshold setting.
Otherwise, sorry, I have no other ideas.|||Yes SQL is installed completely in the external array.

Performance Counter

Hi,
Is there a way to access non Sql Server related performance counters from
Sql Server.
Like % Processor time or % Disk Time?
I'm trying to build a table to establish trends and would like to collect
all the data from via a sp.
Thx
You could try
DBCC sqlperf(THREADS)
Tobes
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>
|||Non- SQL Server perf counters are NOT stored in SQL the same way that the
SQL Counters are..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>
|||You can write Windows performance counters to SQL Server database tables
using the Windows 2003 server or XP performance monitor tool. Create a ODBC
system DSN for your database repository and specify the DSN under log
settings. The account used for the 'Performance logs and alerts' service
needs to be granted full access to the database.
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>
|||Thx,
I've tried this and have tested the ODBC connection which is successfull.
However when I go to start the counter log it fails and the application
event viewer has the following message:
Description:
18456 :
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
I then went and added a local group on the server and added 'NT
AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for this
local group to Sql server and added it as a user to a repository db with full
permissions on the db... get the same error.
Thx again.
"Dan Guzman" wrote:

> You can write Windows performance counters to SQL Server database tables
> using the Windows 2003 server or XP performance monitor tool. Create a ODBC
> system DSN for your database repository and specify the DSN under log
> settings. The account used for the 'Performance logs and alerts' service
> needs to be granted full access to the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
>
>
|||You might try using a domain account for the 'Performance logs and alerts'
service. This has always worked for me.
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...[vbcol=seagreen]
> Thx,
> I've tried this and have tested the ODBC connection which is successfull.
> However when I go to start the counter log it fails and the application
> event viewer has the following message:
> Description:
> 18456 :
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> I then went and added a local group on the server and added 'NT
> AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> this
> local group to Sql server and added it as a user to a repository db with
> full
> permissions on the db... get the same error.
> Thx again.
>
>
> "Dan Guzman" wrote:
|||Thx, that worked.
"Dan Guzman" wrote:

> You might try using a domain account for the 'Performance logs and alerts'
> service. This has always worked for me.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
>
>

Performance Counter

Hi,
Is there a way to access non Sql Server related performance counters from
Sql Server.
Like % Processor time or % Disk Time?
I'm trying to build a table to establish trends and would like to collect
all the data from via a sp.
ThxYou could try
DBCC sqlperf(THREADS)
Tobes
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Non- SQL Server perf counters are NOT stored in SQL the same way that the
SQL Counters are..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||You can write Windows performance counters to SQL Server database tables
using the Windows 2003 server or XP performance monitor tool. Create a ODBC
system DSN for your database repository and specify the DSN under log
settings. The account used for the 'Performance logs and alerts' service
needs to be granted full access to the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Thx,
I've tried this and have tested the ODBC connection which is successfull.
However when I go to start the counter log it fails and the application
event viewer has the following message:
Description:
18456 :
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
I then went and added a local group on the server and added 'NT
AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for this
local group to Sql server and added it as a user to a repository db with full
permissions on the db... get the same error.
Thx again.
"Dan Guzman" wrote:
> You can write Windows performance counters to SQL Server database tables
> using the Windows 2003 server or XP performance monitor tool. Create a ODBC
> system DSN for your database repository and specify the DSN under log
> settings. The account used for the 'Performance logs and alerts' service
> needs to be granted full access to the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> > Hi,
> >
> > Is there a way to access non Sql Server related performance counters from
> > Sql Server.
> > Like % Processor time or % Disk Time?
> >
> > I'm trying to build a table to establish trends and would like to collect
> > all the data from via a sp.
> >
> > Thx
> >
> >
>
>|||You might try using a domain account for the 'Performance logs and alerts'
service. This has always worked for me.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
> Thx,
> I've tried this and have tested the ODBC connection which is successfull.
> However when I go to start the counter log it fails and the application
> event viewer has the following message:
> Description:
> 18456 :
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> I then went and added a local group on the server and added 'NT
> AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> this
> local group to Sql server and added it as a user to a repository db with
> full
> permissions on the db... get the same error.
> Thx again.
>
>
> "Dan Guzman" wrote:
>> You can write Windows performance counters to SQL Server database tables
>> using the Windows 2003 server or XP performance monitor tool. Create a
>> ODBC
>> system DSN for your database repository and specify the DSN under log
>> settings. The account used for the 'Performance logs and alerts' service
>> needs to be granted full access to the database.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "ap001" <ap001@.discussions.microsoft.com> wrote in message
>> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
>> > Hi,
>> >
>> > Is there a way to access non Sql Server related performance counters
>> > from
>> > Sql Server.
>> > Like % Processor time or % Disk Time?
>> >
>> > I'm trying to build a table to establish trends and would like to
>> > collect
>> > all the data from via a sp.
>> >
>> > Thx
>> >
>> >
>>|||Thx, that worked.
"Dan Guzman" wrote:
> You might try using a domain account for the 'Performance logs and alerts'
> service. This has always worked for me.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
> > Thx,
> >
> > I've tried this and have tested the ODBC connection which is successfull.
> > However when I go to start the counter log it fails and the application
> > event viewer has the following message:
> > Description:
> > 18456 :
> > Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> >
> > I then went and added a local group on the server and added 'NT
> > AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> > this
> > local group to Sql server and added it as a user to a repository db with
> > full
> > permissions on the db... get the same error.
> >
> > Thx again.
> >
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> You can write Windows performance counters to SQL Server database tables
> >> using the Windows 2003 server or XP performance monitor tool. Create a
> >> ODBC
> >> system DSN for your database repository and specify the DSN under log
> >> settings. The account used for the 'Performance logs and alerts' service
> >> needs to be granted full access to the database.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> >> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> >> > Hi,
> >> >
> >> > Is there a way to access non Sql Server related performance counters
> >> > from
> >> > Sql Server.
> >> > Like % Processor time or % Disk Time?
> >> >
> >> > I'm trying to build a table to establish trends and would like to
> >> > collect
> >> > all the data from via a sp.
> >> >
> >> > Thx
> >> >
> >> >
> >>
> >>
> >>
>
>sql

Performance Counter

Hi,
Is there a way to access non Sql Server related performance counters from
Sql Server.
Like % Processor time or % Disk Time?
I'm trying to build a table to establish trends and would like to collect
all the data from via a sp.
ThxYou could try
DBCC sqlperf(THREADS)
Tobes
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Non- SQL Server perf counters are NOT stored in SQL the same way that the
SQL Counters are..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||You can write Windows performance counters to SQL Server database tables
using the Windows 2003 server or XP performance monitor tool. Create a ODBC
system DSN for your database repository and specify the DSN under log
settings. The account used for the 'Performance logs and alerts' service
needs to be granted full access to the database.
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Thx,
I've tried this and have tested the ODBC connection which is successfull.
However when I go to start the counter log it fails and the application
event viewer has the following message:
Description:
18456 :
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
I then went and added a local group on the server and added 'NT
AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for thi
s
local group to Sql server and added it as a user to a repository db with ful
l
permissions on the db... get the same error.
Thx again.
"Dan Guzman" wrote:

> You can write Windows performance counters to SQL Server database tables
> using the Windows 2003 server or XP performance monitor tool. Create a OD
BC
> system DSN for your database repository and specify the DSN under log
> settings. The account used for the 'Performance logs and alerts' service
> needs to be granted full access to the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
>
>|||You might try using a domain account for the 'Performance logs and alerts'
service. This has always worked for me.
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...[vbcol=seagreen]
> Thx,
> I've tried this and have tested the ODBC connection which is successfull.
> However when I go to start the counter log it fails and the application
> event viewer has the following message:
> Description:
> 18456 :
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> I then went and added a local group on the server and added 'NT
> AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> this
> local group to Sql server and added it as a user to a repository db with
> full
> permissions on the db... get the same error.
> Thx again.
>
>
> "Dan Guzman" wrote:
>|||Thx, that worked.
"Dan Guzman" wrote:

> You might try using a domain account for the 'Performance logs and alerts'
> service. This has always worked for me.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
>
>

Performance 1proc vs. 2proc

We are purchasing a new Win2003 Server with SQL Server Standard. Due to the
pricing structure of PER PROCESSOR pricing, can someone tell me is the
benefit of a 2nd processor worth the cost on a database server that is
primarily a backend to multiple web based apps?
ThanksMSUTech,
There is no way to say without knowing your load. It is not a matter of the
client (e.g. web app, VB app, etc.) but a matter of the kind of queries that
you are running. If your workload is not very heavy and the queries return
only small row sets, then you are probably fine.
However, a second processor can come in very handy with a greater load or a
mixture of long and short running queries. Why? Because it actually has
another physical processor (and that does matter) to run additional requests
when one processor is very busy. Most of the benefits you can get with a
multi-core processor (not hyperthreaded) since the cores are mostly
independent AND because Microsoft views a multi-core processor as a single
processor. (Very nice of them.)
But, bottom line is that you have to figure the cost/benefit based on your
workload. How much time do you spend waiting? How long do your average and
maximum length queries run? How much are you updating the database? What
kind of locking or blocking are you seeing?
And get a multi-core processor since it has no impact on your licensing.
RLF
"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
> the
> pricing structure of PER PROCESSOR pricing, can someone tell me is the
> benefit of a 2nd processor worth the cost on a database server that is
> primarily a backend to multiple web based apps?
> Thanks|||Russell,
If you have more than 1 processor then at the very least SQL and have its
own processor and Windows and other processes could run on the other
processor if you configure the server that way. Sometimes we forget the
processes that Windows wants/needs to do while SQL is running.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
> MSUTech,
> There is no way to say without knowing your load. It is not a matter of
> the client (e.g. web app, VB app, etc.) but a matter of the kind of
> queries that you are running. If your workload is not very heavy and the
> queries return only small row sets, then you are probably fine.
> However, a second processor can come in very handy with a greater load or
> a mixture of long and short running queries. Why? Because it actually
> has another physical processor (and that does matter) to run additional
> requests when one processor is very busy. Most of the benefits you can
> get with a multi-core processor (not hyperthreaded) since the cores are
> mostly independent AND because Microsoft views a multi-core processor as a
> single processor. (Very nice of them.)
> But, bottom line is that you have to figure the cost/benefit based on your
> workload. How much time do you spend waiting? How long do your average
> and maximum length queries run? How much are you updating the database?
> What kind of locking or blocking are you seeing?
> And get a multi-core processor since it has no impact on your licensing.
> RLF
> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>|||Chris,
True enough. For example, even staying within the SQL Server domain,
running DTS / SSIS is another process.
If you have a dual processor machine, however, you need to license SQL
Server for two processors. The licensing does not have a provision for
promising never to use the other processor, although it is possible to do
so.
But a multi-core (dual or quad) processor brings most of the benefits of two
processors while still being licensed as a single processor. So, the
multi-core processor benefits would extend to Windows and other processes
that might run on the machine.
RLF
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
> Russell,
> If you have more than 1 processor then at the very least SQL and have its
> own processor and Windows and other processes could run on the other
> processor if you configure the server that way. Sometimes we forget the
> processes that Windows wants/needs to do while SQL is running.
> Chris
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and the
>> queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load or
>> a mixture of long and short running queries. Why? Because it actually
>> has another physical processor (and that does matter) to run additional
>> requests when one processor is very busy. Most of the benefits you can
>> get with a multi-core processor (not hyperthreaded) since the cores are
>> mostly independent AND because Microsoft views a multi-core processor as
>> a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>|||I should have been a little clearer with multiple CPU's meaning multi-core
for licencing benefits.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e3Bs3PvpIHA.4928@.TK2MSFTNGP04.phx.gbl...
> Chris,
> True enough. For example, even staying within the SQL Server domain,
> running DTS / SSIS is another process.
> If you have a dual processor machine, however, you need to license SQL
> Server for two processors. The licensing does not have a provision for
> promising never to use the other processor, although it is possible to do
> so.
> But a multi-core (dual or quad) processor brings most of the benefits of
> two processors while still being licensed as a single processor. So, the
> multi-core processor benefits would extend to Windows and other processes
> that might run on the machine.
> RLF
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
>> Russell,
>> If you have more than 1 processor then at the very least SQL and have its
>> own processor and Windows and other processes could run on the other
>> processor if you configure the server that way. Sometimes we forget the
>> processes that Windows wants/needs to do while SQL is running.
>> Chris
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and
>> the queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load
>> or a mixture of long and short running queries. Why? Because it
>> actually has another physical processor (and that does matter) to run
>> additional requests when one processor is very busy. Most of the
>> benefits you can get with a multi-core processor (not hyperthreaded)
>> since the cores are mostly independent AND because Microsoft views a
>> multi-core processor as a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due
>> to the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>>
>

Performance

I'm distributing a table over four machines with different
specs.
How heavily should I weigh...
1. Processor (P3's and P4's)
2. Processor clock speed
3. Memory
4. Bus speed
5. Anything else?
...when deciding who gets more work and who gets less.
Let's say it's an OLAP system, but if anyone would like
to answer for OLTP, I'd appreciate it, too.
Thank you in advance,
Andrewin the absence of more information on your application,
look up the SPEC CPU 2000 integer base performance for
each proc (www.spec.org)
>--Original Message--
>I'm distributing a table over four machines with different
>specs.
>How heavily should I weigh...
>1. Processor (P3's and P4's)
>2. Processor clock speed
>3. Memory
>4. Bus speed
>5. Anything else?
>...when deciding who gets more work and who gets less.
>Let's say it's an OLAP system, but if anyone would like
>to answer for OLTP, I'd appreciate it, too.
>Thank you in advance,
>Andrew
>.
>

Performance

I am trying to improve performance on my SQL 2000
Server. In my performance monitoring, my % processor
time hovers around the 35% to 40% mark. The memory seems
to be sufficient to support our needs, but users are
complaining that the system is slow.
I can identify which databases are being used that causes
the slow response time. I would like to add some indexs
on a few tables. Can I add indexes while users are in
the system?
Any other suggestions to decrease the % processor time,
besides upgrading the processors on the server?
Thank you,
JLFlemingHi
%30-40 avg processor time did not show that you have a bottleneck with
processor.
It seems like you have slow running queries or locking-blocking issues..
I think it would be better for you to analyze you're queries with sql
profiler. And start to optimize or re-write them.
Creating index on tables will block users to access to that tables until
index creation is complete.
And my suggestion is to decide which index to create according to your
queries.
You can use SQL Profiler to capture the trace's and Index Tuning Wizard to
analyze them. (You better use ITW against the db on the test server using the
trace file that you capture from production)
Regards..
"JLFleming" wrote:
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
>|||You might want to have a looka t these below. Low processor utilization can
mean several things but one of the most common is that they are waiting on
Disk I/O to complete. If you have a disk bottleneck the processors can't do
much. The same is true for blocking. Have you run sp_who2 to see if anyone
is being blocked?
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
"JLFleming" <anonymous@.discussions.microsoft.com> wrote in message
news:066e01c50eac$063c8340$a401280a@.phx.gbl...
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming|||I have very little to add to what these guys have said,
however you may also need to look at Disk Raiding and
Network speed.
Other than that is your system used as a data input tool,
reporting tool or a bit of both.
Peter
peternolan67REMOVETHIS@.hotmail.com
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>I am trying to improve performance on my SQL 2000
>Server. In my performance monitoring, my % processor
>time hovers around the 35% to 40% mark. The memory seems
>to be sufficient to support our needs, but users are
>complaining that the system is slow.
>I can identify which databases are being used that causes
>the slow response time. I would like to add some indexs
>on a few tables. Can I add indexes while users are in
>the system?
>Any other suggestions to decrease the % processor time,
>besides upgrading the processors on the server?
>Thank you,
>JLFleming
>.
>|||Our system is mostly used as a data input tool. We do
some reporting, but not much.
>--Original Message--
>I have very little to add to what these guys have said,
>however you may also need to look at Disk Raiding and
>Network speed.
>Other than that is your system used as a data input
tool,
>reporting tool or a bit of both.
>Peter
>peternolan67REMOVETHIS@.hotmail.com
>"It's true hard work never killed anybody, but I figure,
>why take the chance?"
>Ronald Reagan
>
>>--Original Message--
>>I am trying to improve performance on my SQL 2000
>>Server. In my performance monitoring, my % processor
>>time hovers around the 35% to 40% mark. The memory
seems
>>to be sufficient to support our needs, but users are
>>complaining that the system is slow.
>>I can identify which databases are being used that
causes
>>the slow response time. I would like to add some
indexs
>>on a few tables. Can I add indexes while users are in
>>the system?
>>Any other suggestions to decrease the % processor time,
>>besides upgrading the processors on the server?
>>Thank you,
>>JLFleming
>>.
>.
>

perfomance related questions

Why would a stored procedure take longer to run on an OLTP environment
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>