Procedure Time out: processor

Showing posts with label processor. Show all posts

Wednesday, March 21, 2012

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
Thanks
Hi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:

> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint t
o
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:

> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Upgrade on a sql cluster

In our environment we have a cluster active standby with a shared storage
iscsi
The two machines are Windows 2000 Advanced Server with Intel Xeon
Single-Core 3.4Ghz with SQL Server 2000
Enterprise Edition.
Now we want to sustitute these two with another two machines with
Dual Intel Xeon Dual-Core 3.6Ghz
We want to clone the actual machines in the new environment cause we want a
current machine for disaster recovery and cause we want also to test the
disaster recovery plan...
1) the two new dual core processors are view from operating system as a four
processor is this configuration supported with the environment Windows 2000
Advanced Server and SQL 2000 Server Enterprise Edition ?
2)when we have transferred the disk and the configuration and the boot is ok
and sql services are started what tuning and best practice we have to apply
for the new environment for tuning performance with new dual vs single cpu
node
Hi Davide,
1) Yes, you can manage 4 processors with these licenses.
2) We need more information about your environment to answer this. Generally
speaking, you should take care of CPU affinity and IO affinity, and take a
look at your files and filegroups configurations, especially on tempdb
database
Regards
Antonio Soto
Solid Quality Leaning
http://www.solidqualitylearning.com
DISCLAIMER:
Anything written in this message represents solely the point of view of the
sender. This message does not imply endorsement from Solid Quality Learning,
and it does not represent the point of view of Solid Quality Learning or any
other person, company or institution mentioned in this message
"Dr Davide BOmbarda" <DrDavideBOmbarda@.discussions.microsoft.com> escribi
en el mensaje news:CC20F619-9ED5-4CBB-BA62-B23AF4B2C75B@.microsoft.com...
> In our environment we have a cluster active standby with a shared storage
> iscsi
> The two machines are Windows 2000 Advanced Server with Intel Xeon
> Single-Core 3.4Ghz with SQL Server 2000
> Enterprise Edition.
> Now we want to sustitute these two with another two machines with
> Dual Intel Xeon Dual-Core 3.6Ghz
> We want to clone the actual machines in the new environment cause we want
> a
> current machine for disaster recovery and cause we want also to test the
> disaster recovery plan...
> 1) the two new dual core processors are view from operating system as a
> four
> processor is this configuration supported with the environment Windows
> 2000
> Advanced Server and SQL 2000 Server Enterprise Edition ?
> 2)when we have transferred the disk and the configuration and the boot is
> ok
> and sql services are started what tuning and best practice we have to
> apply
> for the new environment for tuning performance with new dual vs single cpu
> node
>
>
|||I/O affinity only comes into play with the Datacenter Edition when working
with specific SAN configurations. Even then, you need to be pushing the I/O
channel pretty hard to even see any effect.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:umQqsqpKGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi Davide,
> 1) Yes, you can manage 4 processors with these licenses.
> 2) We need more information about your environment to answer this.
> Generally speaking, you should take care of CPU affinity and IO affinity,
> and take a look at your files and filegroups configurations, especially on
> tempdb database
> Regards
> Antonio Soto
> Solid Quality Leaning
> http://www.solidqualitylearning.com
> DISCLAIMER:
> Anything written in this message represents solely the point of view of
> the sender. This message does not imply endorsement from Solid Quality
> Learning, and it does not represent the point of view of Solid Quality
> Learning or any other person, company or institution mentioned in this
> message
>
>
> "Dr Davide BOmbarda" <DrDavideBOmbarda@.discussions.microsoft.com> escribi
> en el mensaje news:CC20F619-9ED5-4CBB-BA62-B23AF4B2C75B@.microsoft.com...
>

Processor Queue - Weird

Howdy,

This is a follow on from a previous post

http://www.dbforums.com/t984271.html

And now I have found something interesting :

(1) When I was monitoring the System\Processor Queue locally ( Via a term server login onto the box ) I would see a queue of 3-4. If I monitor the same parameter from a remote PC, I see a Processor Queue of 1 - why?
The box had 1 GB RAM ( SQL used 500 MB and had 250 MB free according to Task manager ).

(2)
I have another almost identical box that has same CPU but twice ammount of RAM ( 2 GB ) but has System\Processor Queue of almost
0 - why?

All other parameters for Disk, IO etc are fine.

Cheers,

SGHow about PROCESS counter during these values?|||sqlguy:

I think this guy (http://groups.google.com/groups?q=%22processor+queue+length%22&hl=en&lr=&ie=UTF-8&selm=YhwfOM4ZTgdHxYwWN9bu3Av1M6%2BQ%404ax.com&rnum=1) knows what he is talking about.sql

Processor question

Hello Everyone,
When a certain platform let it be standard or enterprise, the processor
limitation on Standard is currently 4. Does that mean 4 physical processors
or if you had 2 physical processors with hyper threading which in Windows
would give you 4? I a bit confused about that.
Thank you
Alex AndersonAlex Anderson wrote:
> Hello Everyone,
> When a certain platform let it be standard or enterprise, the
> processor limitation on Standard is currently 4. Does that mean 4
> physical processors or if you had 2 physical processors with hyper
> threading which in Windows would give you 4? I a bit confused about
> that.
> Thank you
> Alex Anderson
Physical. In fact, it's my understanding that dual-core processors are
going to be treated as a single processor according to the Microsoft
Licensing. SO you need to license the physical CPUs in the box (for CPU
licensing). As always, check with Microsoft Licensing for any licensing
issues and questions.
--
David Gugick - SQL Server MVP
Quest Software|||David is correct. Multi-core and hyperthreaded processors count as a single
processor for licensing and edition processor limits. This makes MS
licensing highly competitive and much simpler than "the other guys".
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ebZRcptIGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Alex Anderson wrote:
>> Hello Everyone,
>> When a certain platform let it be standard or enterprise, the
>> processor limitation on Standard is currently 4. Does that mean 4
>> physical processors or if you had 2 physical processors with hyper
>> threading which in Windows would give you 4? I a bit confused about
>> that.
>> Thank you
>> Alex Anderson
> Physical. In fact, it's my understanding that dual-core processors are
> going to be treated as a single processor according to the Microsoft
> Licensing. SO you need to license the physical CPUs in the box (for CPU
> licensing). As always, check with Microsoft Licensing for any licensing
> issues and questions.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Thank you both for the information. You know Microsoft, you never know when
the wool will be pulled over our eyes.
Thank you
Alex Anderson
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eJ1sIGuIGHA.1288@.TK2MSFTNGP09.phx.gbl...
> David is correct. Multi-core and hyperthreaded processors count as a
> single processor for licensing and edition processor limits. This makes
> MS licensing highly competitive and much simpler than "the other guys".
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:ebZRcptIGHA.1760@.TK2MSFTNGP10.phx.gbl...
>> Alex Anderson wrote:
>> Hello Everyone,
>> When a certain platform let it be standard or enterprise, the
>> processor limitation on Standard is currently 4. Does that mean 4
>> physical processors or if you had 2 physical processors with hyper
>> threading which in Windows would give you 4? I a bit confused about
>> that.
>> Thank you
>> Alex Anderson
>> Physical. In fact, it's my understanding that dual-core processors are
>> going to be treated as a single processor according to the Microsoft
>> Licensing. SO you need to license the physical CPUs in the box (for CPU
>> licensing). As always, check with Microsoft Licensing for any licensing
>> issues and questions.
>> --
>> David Gugick - SQL Server MVP
>> Quest Software
>|||Microsoft is often confusing, but most of the time they avoid active
stupidity. Remember, MS is a very large organization and sometimes it takes
a while for feedback to trickle through. This time, they appear to have
gotten it right.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex Anderson" <AAnderson@.Murrieta.org> wrote in message
news:O0Cuq31IGHA.2708@.tk2msftngp13.phx.gbl...
> Thank you both for the information. You know Microsoft, you never know
> when the wool will be pulled over our eyes.
> Thank you
> Alex Anderson
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eJ1sIGuIGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> David is correct. Multi-core and hyperthreaded processors count as a
>> single processor for licensing and edition processor limits. This makes
>> MS licensing highly competitive and much simpler than "the other guys".
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:ebZRcptIGHA.1760@.TK2MSFTNGP10.phx.gbl...
>> Alex Anderson wrote:
>> Hello Everyone,
>> When a certain platform let it be standard or enterprise, the
>> processor limitation on Standard is currently 4. Does that mean 4
>> physical processors or if you had 2 physical processors with hyper
>> threading which in Windows would give you 4? I a bit confused about
>> that.
>> Thank you
>> Alex Anderson
>> Physical. In fact, it's my understanding that dual-core processors are
>> going to be treated as a single processor according to the Microsoft
>> Licensing. SO you need to license the physical CPUs in the box (for CPU
>> licensing). As always, check with Microsoft Licensing for any licensing
>> issues and questions.
>> --
>> David Gugick - SQL Server MVP
>> Quest Software
>>
>

Processor question

Processor performance issues

I was hoping someone out there can give me some direction on some performance issues I've been having with SQL Server 2005. I have two SQL Servers instances configured almost identically (or so it seems) on two different machines.

I have a certain data process initiated from a database aplication that is giving me two different results depending on which sever processes it. One of my servers is taking considerable more processor time and effort than the other running at 100% utilization for a long period of time. I have double checked the settings on both SQL Server instances and there are very few differences. The Maximum worker threads on the poor one is set to 1024 while the better server is set to automatic (0). That is the only differnce I have found in the settings of these two servers and it's my understanding that I should not see a huge performance difference due to this setting.

I'm a bit at my wits end here and really need some more ideas at what other factors that I am not taking into account could effect Processor utilization.

Hi Joe,

you will need to run the explain on each machine and review the plan created by the optimiser for any piece of sql you think is running too slowly. If you have the same piece of sql that performs differently on the two machines even better.

It is not unusual for small differences in number of rows or an index that should be there that is not to significantly influence performance.

Alas, I wish there were more documentation around on how the optimiser chooses access paths but there seems to be not a lot of it around.......

(PS. In 2005 you generate plans from sql server studio in case you are not aware...)

Best Regards

Peter

|||

Try running these queries on both systems, to give a better idea of what is going on:

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2)),

'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats;

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

Processor model

I have a server with 4 dual core processors.
If I install SQL Server 2005 Express edition on it (which is free), I do not
have to disable other 3 processors because by default express edition will
use at most 1 processor?
But if I use SQL Server 2005 Workgroup (or Standard) edition with one
processor license, I would have to disable other 3 processors at the bios
level to be legal? If I don't disable it, will it use all 4 processors even
though I only bought license for one processor?
Help would be appreciated.JJ,
I could be wrong, but I believe you cannot get around licensing by
restricting the number of processers. I believe if you buy a license for one
processor, then you cannot use it on any server with more than one physical
processor.
-- Bill
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal?
Yes.
> If I don't disable it, will it use all 4 processors even though I only
> bought license for one processor?
SQL Server will use the lesser of either the number of supported or
installed processors. Workgroup supports up to 2 CPUs and Standard up to 4.
However you will be violating the license agreement unless you have
processor licenses for each processor socket available to the OS. See
http://www.microsoft.com/sql/howtobuy/faq.mspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||But if I installed express 2005, then no need to disable processors, right?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the bios
>> level to be legal?
> Yes.
>> If I don't disable it, will it use all 4 processors even though I only
>> bought license for one processor?
> SQL Server will use the lesser of either the number of supported or
> installed processors. Workgroup supports up to 2 CPUs and Standard up to
> 4. However you will be violating the license agreement unless you have
> processor licenses for each processor socket available to the OS. See
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JJ" <nospam@.nospam.com> wrote in message
> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>>I have a server with 4 dual core processors.
>> If I install SQL Server 2005 Express edition on it (which is free), I do
>> not have to disable other 3 processors because by default express edition
>> will use at most 1 processor?
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the bios
>> level to be legal? If I don't disable it, will it use all 4 processors
>> even though I only bought license for one processor?
>> Help would be appreciated.
>|||> But if I installed express 2005, then no need to disable processors,
> right?
Correct. SQL Server Express is free so the processing licensing model does
not apply,
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:OZo$JxqTHHA.5060@.TK2MSFTNGP06.phx.gbl...
> But if I installed express 2005, then no need to disable processors,
> right?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the
>> bios level to be legal?
>> Yes.
>> If I don't disable it, will it use all 4 processors even though I only
>> bought license for one processor?
>> SQL Server will use the lesser of either the number of supported or
>> installed processors. Workgroup supports up to 2 CPUs and Standard up to
>> 4. However you will be violating the license agreement unless you have
>> processor licenses for each processor socket available to the OS. See
>> http://www.microsoft.com/sql/howtobuy/faq.mspx
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "JJ" <nospam@.nospam.com> wrote in message
>> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>>I have a server with 4 dual core processors.
>> If I install SQL Server 2005 Express edition on it (which is free), I do
>> not have to disable other 3 processors because by default express
>> edition will use at most 1 processor?
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the
>> bios level to be legal? If I don't disable it, will it use all 4
>> processors even though I only bought license for one processor?
>> Help would be appreciated.
>>
>

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...

I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a écrit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time... :)
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...

I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql

Processor Licensing question

If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I nee
d
> another license? Thanks.
> --
> George Hester
> _________________________________"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtob...einstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David|||I have a dual processor system. One processor is used for boot operations
and the other is used for Applications. If I were to install SQL Single
Processior License on this machine would that be within the Licensing
restrictions as there is the one processor for Applications or would I need
another license? Thanks.
George Hester
_________________________________|||If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I nee
d
> another license? Thanks.
> --
> George Hester
> _________________________________|||"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtob...einstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David

Processor Licensing question

I have a dual processor system. One processor is used for boot operations
and the other is used for Applications. If I were to install SQL Single
Processior License on this machine would that be within the Licensing
restrictions as there is the one processor for Applications or would I need
another license? Thanks.
--
George Hester
_________________________________If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I need
> another license? Thanks.
> --
> George Hester
> _________________________________|||"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtobuy/multipleinstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David

Processor licensing

In the processor licensing mode a license is required for each physical
or virtual processor like explained in
http://www.microsoft.com/sql/howtobuy/default.mspx
->But what about dual core processor?
->And what about clustering? (if a user install 2 server in cluster mode)

tnxOn Thu, 12 Oct 2006 14:14:40 GMT, Enorme Vigenti wrote:

Quote:

Originally Posted by

>In the processor licensing mode a license is required for each physical
>or virtual processor like explained in
>http://www.microsoft.com/sql/howtobuy/default.mspx
>
>->But what about dual core processor?

Hi Enorme,

You pay per soocket. So a dual-core can run on a single-core license.
And if you have a quad-code CPU with hyperthreading (do such beasts even
exist?), you'd have eight logical processors and still have to pay for
only a single processor license.

Quote:

Originally Posted by

>->And what about clustering? (if a user install 2 server in cluster mode)

If only one server is active and the other is a passice server, used
only to take over from the active server in case of an emergency, you
only need a license for the active server. The passive server can't have
more processors than the active server, though.

If you have two active servers (e.g. using one to offload some of the
reporting tasks), you'll have to license both servers.

Licensing FAQ: http://www.microsoft.com/sql/howtobuy/faq.mspx
--
Hugo Kornelis, SQL Server MVP|||Licensing is per processor, not per core. So if you have one dual core
processor it still counts as one.
In clustering, I believe that you dont have to pay for the 'standby' server
if you do not use it for anything else (if its just standby you only need
one license for two servers in cluster).

"Enorme Vigenti" <Lsimon5@.libero.itwrote in message
news:k5sXg.536$uv5.900@.twister1.libero.it...

Quote:

Originally Posted by

In the processor licensing mode a license is required for each physical
or virtual processor like explained in
http://www.microsoft.com/sql/howtobuy/default.mspx
>
->But what about dual core processor?
->And what about clustering? (if a user install 2 server in cluster mode)
>
tnx

|||On Thu, 12 Oct 2006 21:00:33 +0200, MC wrote:

(snip)

Hi MC,

May I suggest that you fix your system clock? The NNTP headers show that
your message was sent on Friday, 15:41 UTC/GMT, but the date on your
message shows as Thursday 19:00 UTC/GMT. That's almost 24 hours
difference.

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Date: Thu, 12 Oct 2006 21:00:33 +0200

(...)

Quote:

Originally Posted by

Quote:

Originally Posted by

>>NNTP-Posting-Date: Fri, 13 Oct 2006 15:41:11 +0000 (UTC)

--
Hugo Kornelis, SQL Server MVP|||I'll pass it on to the admin on the machine I used. Thanks Hugo

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:5900j21t2g3f5gqrgspj346qsmio9ikth5@.4ax.com...

Quote:

Originally Posted by

On Thu, 12 Oct 2006 21:00:33 +0200, MC wrote:
>
(snip)
>
Hi MC,
>
May I suggest that you fix your system clock? The NNTP headers show that
your message was sent on Friday, 15:41 UTC/GMT, but the date on your
message shows as Thursday 19:00 UTC/GMT. That's almost 24 hours
difference.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>Date: Thu, 12 Oct 2006 21:00:33 +0200

(...)

Quote:

Originally Posted by

Quote:

Originally Posted by

>NNTP-Posting-Date: Fri, 13 Oct 2006 15:41:11 +0000 (UTC)

>
>
--
Hugo Kornelis, SQL Server MVP

Processor License

I read the FAQ at
http://www.microsoft.com/sql/howtobuy/partitioning.asp, but it is still
not clear to me how to buy processor licenses basing on the number of
processors
The FAQ states
"If any processor in the server is made inaccessible to all of the
operating system copies set up to run SQL Server, then that processor
does not require a Processor license for SQL Server. In other words, a
SQL Server Processor license is required for each processor that is
accessible to any operating system copy on which SQL Server is set up
to run. "
Does this mean that the only way to avoid buying multiple processor
licenses is disabling the processor from the server's BIOS?
I guess that the "processor control" tab in the SQL server properties
has nothing to do with this, even if I'd find it very wise if MS
provided us with such a tool...
I also guess that they mean physical processor AND NOT virtual
processor deriving from Hyperthreading...
Thanks
Dave
> Does this mean that the only way to avoid buying multiple processor
> licenses is disabling the processor from the server's BIOS?
That is how I understand it.

> I also guess that they mean physical processor AND NOT virtual
> processor deriving from Hyperthreading...
Yep
Keith
<dbwmn2001@.yahoo.com> wrote in message
news:1106241266.515795.105740@.c13g2000cwb.googlegr oups.com...
> I read the FAQ at
> http://www.microsoft.com/sql/howtobuy/partitioning.asp, but it is still
> not clear to me how to buy processor licenses basing on the number of
> processors
> The FAQ states
> "If any processor in the server is made inaccessible to all of the
> operating system copies set up to run SQL Server, then that processor
> does not require a Processor license for SQL Server. In other words, a
> SQL Server Processor license is required for each processor that is
> accessible to any operating system copy on which SQL Server is set up
> to run. "
> Does this mean that the only way to avoid buying multiple processor
> licenses is disabling the processor from the server's BIOS?
> I guess that the "processor control" tab in the SQL server properties
> has nothing to do with this, even if I'd find it very wise if MS
> provided us with such a tool...
> I also guess that they mean physical processor AND NOT virtual
> processor deriving from Hyperthreading...
> Thanks
> Dave
>
|||Hi,
Of course per processor licence is for physical processor and not for
Hyperthreading.
As regards disabling is as far as I know exactly as you wrote. You have to
disable processor in BIOS otherwise you should buy a licence.
Danijel
<dbwmn2001@.yahoo.com> wrote in message
news:1106241266.515795.105740@.c13g2000cwb.googlegr oups.com...
>I read the FAQ at
> http://www.microsoft.com/sql/howtobuy/partitioning.asp, but it is still
> not clear to me how to buy processor licenses basing on the number of
> processors
> The FAQ states
> "If any processor in the server is made inaccessible to all of the
> operating system copies set up to run SQL Server, then that processor
> does not require a Processor license for SQL Server. In other words, a
> SQL Server Processor license is required for each processor that is
> accessible to any operating system copy on which SQL Server is set up
> to run. "
> Does this mean that the only way to avoid buying multiple processor
> licenses is disabling the processor from the server's BIOS?
> I guess that the "processor control" tab in the SQL server properties
> has nothing to do with this, even if I'd find it very wise if MS
> provided us with such a tool...
> I also guess that they mean physical processor AND NOT virtual
> processor deriving from Hyperthreading...
> Thanks
> Dave
>
|||Disabling in BIOS or physically removing a processor is the only way to keep
from counting a processor towards licensing requirements. If the OS sees
it, you must license it.
"Processors" means physical processors, not logical processors. Turning
HyperThreading on or off has no effect on licensing requirements.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<dbwmn2001@.yahoo.com> wrote in message
news:1106241266.515795.105740@.c13g2000cwb.googlegr oups.com...
> I read the FAQ at
> http://www.microsoft.com/sql/howtobuy/partitioning.asp, but it is still
> not clear to me how to buy processor licenses basing on the number of
> processors
> The FAQ states
> "If any processor in the server is made inaccessible to all of the
> operating system copies set up to run SQL Server, then that processor
> does not require a Processor license for SQL Server. In other words, a
> SQL Server Processor license is required for each processor that is
> accessible to any operating system copy on which SQL Server is set up
> to run. "
> Does this mean that the only way to avoid buying multiple processor
> licenses is disabling the processor from the server's BIOS?
> I guess that the "processor control" tab in the SQL server properties
> has nothing to do with this, even if I'd find it very wise if MS
> provided us with such a tool...
> I also guess that they mean physical processor AND NOT virtual
> processor deriving from Hyperthreading...
> Thanks
> Dave
>

Processor license

Hello everyone,
What is the procedure of changing a SQL 2000 server to processor mode
from per seat?
Thank you
Alex Anderson
The following is unsupported but you may want to check the
link:
Q:How to change the license mode after SQL2000 setup?
http://www.tkdinesh.com/faq/ans/license.html
-Sue
On Mon, 23 Aug 2004 15:18:14 -0700, "Alex Anderson"
<AAnderson@.Murrieta.org> wrote:

>Hello everyone,
> What is the procedure of changing a SQL 2000 server to processor mode
>from per seat?
>Thank you
>Alex Anderson
>
|||Sue,
Interesting link, however Mode is already set to 0 and I'm running
Windows 2003 Server. Although, contained within the same registry key is a
FlipAllow and I'm not sure what this is used for. Perhaps to allow the
switch between Per device and Processor? I have the licenses for two
processes (just purchased them from Microsoft) and now from what I'm told I
might just have to pay for support to re-license SQL. Go figure...
Thank you
Alex Anderson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:fe6li09llida0kiqg7tksh26hta255i5de@.4ax.com...
> The following is unsupported but you may want to check the
> link:
> Q:How to change the license mode after SQL2000 setup?
> http://www.tkdinesh.com/faq/ans/license.html
> -Sue
> On Mon, 23 Aug 2004 15:18:14 -0700, "Alex Anderson"
> <AAnderson@.Murrieta.org> wrote:
>
sql

Wednesday, March 21, 2012

Processor Utilization

Processor Utilization

Processor Utilization

Processor Upgrade on a sql cluster

Processor Queue - Weird

Processor question

Processor question

Processor question

Processor performance issues

Processor model

Processor model

Processor model

processor maxed out block other process...

processor maxed out block other process...

processor maxed out block other process...

Processor Licensing question

Processor Licensing question

Processor licensing

Processor License

Processor license

Procedure Time out

Blog Archive

About Me