Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
Saturday, February 25, 2012
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.
Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.
Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
CPU dedicated to one Stored Procedure
Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this S
P
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:
> Hi,
> I have one Stored prodecure that need too much time to finish. When this S
P
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
Cpu decuduction
Is there any way that i can deduct how much cpu is taking byu each and every
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking
"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
.. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert
|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> every
> .. i
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>
|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
[vbcol=seagreen]
> "Robert Klemme" wrote:
and[vbcol=seagreen]
one[vbcol=seagreen]
plan[vbcol=seagreen]
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking
"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
.. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert
|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> every
> .. i
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>
|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
[vbcol=seagreen]
> "Robert Klemme" wrote:
and[vbcol=seagreen]
one[vbcol=seagreen]
plan[vbcol=seagreen]
Cpu decuduction
Is there any way that i can deduct how much cpu is taking byu each and every
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> every
> .. i
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
[vbcol=seagreen]
> "Robert Klemme" wrote:
>
and[vbcol=seagreen]
one[vbcol=seagreen]
plan[vbcol=seagreen]
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> every
> .. i
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
[vbcol=seagreen]
> "Robert Klemme" wrote:
>
and[vbcol=seagreen]
one[vbcol=seagreen]
plan[vbcol=seagreen]
Cpu decuduction
Is there any way that i can deduct how much cpu is taking byu each and every
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> > Is there any way that i can deduct how much cpu is taking byu each and
> every
> > spid. In the sysprocess the cpu time is cumulative i dont want that one
> .. i
> > want that how much time currently it is taking
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
> "Robert Klemme" wrote:
> >
> > "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> > > Is there any way that i can deduct how much cpu is taking byu each
and
> > every
> > > spid. In the sysprocess the cpu time is cumulative i dont want that
one
> > .. i
> > > want that how much time currently it is taking
> >
> > Use the profiler and / or perfmon - that will give you detailed
> > information about the load generated by SQL staments. The execution
plan
> > (visible via Profiler and Query Analyzer) is also very informative.
> >
> > Kind regards
> >
> > robert
> >
> >
spid. In the sysprocess the cpu time is cumulative i dont want that one . i
want that how much time currently it is taking"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> Is there any way that i can deduct how much cpu is taking byu each and
every
> spid. In the sysprocess the cpu time is cumulative i dont want that one
. i
> want that how much time currently it is taking
Use the profiler and / or perfmon - that will give you detailed
information about the load generated by SQL staments. The execution plan
(visible via Profiler and Query Analyzer) is also very informative.
Kind regards
robert|||hi thanks for the reply but i want to know the query not from the profiler .
Is there is any query to check current cpu time not the cumulative one
"Robert Klemme" wrote:
> "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> > Is there any way that i can deduct how much cpu is taking byu each and
> every
> > spid. In the sysprocess the cpu time is cumulative i dont want that one
> .. i
> > want that how much time currently it is taking
> Use the profiler and / or perfmon - that will give you detailed
> information about the load generated by SQL staments. The execution plan
> (visible via Profiler and Query Analyzer) is also very informative.
> Kind regards
> robert
>|||"sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:439850D6-3E83-46B0-A836-EE620D397D73@.microsoft.com...
> hi thanks for the reply but i want to know the query not from the
profiler .
Why not?
> Is there is any query to check current cpu time not the cumulative one
What information will that give to you? Profiler data is much more
detailed and thus you'll gain a lot more info from that.
If you want CPU load, then you can use perfmon and taskmanager.
robert
> "Robert Klemme" wrote:
> >
> > "sunil" <sunil@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:3F7183A4-FD17-40A0-A808-E52188B62B68@.microsoft.com...
> > > Is there any way that i can deduct how much cpu is taking byu each
and
> > every
> > > spid. In the sysprocess the cpu time is cumulative i dont want that
one
> > .. i
> > > want that how much time currently it is taking
> >
> > Use the profiler and / or perfmon - that will give you detailed
> > information about the load generated by SQL staments. The execution
plan
> > (visible via Profiler and Query Analyzer) is also very informative.
> >
> > Kind regards
> >
> > robert
> >
> >
CPU counter in SQL Profiler - milliseconds or meaningless?
Good evening,
I recently got out of a performance workshop here at SQL PASS in Orlando,
and was told by the presenter that the CPU counter in SQL Profiler does not
represent the total CPU time in milliseconds like BOL claims, but is in fact
a "meaningless" number. He says that he got this info from members of the SQL
Server team at Microsoft. While I have followed this gentleman's advice
before, I am skeptical about this claim. Several of the other folks in the
room agreed with him, but none could give me a reference to a site or
document to confirm this. I have done some searching, but have come up with
nothing. I would appreciate an "official" answer from one of the MS support
team.
My understanding has always been that CPU shows total CPU time, which can be
compared to Duration to help determine if a long-running query is slow
because of resource utilization (values are close to each other) or the query
has been slowed down by network, I/O or other "external" factors (CPU is
significantly lower than Duration). If my interpretation is wrong, I'd
greatly appreciate being set straight since I currently use this
thought-process when interpreting my own traces. I am now aware of the
RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
Thanks,
James
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number.
My understanding (which I'll be glad to see confirmed or refuted) is that
this number does indeed represent CPU milliseconds. However, the CPU number
shown in execution plans is "meaningless" (i.e., only relative).
|||Interesting, who presented this? You can come to my session on Friday about
SQL Trace Internals or come and see me at the Ask the Experts at PASS
The real question is if the CPU column for each event is really that useful?
Some of them in my opinion are not.
Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
http://msdn.microsoft.com/library/de...hreadtimes.asp)
For every session this information is maintained on the PSS structure in
milliseconds.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
> not
> represent the total CPU time in milliseconds like BOL claims, but is in
> fact
> a "meaningless" number. He says that he got this info from members of the
> SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
> with
> nothing. I would appreciate an "official" answer from one of the MS
> support
> team.
> My understanding has always been that CPU shows total CPU time, which can
> be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
> query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James
|||Brad McGee of sql-server-performance.com was the presenter. Meanwhile, I
spoke to some of the PSS guys in the lab and they said that the CPU counter
does indeed represent milliseconds.
And yes, I will be at your session tomorrow. Thanks for your help!
James
"Gert E.R. Drapers" wrote:
> Interesting, who presented this? You can come to my session on Friday about
> SQL Trace Internals or come and see me at the Ask the Experts at PASS
> The real question is if the CPU column for each event is really that useful?
> Some of them in my opinion are not.
> Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
> http://msdn.microsoft.com/library/de...hreadtimes.asp)
> For every session this information is maintained on the PSS structure in
> milliseconds.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2004 All rights reserved.
> "James Cook" <James Cook@.discussions.microsoft.com> wrote in message
> news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
>
>
|||Hi James,
There might be a little miscommunication here. Many of the PerfMon
counters, when they are measured per second or even per millisecond, are
cumulative rather than absolute values. So, let's say you're polling a
counter called 'widgets/sec' very 15 seconds. The values might come back as
1116, 1132, 1148, 1164, and so on and so on. Because this number is not an
absolute value, but a cumulative value, you might at first think that you've
got an ever increasing value. But in reality, the counter has a constant
absolute value of 16. You get to that conclusion by subtracting the last
polled value from the current polled value. So it's something you just have
to be careful about.
There is a Knowledge Base article that explains this quirk in greater
detail, including a list of all affected PerfMon counters. The article is
written by another SQL Server MVP, Geogh Hiten. Way to go Geoff! <g> Check
out:
http://support.microsoft.com/default...&Product=sql2k
Hope this helps,
-Kevin
SQL Server MVP
I support PASS, the Professional Association for SQL Server.
www.sqlpass.org
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number. He says that he got this info from members of the
SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
with
> nothing. I would appreciate an "official" answer from one of the MS
support
> team.
> My understanding has always been that CPU shows total CPU time, which can
be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James
I recently got out of a performance workshop here at SQL PASS in Orlando,
and was told by the presenter that the CPU counter in SQL Profiler does not
represent the total CPU time in milliseconds like BOL claims, but is in fact
a "meaningless" number. He says that he got this info from members of the SQL
Server team at Microsoft. While I have followed this gentleman's advice
before, I am skeptical about this claim. Several of the other folks in the
room agreed with him, but none could give me a reference to a site or
document to confirm this. I have done some searching, but have come up with
nothing. I would appreciate an "official" answer from one of the MS support
team.
My understanding has always been that CPU shows total CPU time, which can be
compared to Duration to help determine if a long-running query is slow
because of resource utilization (values are close to each other) or the query
has been slowed down by network, I/O or other "external" factors (CPU is
significantly lower than Duration). If my interpretation is wrong, I'd
greatly appreciate being set straight since I currently use this
thought-process when interpreting my own traces. I am now aware of the
RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
Thanks,
James
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number.
My understanding (which I'll be glad to see confirmed or refuted) is that
this number does indeed represent CPU milliseconds. However, the CPU number
shown in execution plans is "meaningless" (i.e., only relative).
|||Interesting, who presented this? You can come to my session on Friday about
SQL Trace Internals or come and see me at the Ask the Experts at PASS
The real question is if the CPU column for each event is really that useful?
Some of them in my opinion are not.
Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
http://msdn.microsoft.com/library/de...hreadtimes.asp)
For every session this information is maintained on the PSS structure in
milliseconds.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
> not
> represent the total CPU time in milliseconds like BOL claims, but is in
> fact
> a "meaningless" number. He says that he got this info from members of the
> SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
> with
> nothing. I would appreciate an "official" answer from one of the MS
> support
> team.
> My understanding has always been that CPU shows total CPU time, which can
> be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
> query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James
|||Brad McGee of sql-server-performance.com was the presenter. Meanwhile, I
spoke to some of the PSS guys in the lab and they said that the CPU counter
does indeed represent milliseconds.
And yes, I will be at your session tomorrow. Thanks for your help!
James
"Gert E.R. Drapers" wrote:
> Interesting, who presented this? You can come to my session on Friday about
> SQL Trace Internals or come and see me at the Ask the Experts at PASS
> The real question is if the CPU column for each event is really that useful?
> Some of them in my opinion are not.
> Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
> http://msdn.microsoft.com/library/de...hreadtimes.asp)
> For every session this information is maintained on the PSS structure in
> milliseconds.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2004 All rights reserved.
> "James Cook" <James Cook@.discussions.microsoft.com> wrote in message
> news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
>
>
|||Hi James,
There might be a little miscommunication here. Many of the PerfMon
counters, when they are measured per second or even per millisecond, are
cumulative rather than absolute values. So, let's say you're polling a
counter called 'widgets/sec' very 15 seconds. The values might come back as
1116, 1132, 1148, 1164, and so on and so on. Because this number is not an
absolute value, but a cumulative value, you might at first think that you've
got an ever increasing value. But in reality, the counter has a constant
absolute value of 16. You get to that conclusion by subtracting the last
polled value from the current polled value. So it's something you just have
to be careful about.
There is a Knowledge Base article that explains this quirk in greater
detail, including a list of all affected PerfMon counters. The article is
written by another SQL Server MVP, Geogh Hiten. Way to go Geoff! <g> Check
out:
http://support.microsoft.com/default...&Product=sql2k
Hope this helps,
-Kevin
SQL Server MVP
I support PASS, the Professional Association for SQL Server.
www.sqlpass.org
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number. He says that he got this info from members of the
SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
with
> nothing. I would appreciate an "official" answer from one of the MS
support
> team.
> My understanding has always been that CPU shows total CPU time, which can
be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James
Cpu count in the Profiler and Task Manager
Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
Rich
Rich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich
|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
Rich
Rich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich
|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>
Cpu count in the Profiler and Task Manager
Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus t
he
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus t
he
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>
Cpu count in the Profiler and Task Manager
Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> > Hello Group,
> >
> > does anyone know if there is a relationship between the CPU count that is
> > displayed in the SQL Profiler and the Task Manager? I have a trace
> > running
> > to show when a long insert takes places and the CPU column displays "16".
> > The same moment I have the Task Manager open and I am looking at the
> > SQLSERV.exe it displayes "40".
> >
> > I thought that the numbers would be the same.
> >
> > Rich
>
>
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> > Hello Group,
> >
> > does anyone know if there is a relationship between the CPU count that is
> > displayed in the SQL Profiler and the Task Manager? I have a trace
> > running
> > to show when a long insert takes places and the CPU column displays "16".
> > The same moment I have the Task Manager open and I am looking at the
> > SQLSERV.exe it displayes "40".
> >
> > I thought that the numbers would be the same.
> >
> > Rich
>
>
CPU cost to physical reads?
Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>> Me again, with yet another question/rant about performance tuning.
>> Is there a CPU cost, as reported to profiler, when a read is physical
>> and not logical?
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
>> guess), start QA, start profiler, run big, fat query, takes 75 seconds
>> (including several recompiles), including 200k reads and 42 seconds of
>> CPU. The difference in time is presumably (and logically, from a cold
>> start) physical disk I/O.
>> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
>> is only 6 seconds.
>> Even if the compile-times all counted as CPU (does it?), that would
>> only be about 24 seconds, not enough to explain the earlier 42.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>> It sure seems like there is a correspondence between physical reads
>> and CPU time.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>> Me again, with yet another question/rant about performance tuning.
>> Is there a CPU cost, as reported to profiler, when a read is physical
>> and not logical?
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
>> guess), start QA, start profiler, run big, fat query, takes 75 seconds
>> (including several recompiles), including 200k reads and 42 seconds of
>> CPU. The difference in time is presumably (and logically, from a cold
>> start) physical disk I/O.
>> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
>> is only 6 seconds.
>> Even if the compile-times all counted as CPU (does it?), that would
>> only be about 24 seconds, not enough to explain the earlier 42.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>> It sure seems like there is a correspondence between physical reads
>> and CPU time.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
CPU cost to physical reads?
Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.
see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.
|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.
|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.
see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.
|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.
|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
CPU cost to physical reads?
Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com
cpu at 100% by sqlserver.exe
Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)
Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>
|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)
Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>
|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
cpu at 100% by sqlserver.exe
Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cann
ot
> explain it by looking at the start/end times of backup's, large queries et
c
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cann
ot
> explain it by looking at the start/end times of backup's, large queries et
c
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
cpu at 100% by sqlserver.exe
Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
CPU and MEMORY usage
Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfomingexecute sp_who2 active
this will tell you whos is on your db and how much cpu (CPUTime) and mem (DISKIO) each user is taking
Cheers|||Actually gives the user activity accross the server you run it on. Not just the db activity - Sorry|||should use Performance Mointor to assess your server activity over a period of time.
you can log this info in .csv then import into SQL Server
have a look at :
www.sql-server-performance.com/performance_monitor_tips.asp
cheers|||On sp_who2 does the CPUTime equals to the CPU usage on the task manager and what about the Memory because i also want this memory to be the same as the one on the Task Manager|||Not sure about that. I think cputime that a sp_who will show the diskIO for each user and system query that occurs at the time of the SP execution. This does not take into consideration the O/S resources getting used.
I guess you could find out how much mem is allocated to SQL by looking at the properties of the server (right click on the server in Enterprise Manger) . From this figure you can work out how much resources are getting used in terms of how much mem has been allocated to SQL Server.
Cheers|||I 100% agree with you when you say we can manually look at properties and again on the task manager properties, the thing is we want a script that will do this for us automatically every hour to check the memory and cpu usage.|||Use performance mointor as it is the easiest way to shedule.
Look over the link above to see what counters you want to look at.
i.e Memory -- Pages/Sec, Memory Manager -- Total Server Memory (KB)
etc
What you could do is shedule it to output a csv file on the hour then set up a dts task to import this into SQL Server for anyalsis.
Once you know how much mem you allocated to SQL Server then you have constant that you can do calculations with.
Cheers|||Thanks, I think this will work perfect
this will tell you whos is on your db and how much cpu (CPUTime) and mem (DISKIO) each user is taking
Cheers|||Actually gives the user activity accross the server you run it on. Not just the db activity - Sorry|||should use Performance Mointor to assess your server activity over a period of time.
you can log this info in .csv then import into SQL Server
have a look at :
www.sql-server-performance.com/performance_monitor_tips.asp
cheers|||On sp_who2 does the CPUTime equals to the CPU usage on the task manager and what about the Memory because i also want this memory to be the same as the one on the Task Manager|||Not sure about that. I think cputime that a sp_who will show the diskIO for each user and system query that occurs at the time of the SP execution. This does not take into consideration the O/S resources getting used.
I guess you could find out how much mem is allocated to SQL by looking at the properties of the server (right click on the server in Enterprise Manger) . From this figure you can work out how much resources are getting used in terms of how much mem has been allocated to SQL Server.
Cheers|||I 100% agree with you when you say we can manually look at properties and again on the task manager properties, the thing is we want a script that will do this for us automatically every hour to check the memory and cpu usage.|||Use performance mointor as it is the easiest way to shedule.
Look over the link above to see what counters you want to look at.
i.e Memory -- Pages/Sec, Memory Manager -- Total Server Memory (KB)
etc
What you could do is shedule it to output a csv file on the hour then set up a dts task to import this into SQL Server for anyalsis.
Once you know how much mem you allocated to SQL Server then you have constant that you can do calculations with.
Cheers|||Thanks, I think this will work perfect
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>
|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
CPU and memory usage
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any w
ay
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.Unfortunatly there is not a straight answer for your query.
My first question is are you sure its SQL Server that causing it, i.e. have
you looked into TASK MANAGER to see what process has the most CPU time ?
You could also set up a Performance check under Administration Tool, you can
set up CPU as one of the things it check.
Could you give an indication of the size of the database and the version of
SQL Server you using.
If you like although its not really recommended you can change the CPU's SQL
Server uses. You go to the properties of the Server under Enterprise Manager
then select Processors, just read up on BOL when you get there.
You could also check which jobs are running at the time, both continually
and at certain times, then have a look at optimising them.
However according to SQL Server 2000 Performance Tuning if the CPU usage is
greater than 80% then generally you need new processors.
"CC&JM" wrote:
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any w
ay
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||Maybe these will help:
ut while DB expanding
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
> Hello,
> I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
> currently consuming these CPU's and i cannot detect what is
> affecting(degreeding performance) so much SQL Server.
> Where should i need to start troubleshooting my bottleneck? Is there any
> way
> or free tool that i can use without affecting my system?
> Thanks and best regards.
>|||The biggest problem you have is to diagnose whether or not SQL Server is
performing legitimate work or not. If it is, then, as the others have
suggested, you are probably over capacity. If not, I would be suspecious
that you are being hit by a "slammer" like attack.
Here are some tests.
Block UDP port 1434 from the IPSec policies on the connecting
switch/firewall or SQL Server host itself. If the acitivity drops, you were
either being attacked, or legitimate clients have now all been locked out.
Take a look at SQLServer:Databases Transactions per Second and
SQLServer:Statistics Batch Requests per Second. This will tell you how much
activity is being thrown at the server.
Check the ProcessorTime% and PageFaults/sec for the sqlservr process. If
SQL Server is page faulting, you are probably shy on memory. You say you
have 4 GB of ram, but how are you using it? Have you enabled 4 GB Tuning?
Do you have SS2K Standard or Enterprise Edition. If EE, have you enabled
AWE or considered going multi-instanced?
What build level are you running? There was a MS security patch for SP2,
which was rolled up into SP3. There is also a post SP3a (build 760)
security patch, MS03-031 (builds 818 and 819). Finally, the most recent
publicly available hot fix is build 878.
First make sure you are not "infected," then I would concentrate on the
performance tuning and capacity planning.
Sincerely,
Anthony Thomas
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:7D64EBA4-A145-4BF0-A420-84E1E6D1BE50@.microsoft.com...
Hello,
I have one database server, with 4 CPUs and 4GB of RAM, SQL Server is
currently consuming these CPU's and i cannot detect what is
affecting(degreeding performance) so much SQL Server.
Where should i need to start troubleshooting my bottleneck? Is there any way
or free tool that i can use without affecting my system?
Thanks and best regards.
CPU and Memory counters
Hi,
Id like to collect some information about SQL server itself like "% of CPU
Usage" or "% of memory usage".
Something like SELECT @.@.CPU_Usage ...... ?
Thanks,
Tim.Use Performance Monitor. You can set it up to record automagically on
specific schedules/durations/frequencies as well as choosing your
objects/counters.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Id like to collect some information about SQL server itself like "% of CPU
> Usage" or "% of memory usage".
> Something like SELECT @.@.CPU_Usage ...... ?
> Thanks,
> Tim.
>|||Thank you, Geoff,
problem is - I need this counter into me SELECT statement to put it in a
table.
Any idea?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Use Performance Monitor. You can set it up to record automagically on
> specific schedules/durations/frequencies as well as choosing your
> objects/counters.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Tim Xox" <timur@.mtgsi.com> wrote in message
> news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > Id like to collect some information about SQL server itself like "% of
CPU
> > Usage" or "% of memory usage".
> > Something like SELECT @.@.CPU_Usage ...... ?
> > Thanks,
> > Tim.
> >
> >
>|||Take a look at the sysperfinfo table.
--
Andrew J. Kelly
SQL Server MVP
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uZfayn9oDHA.488@.tk2msftngp13.phx.gbl...
> Thank you, Geoff,
> problem is - I need this counter into me SELECT statement to put it in a
> table.
> Any idea?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Use Performance Monitor. You can set it up to record automagically on
> > specific schedules/durations/frequencies as well as choosing your
> > objects/counters.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> > "Tim Xox" <timur@.mtgsi.com> wrote in message
> > news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > Id like to collect some information about SQL server itself like "% of
> CPU
> > > Usage" or "% of memory usage".
> > > Something like SELECT @.@.CPU_Usage ...... ?
> > > Thanks,
> > > Tim.
> > >
> > >
> >
> >
>
Id like to collect some information about SQL server itself like "% of CPU
Usage" or "% of memory usage".
Something like SELECT @.@.CPU_Usage ...... ?
Thanks,
Tim.Use Performance Monitor. You can set it up to record automagically on
specific schedules/durations/frequencies as well as choosing your
objects/counters.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Id like to collect some information about SQL server itself like "% of CPU
> Usage" or "% of memory usage".
> Something like SELECT @.@.CPU_Usage ...... ?
> Thanks,
> Tim.
>|||Thank you, Geoff,
problem is - I need this counter into me SELECT statement to put it in a
table.
Any idea?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Use Performance Monitor. You can set it up to record automagically on
> specific schedules/durations/frequencies as well as choosing your
> objects/counters.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Tim Xox" <timur@.mtgsi.com> wrote in message
> news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > Id like to collect some information about SQL server itself like "% of
CPU
> > Usage" or "% of memory usage".
> > Something like SELECT @.@.CPU_Usage ...... ?
> > Thanks,
> > Tim.
> >
> >
>|||Take a look at the sysperfinfo table.
--
Andrew J. Kelly
SQL Server MVP
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uZfayn9oDHA.488@.tk2msftngp13.phx.gbl...
> Thank you, Geoff,
> problem is - I need this counter into me SELECT statement to put it in a
> table.
> Any idea?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Use Performance Monitor. You can set it up to record automagically on
> > specific schedules/durations/frequencies as well as choosing your
> > objects/counters.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> > "Tim Xox" <timur@.mtgsi.com> wrote in message
> > news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > Id like to collect some information about SQL server itself like "% of
> CPU
> > > Usage" or "% of memory usage".
> > > Something like SELECT @.@.CPU_Usage ...... ?
> > > Thanks,
> > > Tim.
> > >
> > >
> >
> >
>
CPU and I/O affinity config
I have 8 Processors and 12 GB memory box, I want to install two production
SQl Server 2005
instances on this box. Please advise what is the correct seeting to set these
processor affinity and i/0 affinity.
I am planning to assaign 4 CPu for one instace and 3 instances for another
instance.
My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
how to achive this?
4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
mask 2,3 for i/o affinity mask.
Please give some advise or related articleas on this how to do configure.
--
SQL serer DBAHi
According to http://msdn2.microsoft.com/en-us/library/ms189629(SQL.90).aspx
"When specifying the affinity I/O mask option, you must use it with the
affinity mask configuration option. Do not enable the same CPU in both the
affinity I/O mask switch and the affinity mask option"
John
"Srikanth" wrote:
> I have 8 Processors and 12 GB memory box, I want to install two production
> SQl Server 2005
> instances on this box. Please advise what is the correct seeting to set these
> processor affinity and i/0 affinity.
> I am planning to assaign 4 CPu for one instace and 3 instances for another
> instance.
> My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
> how to achive this?
> 4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
> CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
> mask 2,3 for i/o affinity mask.
> Please give some advise or related articleas on this how to do configure.
>
> --
> SQL serer DBA
SQl Server 2005
instances on this box. Please advise what is the correct seeting to set these
processor affinity and i/0 affinity.
I am planning to assaign 4 CPu for one instace and 3 instances for another
instance.
My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
how to achive this?
4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
mask 2,3 for i/o affinity mask.
Please give some advise or related articleas on this how to do configure.
--
SQL serer DBAHi
According to http://msdn2.microsoft.com/en-us/library/ms189629(SQL.90).aspx
"When specifying the affinity I/O mask option, you must use it with the
affinity mask configuration option. Do not enable the same CPU in both the
affinity I/O mask switch and the affinity mask option"
John
"Srikanth" wrote:
> I have 8 Processors and 12 GB memory box, I want to install two production
> SQl Server 2005
> instances on this box. Please advise what is the correct seeting to set these
> processor affinity and i/0 affinity.
> I am planning to assaign 4 CPu for one instace and 3 instances for another
> instance.
> My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
> how to achive this?
> 4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
> CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
> mask 2,3 for i/o affinity mask.
> Please give some advise or related articleas on this how to do configure.
>
> --
> SQL serer DBA
CPU and I/O affinity config
I have 8 Processors and 12 GB memory box, I want to install two production
SQl Server 2005
instances on this box. Please advise what is the correct seeting to set thes
e
processor affinity and i/0 affinity.
I am planning to assaign 4 CPu for one instace and 3 instances for another
instance.
My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
how to achive this?
4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
mask 2,3 for i/o affinity mask.
Please give some advise or related articleas on this how to do configure.
SQL serer DBAHi
According to http://msdn2.microsoft.com/en-us/library/ms189629(SQL.90).aspx
"When specifying the affinity I/O mask option, you must use it with the
affinity mask configuration option. Do not enable the same CPU in both the
affinity I/O mask switch and the affinity mask option"
John
"Srikanth" wrote:
> I have 8 Processors and 12 GB memory box, I want to install two production
> SQl Server 2005
> instances on this box. Please advise what is the correct seeting to set th
ese
> processor affinity and i/0 affinity.
> I am planning to assaign 4 CPu for one instace and 3 instances for another
> instance.
> My confusion is i need to use same CPUs for CPU affinity and i/o affinity
or
> how to achive this?
> 4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 u
se
> CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
> mask 2,3 for i/o affinity mask.
> Please give some advise or related articleas on this how to do configure.
>
> --
> SQL serer DBA
SQl Server 2005
instances on this box. Please advise what is the correct seeting to set thes
e
processor affinity and i/0 affinity.
I am planning to assaign 4 CPu for one instace and 3 instances for another
instance.
My confusion is i need to use same CPUs for CPU affinity and i/o affinity or
how to achive this?
4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 use
CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
mask 2,3 for i/o affinity mask.
Please give some advise or related articleas on this how to do configure.
SQL serer DBAHi
According to http://msdn2.microsoft.com/en-us/library/ms189629(SQL.90).aspx
"When specifying the affinity I/O mask option, you must use it with the
affinity mask configuration option. Do not enable the same CPU in both the
affinity I/O mask switch and the affinity mask option"
John
"Srikanth" wrote:
> I have 8 Processors and 12 GB memory box, I want to install two production
> SQl Server 2005
> instances on this box. Please advise what is the correct seeting to set th
ese
> processor affinity and i/0 affinity.
> I am planning to assaign 4 CPu for one instace and 3 instances for another
> instance.
> My confusion is i need to use same CPUs for CPU affinity and i/o affinity
or
> how to achive this?
> 4 CPU for one instanec - 0,1,2,3. here confusion i need to check 0,1,2,3 u
se
> CPY affinitymask and 0,1,2,3 to i/o affinity mask or 0,1 for CPU affinity
> mask 2,3 for i/o affinity mask.
> Please give some advise or related articleas on this how to do configure.
>
> --
> SQL serer DBA
CPU and Duration don't match in SQL Server Profiler
Hello All,
I have one store proc that is really slowing down my SQL server, at least according to the SQL Profiler. When I run the profiler and choose to show store procs that take longer then 3000 ms to execute, this is the only precedure that shows up. The CPU time and Duration is both in the 3000 ms mark. When I open up Query Analyzer and run that same store proc it takes less then a second, but when I check the Profiler to see the time, it's Blank (yellow) .. I'm using SQL 2005 Profiler. Any ideas why
1. I don't see a time when I execute the query from the profiler?
2. It says it takes 3 seconds when I only see it taking less then one?
Thank you
This is probably 3000 microseconds (3 milliseconds) :)
"In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds."
Books Online:
http://msdn2.microsoft.com/en-us/library/ms175848.aspx
SQL Server Enthusiast:
http://weblogs.sqlteam.com/tarad/archive/2006/10/04/13621.aspx
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
CPU 100%
Hi guys
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
Jonas
It seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks
|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
Jonas
It seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks
|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>
CPU 100%
Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXS
Hello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default...b;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default...b;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?
|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXS
Hello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default...b;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default...b;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?
|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
CPU 100%
Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/defaul...kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/defaul...kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
CPU 100%
Hi guys
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
JonasIt seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
JonasIt seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>
CPU 100%
I have written an application that needs to constantly communicate with SQL Server . The application works fine but I am having issues with the SQL server spiking to 100% CPU utilisation constanly.
I have no triggers or cursors in my database. All my calls to the database are via stored procedures and almost all the code in the stored procedures are Clustered Index Seeks retuning maximum 1 or 2 rows.
example :
Code Snippet
IF NOT EXISTS(Select PrimaryKey from TableName where (PrimaryKey = 'Value'))
BEGIN
INSERT INTO TableName (PrimaryKey,Column1,Column2) Values (@.PrimaryKey,@.Column1,@.Column2)
END
ELSE
BEGIN
UPDATE TableName SET Column1 = @.Column1, Column2 = @.Column2 WHERE PrimaryKey = @.PrimaryKey
END
I ran through the profiler and there were no errors reported and all the stats show that the stored procedures take maximum 1 second to execute. However the CPU on the box is constanly running at 100%. I am not sure what I need to do.
I use full text indexing but I at the moment that is not turned on so it cannot contribute to the CPU usage.
Some tables have 100,000 rows and other have around a million rows. I noticed the queres to the tables that have millions of rows are the main tables causing the performance to go to 100%.
The SQL Server box that I am testing on has around 2 GB RAM ,Pentium(4) 3.2GHz. I will appreciate any advise!!
Start the Activity Monitor and see which process (in SQL Server) hold the processor time (column CPU). Maybe this is not a SQL Server problem.Jens K. Suessmeyer
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)