I can find out the limit of CPUs and ram on workgroup edition are 2pc and
3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
machine? The 3GB ram limitation refers to the maximum size of memory
allocated to SQL Server?
Thanks!The CPU and memory restrictions refer to the maximum SQL Server will use.
The additional processors/memory will be available to other applications but
ignored by SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jackie" <-> wrote in message news:OHt2YphsGHA.1196@.TK2MSFTNGP04.phx.gbl...
>I can find out the limit of CPUs and ram on workgroup edition are 2pc and
> 3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
> machine? The 3GB ram limitation refers to the maximum size of memory
> allocated to SQL Server?
> Thanks!
>
>
Showing posts with label cpus. Show all posts
Showing posts with label cpus. Show all posts
Sunday, March 11, 2012
CPUs and RAM limit on workgroup edition
I can find out the limit of CPUs and ram on workgroup edition are 2pc and
3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
machine? The 3GB ram limitation refers to the maximum size of memory
allocated to SQL Server?
Thanks!The CPU and memory restrictions refer to the maximum SQL Server will use.
The additional processors/memory will be available to other applications but
ignored by SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jackie" <-> wrote in message news:OHt2YphsGHA.1196@.TK2MSFTNGP04.phx.gbl...
>I can find out the limit of CPUs and ram on workgroup edition are 2pc and
> 3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
> machine? The 3GB ram limitation refers to the maximum size of memory
> allocated to SQL Server?
> Thanks!
>
>
3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
machine? The 3GB ram limitation refers to the maximum size of memory
allocated to SQL Server?
Thanks!The CPU and memory restrictions refer to the maximum SQL Server will use.
The additional processors/memory will be available to other applications but
ignored by SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jackie" <-> wrote in message news:OHt2YphsGHA.1196@.TK2MSFTNGP04.phx.gbl...
>I can find out the limit of CPUs and ram on workgroup edition are 2pc and
> 3GB respectively. What does this mean if I have 4 CPUs and 4GB ram in one
> machine? The 3GB ram limitation refers to the maximum size of memory
> allocated to SQL Server?
> Thanks!
>
>
Wednesday, March 7, 2012
CPU maximums & multicore CPUs
I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?
The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>
|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>
|||Try this:
http://download.microsoft.com/downlo...5Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||http://www.microsoft.com/sql/prodinf...-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downlo...5Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?
The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>
|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>
|||Try this:
http://download.microsoft.com/downlo...5Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||http://www.microsoft.com/sql/prodinf...-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downlo...5Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>
CPU maximums & multicore CPUs
I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled ou
t
anywhere on Microsoft's site. I realize it can be inferred by what is state
d
in most of the papers they have on licensing models but I have management wh
o
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL wi
ll
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>|||Try this:
http://download.microsoft.com/downl...05Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||http://www.microsoft.com/sql/prodin...e-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downl...05Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled ou
t
anywhere on Microsoft's site. I realize it can be inferred by what is state
d
in most of the papers they have on licensing models but I have management wh
o
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL wi
ll
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>|||Try this:
http://download.microsoft.com/downl...05Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||http://www.microsoft.com/sql/prodin...e-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downl...05Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>
CPU maximums & multicore CPUs
I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
> >I have been reading the licensing information for SQL 2005 as it relates to
> > logical CPUs (hyperthreading or multicores) but all of the white papers I
> > am
> > reading cover the Per Processor model when addressing this.
> >
> > Does anyone know, when not using the Per Processor model (i.e. Per Server
> > with either Device or User CALs) does the same logic apply? In other
> > words,
> > if I have a server with 4 physical CPUs that do hyperthreading, can I run
> > Standard Edition and use all eight or do I need to go to Enterprise
> > Edition
> > to use more than 4 CPUs?
> >
> >
>
>|||Try this:
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||You win. Your link is better than my link. :)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>> Thanks for the response. Just one thing. Do you know if this is spelled
>> out
>> anywhere on Microsoft's site. I realize it can be inferred by what is
>> stated
>> in most of the papers they have on licensing models but I have management
>> who
>> would feel better if it was spelled out.
>> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore
>> extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it
>> >relates to
>> > logical CPUs (hyperthreading or multicores) but all of the white
>> > papers I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>
>
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
> >I have been reading the licensing information for SQL 2005 as it relates to
> > logical CPUs (hyperthreading or multicores) but all of the white papers I
> > am
> > reading cover the Per Processor model when addressing this.
> >
> > Does anyone know, when not using the Per Processor model (i.e. Per Server
> > with either Device or User CALs) does the same logic apply? In other
> > words,
> > if I have a server with 4 physical CPUs that do hyperthreading, can I run
> > Standard Edition and use all eight or do I need to go to Enterprise
> > Edition
> > to use more than 4 CPUs?
> >
> >
>
>|||Try this:
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||You win. Your link is better than my link. :)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>> Thanks for the response. Just one thing. Do you know if this is spelled
>> out
>> anywhere on Microsoft's site. I realize it can be inferred by what is
>> stated
>> in most of the papers they have on licensing models but I have management
>> who
>> would feel better if it was spelled out.
>> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore
>> extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it
>> >relates to
>> > logical CPUs (hyperthreading or multicores) but all of the white
>> > papers I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>
>
Saturday, February 25, 2012
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.
Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.
Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this S
P
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this S
P
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
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
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
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
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any w
ay
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any w
ay
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Subscribe to:
Posts (Atom)