Hi, Ive been told this, but I hope it is NOT true. I have an sql
server2000 installation running on a server that has four processors.
It is on a active network but is not the domain controller so
essentially it is fully dedicated to servicing the needs of sql
server, (a bit of browsing, a bit of ms Office, but almost wholly
dedicated to sqlserver. Now, the big question, why, when the server
properties have been set to utilize all four processors, can any one
job never get more than 25% of cpu time? I can launch multiple
instance of QA and run the same job on each one and that will utilise
more and more cpu time, but if you launch multile QA windows from
within one insance of QA, you can NEVER get more than 25% CPU
utilisation. Now i have to run a job (FTS is a good example,
re-indexing lots of db's another, or even a huge query with multiple
ufd's on computed cols which I hoped would grab lots of CPU time that
they need, but no. So do I have to live with this or can I tell either
windows or sql server to grab more cpu when it want to ie use my spare
CPU capacity more efficiently or am i working on a misguided premise
and 25% per job is your lot?
DMAC 4 2311
>>Hi, Ive been told this, but I hope it is NOT true. I have an sql server2000 installation running on a server that has four processors. It is on a active network but is not the domain controller so essentially it is fully dedicated to servicing the needs of sql server, (a bit of browsing, a bit of ms Office, but almost wholly dedicated to sqlserver. Now, the big question, why, when the server properties have been set to utilize all four processors, can any one job never get more than 25% of cpu time?
MSSQL can utilize 'parallelism' to make use of multi processors. But
it doesn't work half the time. Unless you have a single user, it is
better for MSSQL to save those other processors for other SPIDS.
I can launch multiple instance of QA and run the same job on each one and that will utilise more and more cpu time, but if you launch multile QA windows from within one insance of QA, you can NEVER get more than 25% CPU utilisation . Now i have to run a job (FTS is a good example, re-indexing lots of db's another, or even a huge query with multiple ufd's on computed cols which I hoped would grab lots of CPU time that they need, but no. So do I have to live with this or can I tell
eitherwindows or sql server to grab more cpu when it want to ie use my
spareCPU capacity more efficiently or am i working on a misguided premise and 25% per job is your lot?
What's FTS & UFDs? User defined function?
The SQL Server optimizer will generate a parallel plan only when it makes
sense to do so and the current server workload permits it. Many queries
will not benefit from a parallel plan. In practice, parallelism can be a
symptom of needed indexes or poorly formulated query. I can launch multiple instance of QA and run the same job on each one and that will utilise more and more cpu time, but if you launch multile QA windows from within one insance of QA, you can NEVER get more than 25% CPU utilisation.
This is not consistent with my experience. For example, I see both
processors fully used on my dual-cpu box by running the following query from
within the same QA instance. Do you get similar results?
USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DMAC" <dr***@drmcl.fr ee-online.co.uk> wrote in message
news:46******** *************** ***@posting.goo gle.com... Hi, Ive been told this, but I hope it is NOT true. I have an sql server2000 installation running on a server that has four processors. It is on a active network but is not the domain controller so essentially it is fully dedicated to servicing the needs of sql server, (a bit of browsing, a bit of ms Office, but almost wholly dedicated to sqlserver. Now, the big question, why, when the server properties have been set to utilize all four processors, can any one job never get more than 25% of cpu time? I can launch multiple instance of QA and run the same job on each one and that will utilise more and more cpu time, but if you launch multile QA windows from within one insance of QA, you can NEVER get more than 25% CPU utilisation. Now i have to run a job (FTS is a good example, re-indexing lots of db's another, or even a huge query with multiple ufd's on computed cols which I hoped would grab lots of CPU time that they need, but no. So do I have to live with this or can I tell either windows or sql server to grab more cpu when it want to ie use my spare CPU capacity more efficiently or am i working on a misguided premise and 25% per job is your lot?
DMAC
Thanks Dan,
Your query did indeed put all four processors into overdrive, (took me
a while to cancel it cos I could not get my mouse click over the wire)
so my problem probably lies with your first suggestion about poorly
formed queries. Is there any mechanism to influence the optimiser or
thread selection to have my one really bad query utilise its own
processor( or just generally so that I can keep developement to its own
cpu/thread combo), leaving everthing else to utilize the other
processors, ie why did sql server immediately grap all the cpu time from
your query?
Cheers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
> Is there any mechanism to influence the optimiser or thread selection to have my one really bad query utilise its own processor( or just generally so that I can keep developement to its own cpu/thread combo), leaving everthing else to utilize the other processors,
You can specify a MAXDOP hint to limit parallelism to the specified number
of processors: for a particular query:
USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
OPTION (MAXDOP 1)
GO
You can also adjust the server-wide setting with the 'max degree of
parallelism' configuration option. On a server with 4 or more processors, I
usually use this option to specify fewer processors than are available (e.g.
3) so that a single query won't monopolize CPU resources.
ie why did sql server immediately grap all the cpu time from your query?
When SQL Server determines a query can benefit from parallelism, it
considers the current server workload and adjusts the number of parallel
threads accordingly. The optimizer may choose to use a single thread or
fewer processors when the machine is busy and a more aggressive plan when
not currently busy.
It's a good practice to segregate development and production on different
servers when possible.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<DM**@devdex.co m> wrote in message news:41******** **@127.0.0.1... Thanks Dan,
Your query did indeed put all four processors into overdrive, (took me a while to cancel it cos I could not get my mouse click over the wire) so my problem probably lies with your first suggestion about poorly formed queries. Is there any mechanism to influence the optimiser or thread selection to have my one really bad query utilise its own processor( or just generally so that I can keep developement to its own cpu/thread combo), leaving everthing else to utilize the other processors, ie why did sql server immediately grap all the cpu time from your query?
Cheers
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Thomas Womack |
last post by:
If I have a dual-processor hyperthreaded machine (so with four CPU
contexts), will a python program distribute threads over all four
logical processors?
I ask because I'm fairly sure that this *does* happen using the
threading extensions in MFC, and fairly sure that it *doesn't* when
using Java, so I don't see that the result is obvious for python.
Tom
|
by: ajikoe |
last post by:
Hello,
Is anyone has experiance in running python code to run multi thread
parallel in multi processor. Is it possible ?
Can python manage which cpu shoud do every thread?
Sincerely Yours,
Pujo
|
by: seesharp |
last post by:
Hi,
we have a multithreaded application (win service) written
in C# that is running well on all but one box. The only
difference we see between the problem box and the good
ones is that the problem box is a dual processor system.
MS professional support has analyzed a Dr. Watson dump,
and the problem occurs in a 3rd party component we use
(a .dll). So far no good replies from the vendor of this
|
by: Amit Dedhia |
last post by:
Hi
I am developing a Dot net application (involving image processing) on
a uni processor. It works well on my machine. I then take all my code
on a multi processor, build and run the application there. There is
performance degradation.
The usual performance of the application on MP machine is better than
that of uni processor machine. But the performance of MP degrades when
it comes to the multi-threaded part of the application. I am...
|
by: JohnFol |
last post by:
I know Windows / SQL etc can utilise multiple processors. In the good old
days of coding, you simply wrote the .EXE and Windows would run it on a
single processor (or a given processor for multi-processor machines)
I know also that threads exist, but am having a few problems understanding
how they are split across processors so here is an easy example.
I have a VB.Net written application that is processor intensive. It has not
been...
| |
by: Eugene |
last post by:
Hi All,
In multi processor computer, do we need to use specific design/coding method
to optimize our program to take advantage of the multi processor?
If I use a normal program, would it automatically achieve better (maybe
significantly) performance by adding more cpu(s). By using special techniques
(targeting multi processor sys), would the performance be increased further?
If yes, what is those techniques?
|
by: Bill Cunningham |
last post by:
I've been reading the C standard online and I'm puzzled as to what multibyte
chars are. Wide chars I believe would be characters for languages such as
cantonese or Japanese. I know the ASCII character set specifies that each
character such as 'b' or 'B' is an 8 bit character. So what's a multibyte
character?
Also how would you use the function parameter main (char argc, char
**argv) if that's correct?
Bill
|
by: fniles |
last post by:
If I use thread in my VB.NET application, will it work in a multi processor
machine ? I mean, will it crash a multi processor machine ?
Thanks.
|
by: Sabri.Pllana |
last post by:
We apologize if you receive multiple copies of this call for papers.
***********************************************************************
2008 International Workshop on Multi-Core Computing Systems
(MuCoCoS'08)
Barcelona, Spain, March 4 - 7, 2008; in conjunction with CISIS'08.
<http://www.par.univie.ac.at/~pllana/mucocos08>
***********************************************************************
Context
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |