After reading the information on the Poor man's monitor
http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
I could write an application that would immediately tell me when a timeout
was happening. Being that the server is a production server, I don't dare
test on it and the testing I did indicated that only the Server 2003 was able
to pull the data I was looking for (as the article indicated).
The problem is that when I set up the profmon.msc on the 2003 server to look
at the SQL timeouts on the 2000 server, it brings back nulls to the three
monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
Is there a way to coax the actual SQL Lock Timeout data back from the 2000
server to the 2003 server through PerfMon.msc?
Should I post this to a different user group?
--
Regards,
JamieAnswered my own question. The counterid will increment each time the table
is created. I believe my smallest counterid in the counterdetails was 191.
When I reset the query to point to the right counterid, it immediately
produced results.
--
Regards,
Jamie
"thejamie" wrote:
> After reading the information on the Poor man's monitor
> http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
> I could write an application that would immediately tell me when a timeout
> was happening. Being that the server is a production server, I don't dare
> test on it and the testing I did indicated that only the Server 2003 was able
> to pull the data I was looking for (as the article indicated).
> The problem is that when I set up the profmon.msc on the 2003 server to look
> at the SQL timeouts on the 2000 server, it brings back nulls to the three
> monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
> Is there a way to coax the actual SQL Lock Timeout data back from the 2000
> server to the 2003 server through PerfMon.msc?
> Should I post this to a different user group?
> --
> Regards,
> Jamie
Showing posts with label monitor. Show all posts
Showing posts with label monitor. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Profiler
We usually monitor our production server via profiler from
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||you can script the sp_trace calls directly per greg's
description or just run profiler on the server,
under almost no circumstances should profiler be saving
directly to a table on the production server
set it to save to a file, and place the file on lightly
loaded disk
also, an accumalation rate by profiler of <1000 line
items /sec should not be a significant load on the server,
but to be safe, try to keep it under 200/sec
>--Original Message--
>We usually monitor our production server via profiler
from
>our desktop. We had an issue over the weekend and would
>usually have the profiler results to review and
>troubleshoot. However, over the weekend our desktop's
were
>rebooted and all info lost.
>So we were thinking of running the profiler directly on
>the production server but have read/heard of issue's
>related to this and a potential performance hit on the
>server. When running it on my own desktop I sometimes run
>into memory issues.
>What are the pro's and con's of running profiler directly
>on the server?
>What about running the stored proc's in query analyzer
>from our desktop compared to running the profiler
directly?
>Would it be better to use the stored procedures instead
of
>profiler?
>Any input would be greatly appreciated.
>Thanks
>.
>
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||you can script the sp_trace calls directly per greg's
description or just run profiler on the server,
under almost no circumstances should profiler be saving
directly to a table on the production server
set it to save to a file, and place the file on lightly
loaded disk
also, an accumalation rate by profiler of <1000 line
items /sec should not be a significant load on the server,
but to be safe, try to keep it under 200/sec
>--Original Message--
>We usually monitor our production server via profiler
from
>our desktop. We had an issue over the weekend and would
>usually have the profiler results to review and
>troubleshoot. However, over the weekend our desktop's
were
>rebooted and all info lost.
>So we were thinking of running the profiler directly on
>the production server but have read/heard of issue's
>related to this and a potential performance hit on the
>server. When running it on my own desktop I sometimes run
>into memory issues.
>What are the pro's and con's of running profiler directly
>on the server?
>What about running the stored proc's in query analyzer
>from our desktop compared to running the profiler
directly?
>Would it be better to use the stored procedures instead
of
>profiler?
>Any input would be greatly appreciated.
>Thanks
>.
>
Profiler
We usually monitor our production server via profiler from
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>
Wednesday, March 21, 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
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
ProcessId remains in memory?
Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.
The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.
|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com... [vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.
The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.
|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com... [vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
ProcessId remains in memory?
Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com...
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on
>>Windows
>>Server 2003 and notice that applications that have been running during the
>>day, have more and more ProcessID's for the same application. The
>>applications sometimes is closed by the user, sometimes crash, but usually
>>they will (after a refresh of EM) not show. I see the Host name in the
>>list
>>of items in Process Info, and now see that each machine has multiple
>>entries
>>for the same application. It seems that the process Info remains fixed in
>>server memory.
>>Anyone know why this should have just started to happed. As far as I can
>>tell, only the clients would have any updates (MS Updates and fixes) the
>>client application has not changed apart from cosmetically. The connection
>>is through ADODB MDAC 2.8
>>Thanks for the time. Hope this is clear.
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entries
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.4ax.com...
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on
>>Windows
>>Server 2003 and notice that applications that have been running during the
>>day, have more and more ProcessID's for the same application. The
>>applications sometimes is closed by the user, sometimes crash, but usually
>>they will (after a refresh of EM) not show. I see the Host name in the
>>list
>>of items in Process Info, and now see that each machine has multiple
>>entries
>>for the same application. It seems that the process Info remains fixed in
>>server memory.
>>Anyone know why this should have just started to happed. As far as I can
>>tell, only the clients would have any updates (MS Updates and fixes) the
>>client application has not changed apart from cosmetically. The connection
>>is through ADODB MDAC 2.8
>>Thanks for the time. Hope this is clear.
ProcessId remains in memory?
Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entrie
s
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.
4ax.com...[vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>
Server 2003 and notice that applications that have been running during the
day, have more and more ProcessID's for the same application. The
applications sometimes is closed by the user, sometimes crash, but usually
they will (after a refresh of EM) not show. I see the Host name in the list
of items in Process Info, and now see that each machine has multiple entries
for the same application. It seems that the process Info remains fixed in
server memory.
Anyone know why this should have just started to happed. As far as I can
tell, only the clients would have any updates (MS Updates and fixes) the
client application has not changed apart from cosmetically. The connection
is through ADODB MDAC 2.8
Thanks for the time. Hope this is clear.The only times I have seen behavior like what I think you are
describing it was always a problem with the application. Rather than
using the connection it already had open, it kept opening a new
connection with each command. It was easy to see when monitoring
using Performance Monitor, watching the number of connections
climbing.
Which is not to say that your problem is the same, but it sure sounds
familiar.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
<hughessatmdnationwide.co.uk> wrote:
>Can anyone help here. I monitor SQL Server EM (2000) SP4 running on Windows
>Server 2003 and notice that applications that have been running during the
>day, have more and more ProcessID's for the same application. The
>applications sometimes is closed by the user, sometimes crash, but usually
>they will (after a refresh of EM) not show. I see the Host name in the list
>of items in Process Info, and now see that each machine has multiple entrie
s
>for the same application. It seems that the process Info remains fixed in
>server memory.
>Anyone know why this should have just started to happed. As far as I can
>tell, only the clients would have any updates (MS Updates and fixes) the
>client application has not changed apart from cosmetically. The connection
>is through ADODB MDAC 2.8
>Thanks for the time. Hope this is clear.|||Thanks, I believe that you are right, one of our developers did m ake a
change to the application, and did not free a query after use. This I think
has kept the connections open. I thought that SQL Server would release
unused connections after a period of inactivity but I guess that is not the
case. Oh well back to the drawing board
thanks for the input.
regards
steve
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6jpp02dg831g66klbjtrt1isq4c2jquldk@.
4ax.com...[vbcol=seagreen]
> The only times I have seen behavior like what I think you are
> describing it was always a problem with the application. Rather than
> using the connection it already had open, it kept opening a new
> connection with each command. It was easy to see when monitoring
> using Performance Monitor, watching the number of connections
> climbing.
> Which is not to say that your problem is the same, but it sure sounds
> familiar.
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 6 Mar 2006 12:23:26 -0000, "Steve"
> <hughessatmdnationwide.co.uk> wrote:
>
Friday, March 9, 2012
Processes blocking RESOURCE MONITOR, normal behaviour?
Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor? Is it fatal if some processes are blocking RESOURCE MONITOR? How can one end up in such situation, is it normal or a bug somewhere?
The server is a 64-bit Windows server having SQL Server 2005 SP1.
Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.
The details if the processes as seen from ActÃvity Monitor (I only have screenshots so I can't copy-paste...):
The main process:
Process id: 69
status: suspended
open transactions: 1
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 578
Wait type: ASYNC_NETWORK_ID
CPU: 1375
Physical IO: 22
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 0
Two "subthreads", there are five similar.
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293046
Wait type: CXPACKET
CPU: 4875
Physical IO: 2214
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 1
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293031
Wait type: CXPACKET
CPU: 4875
Physical IO: 2210
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 2
The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.
Alright Chap,
You need to look at the "BLOCKED BY" rather than "BLOCKING" column.
The BLOCKING=1 means that this process is blocking another process.
Looking at the info you provided, the SPID 69 is not being blocked by any process.
Hope that helps.
Jag
|||Hi
First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.
On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.
|||
Jag Sandhu wrote:
Alright Chap,
You need to look at the "BLOCKED BY" rather than "BLOCKING" column.
The BLOCKING=1 means that this process is blocking another process.
Looking at the info you provided, the SPID 69 is not being blocked by any process.
You are right, 69 is not blocking anything. I'm not interested in what 69 is blocking.
The problem is that 69 IS blocking SPID 1. SPID 1 is a system process, whose significance I don't know. During the problem 69 had been blocking 1 for a long time and the SQL server had been quite jammed. I was suspecting that the jamming was because the system process 1 couldn't do anythin being blocked by 69.
|||
First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.
Thanks for the information, I actually was unaware of the issue. This time I am not using a context connection, so the KB-entry is not valid in my case? I have two SQL Server instances and the CLR stored procedures run on instance A and use a "normal" SQL Server connection (instead of context connection) to connect to the server B.
On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.
OK, thanks for this information too. I find this CXPACKET issue also very strange since our database is in practise idle most of the time. And when this issue I reported happened the connection 69 had been in CXPACKET state for a while (don't know details, but maybe at least minutes). Shuoldn't the CXPACKET state change to something else after a while? Can this have something to do with the RESOURCE MONITOR process being blocked by process 69?
|||Hi JM_F,
When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.
The values are 1 or 0 for Yes or NO respectively.
You have to use SP_who2 or DMV - sys.dm_exec_requests
and look for spid 69 in the BLOCKED by column.
regards
Jag
|||
When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.
The values are 1 or 0 for Yes or NO respectively.
Hello Jag,
If I open Activity Monitor and click help, the following comes:
Blocked By
Process ID (SPID) of a blocking process.
Blocking
Process ID (SPID) of processes that are blocked.
This very clearly states that the value of blocking column contains the process ID. You say it contains 0 or 1. Are you sure of this?
JM
|||Hi JM,
To confirm, please see the books online topic - Activity Monitor (Process Info Page).
- Process ID
SQL Server Process ID.
- User
ID of the user who executed the command.
- Database
Database currently being used by the process.
- Status
Status of the process (for example, running, sleeping, runnable, and background).
- Open Transactions
Number of open transactions for the process.
- Command
Command currently being executed.
- Application
Name of the application program being used by the process.
- Wait Time
Current wait time in milliseconds. When the process is not waiting, the wait time is zero.
- Wait Type
Indicates the name of the last or current wait type.
- Resource
Textual representation of a lock resource.
- CPU
Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.
- Physical IO
Cumulative disk reads and writes for the process.
- Memory Usage
Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
- Login Time
Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.
- Last Batch
Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the displayed time is that at which SQL Server startup occurred.
- Host
Name of the workstation.
- Net Library
Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. .
- Net Address
Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.
- Blocked By
Process ID (SPID) of a blocking process.
- Blocking
Indicates whether this process is blocking others. 1 = yes; 0 = no.
- Execution Context
Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
To confirm, please see the books online topic - Activity Monitor (Process Info Page).
Blocking
Indicates whether this process is blocking others. 1 = yes; 0 = no.
Interesting... I found the entry you pointed in MSDN (http://msdn2.microsoft.com/en-us/library/ms178520.aspx). However the help page in my local installation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/12f87b09-bf20-4a69-8333-e67419472337.htm) contains the description I sent before. So these descriptions don't match.
Can I assume this a bug in SQL Server local documentation?
I have SQL Server 2005 SP 1 on Windows XP.
regards,
JM
|||MS does a very good job of keeping sql bol on msdn2 current. You should download the latest one from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx|||Hi JM,
Please get the latest bol.
By the way, BLOCKING thing is even same for SQL 2000. So its always been like that.
regards
Jag
processadmin role
I need a mamber of my team to monitor some jobs set up by a DBA using SQL Job
I dont want to give him sysadmin prviledge
He should be able to see the job history and re-run the job if it fail
Should i give him processadmin rol
what does processadmin role exactly d
ThanksHi,
Process admin Role
--
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks
I dont want to give him sysadmin prviledge
He should be able to see the job history and re-run the job if it fail
Should i give him processadmin rol
what does processadmin role exactly d
ThanksHi,
Process admin Role
--
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks
processadmin role
I need a mamber of my team to monitor some jobs set up by a DBA using SQL Jobs
I dont want to give him sysadmin prviledges
He should be able to see the job history and re-run the job if it fails
Should i give him processadmin role
what does processadmin role exactly do
Thanks
Hi,
Process admin Role
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks
I dont want to give him sysadmin prviledges
He should be able to see the job history and re-run the job if it fails
Should i give him processadmin role
what does processadmin role exactly do
Thanks
Hi,
Process admin Role
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks
Saturday, February 25, 2012
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||
Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
Subscribe to:
Posts (Atom)