SQL Server Performance  
Author Message
Meeffas





PostPosted: Tue Apr 12 06:38:23 CDT 2005 Top

SQL Server >> SQL Server Performance

Hi,
I want to know, how do I increase the CPU usage allocated for Microsoft SQL
Server...

Currently it only goes as high as 50%... I want to go upto 95-100% for
certain processes.

Regards
RV

SQL Server130  
 
 
Wayne





PostPosted: Tue Apr 12 06:38:23 CDT 2005 Top

SQL Server >> SQL Server Performance SQL receives CPU resources as scheduled from the OS ( sort of.)

You may set in SQL Enterprise Manager WHICH CPUS SQL will use... You can
even set a higher priority ( generally not recommended ) using
sp_configure..

Either SQL does not need the extra CPU power OR ( more likely ) your server
is IO Bound and is waiting for IOS to complete...(And therefore not using
more CPU.)

--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, C****te, NC
(Please respond only to the newsgroup.)

I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.


> Hi,
> I want to know, how do I increase the CPU usage allocated for Microsoft
SQL
> Server...
>
> Currently it only goes as high as 50%... I want to go upto 95-100% for
> certain processes.
>
> Regards
> RV
>


 
 
mike





PostPosted: Tue Apr 12 06:53:49 CDT 2005 Top

SQL Server >> SQL Server Performance Hi

SQL Server will use 100% of the processor if it needs it. You can't limit it.
You can limit which processors it uses in a multi-processor machine in
Properties of the Server in EM.

If you have performance problems, with low CPU utilization, monitor your
disks. If the CPU waits for disk I/O, it can't do much else.

If you have 2+ processors, and run a query, and only 1 processor is used,
then the query can not be spilt up by SQL Server for execution.

Regards
Mike



> Hi,
> I want to know, how do I increase the CPU usage allocated for Microsoft SQL
> Server...
>
> Currently it only goes as high as 50%... I want to go upto 95-100% for
> certain processes.
>
> Regards
> RV
>
 
 
Gert-Jan





PostPosted: Tue Apr 12 14:07:35 CDT 2005 Top

SQL Server >> SQL Server Performance RV,

SQL-Server goal is to return the requested resultset as fast as
possible, not to max out the CPU's. You can possibly lower the
parallelism threshold on SMP systems (so the change of parallel
execution is increased which usually uses more CPU than the serial plan)
and add HASH joint hints to the queries (which typically use many CPU
cycles).

If SQL-Server does not use much CPU, then the performance will probably
not increase by forcing SQL-Server to use more CPU cycles. So why would
you want to do that?

Gert-Jan



>
> Hi,
> I want to know, how do I increase the CPU usage allocated for Microsoft SQL
> Server...
>
> Currently it only goes as high as 50%... I want to go upto 95-100% for
> certain processes.
>
> Regards
> RV