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
Showing posts with label system. Show all posts
Showing posts with label system. 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
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
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
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 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
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 activity is high.
I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David
|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David
|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
Processor activity is high.
I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
Tuesday, March 20, 2012
Processor activity is high.
I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie
Monday, March 12, 2012
Processing cube automatically after ETL ends
Hi all,
We have an ETL process running at night and ending at the morning.
We want the system to automatically process a cube after ETL ends.
The ETL program is Infromatica and the server is Linux (Someone told me to use ascmd but it runs on windows server only so it won't work).
I really need a solution for this i order to automate our process.
Thanks in advance,
Ariel.
Could you create a wrapper web service for ascmd, and call it from Informatica?|||If you are using the Enterprise edition you could also look at using proactive caching to detect when there have been changes to the underlying source data. It also has a silence interval parameter that allows SSAS to wait for a certain amount of silence (ie the ETL has finished) before commencing processing.
If you have HTTP connectivity enabled you could issue a direct xmla command to SSAS. There are some examples of using XMLA from javascript at http://www.activeinterface.com/thinolap.html
Otherwise John's idea of creating a web service wrapper is a good one.
Friday, March 9, 2012
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI
|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>
|||Processes appearing to lock themselves is a change in reporting in SP4, not an actual issue. Basically the process reports itself as a blocking process when it goes to grab disk allocation (as I understand it). The Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on databases up to 400 GB in size.
Originally posted by Dave Wall
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/...513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall
|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI
|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>
|||Processes appearing to lock themselves is a change in reporting in SP4, not an actual issue. Basically the process reports itself as a blocking process when it goes to grab disk allocation (as I understand it). The Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on databases up to 400 GB in size.
Quote:
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/...513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall
|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1756427.html
>
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
--
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
--
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
> > *Hi All,
> >
> > We've recently rebuilt our failover cluster to Windows 2003 and SQL
> > Server
> > 200 SP4. Users are complaining that the system is running slowly,
> > looking at
> > the processes, the active processes appear to be blocking themselves.
> > The
> > configuration of SQL is identical to before and the hardware is the
> > same,
> > just the OS and the SP version of SQL has changed.
> >
> > Any ideas what might be causing the blocking? I've checked for lock
> > escalation (using profiler) and we're not experiencing it!
> >
> > --
> > Dave Wall *
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
--
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
--
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
> > *Hi All,
> >
> > We've recently rebuilt our failover cluster to Windows 2003 and SQL
> > Server
> > 200 SP4. Users are complaining that the system is running slowly,
> > looking at
> > the processes, the active processes appear to be blocking themselves.
> > The
> > configuration of SQL is identical to before and the hardware is the
> > same,
> > just the OS and the SP version of SQL has changed.
> >
> > Any ideas what might be causing the blocking? I've checked for lock
> > escalation (using profiler) and we're not experiencing it!
> >
> > --
> > Dave Wall *
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group...
513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group...
513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
Process with status SLEEPING.
Hi all,
We have a web application with multiple users connecting to SQL Server 2005 database.
Once the users start using the system, I could see in the Process Info from the activity monitor with a several process with status as sleeping and command as Awaiting Command.
I have few questions regarding this for which simple answers would help me ( I searched and couldnt understand from the pages that explained the process information )
My questions are,
1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?
2. Does these process affect the performance ?
3. Does killing these process would affect the application connectivity ?
4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?
5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?
Any help would be appreciated.
Thanks in advance,
DBLearner.
Hi DBLearner,
1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?
-- No
2. Does these process affect the performance ?
-- It will if you have left thousand connections open. Connections need resources.
3. Does killing these process would affect the application connectivity ?
-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.
4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?
-- Web app doesnot close the connection after the work has been done.
5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?
-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:
1. Opens a connection.
2. Do the SQL work e.g. execute a stored procedure or select some data back.
3. Close the connection.
regards
Jag
|||Most web applications use a "connection pool" for SQL connections. This is a normal operation. Leave it alone.Openning and closing connections is very time consuming. You do NOT want to open and close several thousand connections per min.
|||
Thanks Jag & Tom. But what should I do to optimise my connections and applications ?
DBLearner
|||It depends on your web application. You will need to look and see what mechanisim it is using to access the SQL server.|||
Agree with Tom, if it is using Connection pooling then the connections should be left alone.
But if there is no connection pooling present, then the web application should be closing there connection after the work has been completed.
regards
Jag
|||Hi,
Actually I am having a same problem where there are quite a number of process with sleeping status in the database. And I noticed that all these processes are called by the crystal report (using store procedures to get the data from db). FYI, I am using VS.Net 2003 and crystal report 9.0. Does anyone have idea on the solution for the this problem? Thanks.
|||As Jag and Tom say - if this is connection pooling then you are best to leave it alone.
What you are seeing is one sleeping process for each active pool connection. They are waiting, ready to respond quickly when needed (without logging in again). The only time you have a problem is if the number of these sleeping connections does not hold steady but instead rises dramatically (implying that either it is not pooling somewhere or the pooling is failing).
The pool of connections means that the application can be responsive, and if you start killing them then there will be a reduction in that responsiveness (and servicing requests is the database's function). Even worse if you manage to kill a connection just as it is activated to service a request you could cause all kinds of problems (hopefully just longer delays).
|||This is not a "problem", this is how it WORKS. As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.Generally it opens new pipes when other pipes are busy. So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before. It should timeout the pipe and close them after 60 mins or some time of inactivity.
Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.
|||
I have the same problem but i don't have hundread of conn just about 40.
These 40 sleeping connections thought eat me about 90% from the CPU and reset users Session and they loose their products before ariving to the payment.
I did close all the recordsets and connections and still appear new sleeping connections.
Any suggestions?
i used for any recordset opened
rs.close
set rs = nothing
still make me these problems i don't know what to do anymore
Any help will be appreciated
thank you
Process with status SLEEPING.
Hi all,
We have a web application with multiple users connecting to SQL Server 2005 database.
Once the users start using the system, I could see in the Process Info from the activity monitor with a several process with status as sleeping and command as Awaiting Command.
I have few questions regarding this for which simple answers would help me ( I searched and couldnt understand from the pages that explained the process information )
My questions are,
1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?
2. Does these process affect the performance ?
3. Does killing these process would affect the application connectivity ?
4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?
5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?
Any help would be appreciated.
Thanks in advance,
DBLearner.
Hi DBLearner,
1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?
-- No
2. Does these process affect the performance ?
-- It will if you have left thousand connections open. Connections need resources.
3. Does killing these process would affect the application connectivity ?
-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.
4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?
-- Web app doesnot close the connection after the work has been done.
5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?
-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:
1. Opens a connection.
2. Do the SQL work e.g. execute a stored procedure or select some data back.
3. Close the connection.
regards
Jag
|||Most web applications use a "connection pool" for SQL connections. This is a normal operation. Leave it alone.Openning and closing connections is very time consuming. You do NOT want to open and close several thousand connections per min.
|||
Thanks Jag & Tom. But what should I do to optimise my connections and applications ?
DBLearner
|||It depends on your web application. You will need to look and see what mechanisim it is using to access the SQL server.|||
Agree with Tom, if it is using Connection pooling then the connections should be left alone.
But if there is no connection pooling present, then the web application should be closing there connection after the work has been completed.
regards
Jag
|||Hi,
Actually I am having a same problem where there are quite a number of process with sleeping status in the database. And I noticed that all these processes are called by the crystal report (using store procedures to get the data from db). FYI, I am using VS.Net 2003 and crystal report 9.0. Does anyone have idea on the solution for the this problem? Thanks.
|||As Jag and Tom say - if this is connection pooling then you are best to leave it alone.
What you are seeing is one sleeping process for each active pool connection. They are waiting, ready to respond quickly when needed (without logging in again). The only time you have a problem is if the number of these sleeping connections does not hold steady but instead rises dramatically (implying that either it is not pooling somewhere or the pooling is failing).
The pool of connections means that the application can be responsive, and if you start killing them then there will be a reduction in that responsiveness (and servicing requests is the database's function). Even worse if you manage to kill a connection just as it is activated to service a request you could cause all kinds of problems (hopefully just longer delays).
|||This is not a "problem", this is how it WORKS. As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.Generally it opens new pipes when other pipes are busy. So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before. It should timeout the pipe and close them after 60 mins or some time of inactivity.
Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.
|||
I have the same problem but i don't have hundread of conn just about 40.
These 40 sleeping connections thought eat me about 90% from the CPU and reset users Session and they loose their products before ariving to the payment.
I did close all the recordsets and connections and still appear new sleeping connections.
Any suggestions?
i used for any recordset opened
rs.close
set rs = nothing
still make me these problems i don't know what to do anymore
Any help will be appreciated
thank you
Wednesday, March 7, 2012
Process Info
Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter
Users have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter
Users have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Process Info
Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Process Info
Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
PeterUsers have different ideas of what "being in an application"
means so it depends on what they meant. If they meant they
weren't actively using it, then the processes would still be
there if they are still connected. If they application was
actually closed then the processes will clean up eventually.
The processes remaining after closing an app could be due to
poor coding practices in the application, such as ado
references are not being cleaned up. If the issue is the 10
or more processes, that is also controlled by the
application. If it's an ADO app, then it probably wasn't
written to use the active connection - instead it keeps
creating new connections for whatever it needs to do.
It's not really going to hurt anything but it looks like
it's likely related to how the application was written.
-Sue
On Wed, 7 Apr 2004 08:17:28 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Dear All,
>Under Managment/Current Activity/Process Info you can see
>all the users with connections to the system.
>Here is the problem. A couple of users seem to have
>multiple (10 or more) process ID's, all of them sleeping.
>After a chat with them they said they were not in the
>application, but the process was still being displayed.
>My questions are then, should I be concerned, and why
>aren't they closing automatically ?
>Thanks
>Peter
Subscribe to:
Posts (Atom)