Procedure Time out: cpu

Showing posts with label cpu. Show all posts

Friday, March 30, 2012

Profiler and cpu percentage

Can anyone tell me if its possible to filter the results of the profiler
by the percentage of cpu being used. Using PerfMon I can see that there
are often times when the cpu on our server is using over 80% and I would
like to find out what this is.
regards,
Aaroni would think this is a matter of filtering on the start
time from profiler to match the time of the perfmon spikes
>--Original Message--
>Can anyone tell me if its possible to filter the results
of the profiler
>by the percentage of cpu being used. Using PerfMon I can
see that there
>are often times when the cpu on our server is using over
80% and I would
>like to find out what this is.
>regards,
>Aaron
>.
>sql

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Our production SQL Server's CPU runs continuously at about
50%. Below is a copy of the running Process threads, taken
from SQL Spotlight. Can anyone help identify the
bottleneck and give recommendations for improvement
(besides improving queries)' Your help is greatly
appreciated!
Instance Thread ID % CPU % User % Kernel
Elapsed time Switches/sec Thread State
Thread Wait Reason
5 268 104.65 0 106.32 19d 10hrs 207
921.65 Waiting Waiting for a user request
0 0 52.92 0 52.92 147417d 19hrs
807.51 Running -
1 0 50.18 0 50.18 147417d 19hrs
471.7 Running -
3 0 48.15 0 48.15 147417d 19hrs
495.51 Running -
6 272 40.76 0 42.91 19d 10hrs 0
Waiting Waiting for a user request
2 0 36.47 0 36.47 147417d 19hrs
710.07 Running -
15 10268 3 594.66 405.72 3 188.94 9d
20hrs 215 661.8 Waiting Waiting for a user
request
5 344 1.91 0.36 1.55 19d 10hrs 1
134.55 Waiting Waiting for a user request
1 252 1 906.93 335.88 1 571.05 19d
10hrs 229 163.43 Waiting Waiting for an LPC
Receive notice
4 204 1 830.41 266.39 1 564.01 19d
10hrs 133 979.34 Waiting Waiting for an LPC
Receive notice
8 488 1 810.26 278.07 1 532.19 19d
10hrs 0 Waiting Waiting for an LPC Receive
notice
11 4196 1 683.68 288.32 1 395.36 17d
17hrs 197 806.72 Waiting Waiting for an LPC
Receive notice
12 1408 1 612.76 281.77 1 331 17d
17hrs 163 851.13 Waiting Waiting for an LPC
Receive notice
13 4264 1 596.43 286.41 1 310.02 17d
17hrs 135 660.06 Waiting Waiting for an LPC
Receive notice
0 11584 0.79 0.79 0 1d 6hrs
20.82 Waiting Waiting for a user request
4 276 0.36 0 0.36 19d 10hrs
58.45 Waiting Waiting for a user request
3 228 0.12 0 0.12 19d 10hrs 9
Waiting Waiting for an LPC Receive notice
10 2756 0.12 0 0.12 19d 8hrs
2.67 Waiting Waiting for a user requestCome and check ours. It is running at 90% most of the time.
CPU usage depends on the transactions occuring in the
Server (Unless there are other applications running on the
server). I wouldn't be too concern about it at 50%. If you
are, use the SQL Profiler to find out what processes using
the most CPU usage(the number on Profiler is in
miliseconds).
>--Original Message--
>Our production SQL Server's CPU runs continuously at
about
>50%. Below is a copy of the running Process threads,
taken
>from SQL Spotlight. Can anyone help identify the
>bottleneck and give recommendations for improvement
>(besides improving queries)' Your help is greatly
>appreciated!
>Instance Thread ID % CPU % User % Kernel
> Elapsed time Switches/sec Thread State
> Thread Wait Reason
>5 268 104.65 0 106.32 19d 10hrs 207
> 921.65 Waiting Waiting for a user request
>0 0 52.92 0 52.92 147417d 19hrs
> 807.51 Running -
>1 0 50.18 0 50.18 147417d 19hrs
> 471.7 Running -
>3 0 48.15 0 48.15 147417d 19hrs
> 495.51 Running -
>6 272 40.76 0 42.91 19d 10hrs 0
> Waiting Waiting for a user request
>2 0 36.47 0 36.47 147417d 19hrs
> 710.07 Running -
>15 10268 3 594.66 405.72 3 188.94 9d
> 20hrs 215 661.8 Waiting Waiting for a user
>request
>5 344 1.91 0.36 1.55 19d 10hrs 1
> 134.55 Waiting Waiting for a user request
>1 252 1 906.93 335.88 1 571.05 19d
> 10hrs 229 163.43 Waiting Waiting for an LPC
>Receive notice
>4 204 1 830.41 266.39 1 564.01 19d
> 10hrs 133 979.34 Waiting Waiting for an LPC
>Receive notice
>8 488 1 810.26 278.07 1 532.19 19d
> 10hrs 0 Waiting Waiting for an LPC Receive
>notice
>11 4196 1 683.68 288.32 1 395.36 17d
> 17hrs 197 806.72 Waiting Waiting for an LPC
>Receive notice
>12 1408 1 612.76 281.77 1 331 17d
> 17hrs 163 851.13 Waiting Waiting for an LPC
>Receive notice
>13 4264 1 596.43 286.41 1 310.02 17d
> 17hrs 135 660.06 Waiting Waiting for an LPC
>Receive notice
>0 11584 0.79 0.79 0 1d 6hrs
> 20.82 Waiting Waiting for a user request
>4 276 0.36 0 0.36 19d 10hrs
> 58.45 Waiting Waiting for a user request
>3 228 0.12 0 0.12 19d 10hrs 9
> Waiting Waiting for an LPC Receive notice
>10 2756 0.12 0 0.12 19d 8hrs
> 2.67 Waiting Waiting for a user request
>
>.
>|||However, low CPU usage can be because the machine is waiting for I/O requests a lot. Tuning the
queries, by for instance adding indexes so less I/O is used can be a real benefit in these cases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:6e2901c48392$136f9480$a401280a@.phx.gbl...
> Come and check ours. It is running at 90% most of the time.
> CPU usage depends on the transactions occuring in the
> Server (Unless there are other applications running on the
> server). I wouldn't be too concern about it at 50%. If you
> are, use the SQL Profiler to find out what processes using
> the most CPU usage(the number on Profiler is in
> miliseconds).
>
> >--Original Message--
> >Our production SQL Server's CPU runs continuously at
> about
> >50%. Below is a copy of the running Process threads,
> taken
> >from SQL Spotlight. Can anyone help identify the
> >bottleneck and give recommendations for improvement
> >(besides improving queries)' Your help is greatly
> >appreciated!
> >
> >Instance Thread ID % CPU % User % Kernel
> > Elapsed time Switches/sec Thread State
> > Thread Wait Reason
> >5 268 104.65 0 106.32 19d 10hrs 207
> > 921.65 Waiting Waiting for a user request
> >
> >0 0 52.92 0 52.92 147417d 19hrs
> > 807.51 Running -
> >1 0 50.18 0 50.18 147417d 19hrs
> > 471.7 Running -
> >3 0 48.15 0 48.15 147417d 19hrs
> > 495.51 Running -
> >6 272 40.76 0 42.91 19d 10hrs 0
> > Waiting Waiting for a user request
> >
> >2 0 36.47 0 36.47 147417d 19hrs
> > 710.07 Running -
> >15 10268 3 594.66 405.72 3 188.94 9d
> > 20hrs 215 661.8 Waiting Waiting for a user
> >request
> >5 344 1.91 0.36 1.55 19d 10hrs 1
> > 134.55 Waiting Waiting for a user request
> >
> >1 252 1 906.93 335.88 1 571.05 19d
> > 10hrs 229 163.43 Waiting Waiting for an LPC
> >Receive notice
> >4 204 1 830.41 266.39 1 564.01 19d
> > 10hrs 133 979.34 Waiting Waiting for an LPC
> >Receive notice
> >8 488 1 810.26 278.07 1 532.19 19d
> > 10hrs 0 Waiting Waiting for an LPC Receive
> >notice
> >11 4196 1 683.68 288.32 1 395.36 17d
> > 17hrs 197 806.72 Waiting Waiting for an LPC
> >Receive notice
> >12 1408 1 612.76 281.77 1 331 17d
> > 17hrs 163 851.13 Waiting Waiting for an LPC
> >Receive notice
> >13 4264 1 596.43 286.41 1 310.02 17d
> > 17hrs 135 660.06 Waiting Waiting for an LPC
> >Receive notice
> >0 11584 0.79 0.79 0 1d 6hrs
> > 20.82 Waiting Waiting for a user request
> >
> >4 276 0.36 0 0.36 19d 10hrs
> > 58.45 Waiting Waiting for a user request
> >
> >3 228 0.12 0 0.12 19d 10hrs 9
> > Waiting Waiting for an LPC Receive notice
> >
> >10 2756 0.12 0 0.12 19d 8hrs
> > 2.67 Waiting Waiting for a user request
> >
> >
> >.
> >

Friday, March 23, 2012

processors replacement

Your execution plans are going to need updating. They are
developed using a combination of cpu, memory and disk
speed, so if one changes its going to effect the rest.
Its not a big job, just look up update statistics, then
watch your server fly...
Peter
"The best minds are not in government. If any were,
business would steal them away."
Ronald Reagan

>--Original Message--
>Hi,
>SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>memory (3G used for sql server). We're going to replace
>these processors with the new, faster 4 processors. Is
>there any known problem we can expect regarding starting
>sql services and databases with new processors?
>TIA
>Armin
>.
>
They have to shut down the server to add the upgrades and all execution
plans are lost when you stop sql server anyway. And the plans are not
directly related to disk speed at all. Even CPU and memory are things that
are taken into account at run time and not necessarily at compile time. A
complex plan will always have a single and a parallel plan created the first
time it is run. At run time it is decided based on how busy the cpu's are,
memory available, etc that determine if the single threaded plan is used or
a parallel plan and how many threads generated.
Andrew J. Kelly SQL MVP
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:766e01c494e9$304f2300$a301280a@.phx.gbl...[vbcol=seagreen]
> Your execution plans are going to need updating. They are
> developed using a combination of cpu, memory and disk
> speed, so if one changes its going to effect the rest.
> Its not a big job, just look up update statistics, then
> watch your server fly...
> Peter
> "The best minds are not in government. If any were,
> business would steal them away."
> Ronald Reagan
>

Wednesday, March 21, 2012

Processor Utilization

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

Processor Licensing question

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

Tuesday, March 20, 2012

Processor

How should I know if I need to add new processor to my Server?

During Submission of our Records every 24th day of the month the cpu usage of the server is steady 100% can you please help me what alternative can I do? or how can i check if need to add new processor.

Please help me guys.

thanks

Any sustained CPU usage exceeding 90% indicates a need for more 'processing' power.

If this ONLY occurs on the 24th day of the month, and the remainder of the time CPU utilization is lower, you have a couple of options.

Change the workload on the 24th, spread it out into several smaller 'batches'

Get additional processor power (add one or more CPU(s)).

|||

You should also try to investigate what part of your workload is using the most CPU and see if you can do something about it. For example, you might be missing an index for a frequently run query that is causing more CPU pressure (among other things).

-- Get the most CPU intensive queries

SET NOCOUNT ON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLY READ_ONLY FOR

SELECT TOP 25 spid

FROM master..sysprocesses

WHERE status = 'runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDER BY CPU DESC

OPEN spID_Cursor

FETCH NEXT FROM spID_Cursor

INTO @.spID

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT 'Spid #: ' + STR(@.spID)

EXEC ('DBCC INPUTBUFFER (' + @.spID + ')')

FETCH NEXT FROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

-- Get Top 50 executed SP's ordered by avg worker time

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.total_worker_time/qs.execution_count DESC

-- Missing Indexes

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,

migs.*, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

--WHERE statement = '[ngservices].[dbo].[UserFeedUnreadCountRollup]' -- Specify one table

ORDER BY index_advantage DESC;

|||

Yeah, I guest we need a new server after all . . . this is really makes me difficult . . thanks for the INFO

|||

Hey, I might try to use this script . . . I'll keep you update . . . thanks a lot

Friday, March 9, 2012

Process throttling

quote:

> Hi,
> We have an application accessing an SQL Database. The problem is that due

quote:

> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>

|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...

quote:

> Hi,
> We have an application accessing an SQL Database. The problem is that due

quote:

> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>

|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which

quote:

> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
due[QUOTE]
> to
the[QUOTE]
>

|||if you have a multiprocessor machine, you could limit sql server to a subset
of
your processors. that would leave the other processors free to do other stu
ff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user account
s
so that certain users couldn't hog db resources or certain users' queries wo
uld
get higher priority than others.
Microsoft News - SQL Server wrote:
[QUOTE]
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any on
e
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> in
> due
> the

Process throttling

Hi,
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have an application accessing an SQL Database. The problem is that
due
> to
> > a flaw in design, when queries are submitted we get 100% cpu usage on
the
> > server. Is there any way to limit processing power for specific access?
> >
> > Thanks
> >
> > Nich
> >
> >
>|||if you have a multiprocessor machine, you could limit sql server to a subset of
your processors. that would leave the other processors free to do other stuff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user accounts
so that certain users couldn't hog db resources or certain users' queries would
get higher priority than others.
Microsoft News - SQL Server wrote:
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any one
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Try to identify the SQL's which is doing a table scan. Then based on the
> > where condition in the select statement
> > create useful indexes, this will definitely reduce the Disk reads, which
> in
> > turn reduces the CPU usage.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> > news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > We have an application accessing an SQL Database. The problem is that
> due
> > to
> > > a flaw in design, when queries are submitted we get 100% cpu usage on
> the
> > > server. Is there any way to limit processing power for specific access?
> > >
> > > Thanks
> > >
> > > Nich
> > >
> > >
> >
> >

Wednesday, March 7, 2012

process running long

Recently i saw sysprocess due to high CPU usage n i find out that some
process are shoing login_time which is 2-3 hours old. Does this mean that
the process is runnig for 2-3 hours ?How did you identify the process and the login time?
sp_who2?
The LastBatch column indicates the last time that a specific connection
executed a sql statement.
If you are monitoring connections using sp_who2 you can use the CPUTime and
DiskIO columns to "see" how much work a specific connection (SPID) is doing.
Keith Kratochvil
"Vikram" <aa@.aa> wrote in message
news:uPmZqjDdGHA.536@.TK2MSFTNGP02.phx.gbl...
> Recently i saw sysprocess due to high CPU usage n i find out that some
> process are shoing login_time which is 2-3 hours old. Does this mean that
> the process is runnig for 2-3 hours ?
>|||I used following query
SELECT * FROM SYSPROCESSES
where status = 'runnable'
order by cpu desc
login_time is for connection?
that means a connection showing one hour means its live for 1 hour ?
If i want to get the SP execution time, then how to get it ?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23sXEM$DdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> How did you identify the process and the login time?
> sp_who2?
> The LastBatch column indicates the last time that a specific connection
> executed a sql statement.
> If you are monitoring connections using sp_who2 you can use the CPUTime
and
> DiskIO columns to "see" how much work a specific connection (SPID) is
doing.
> --
> Keith Kratochvil
>
> "Vikram" <aa@.aa> wrote in message
> news:uPmZqjDdGHA.536@.TK2MSFTNGP02.phx.gbl...
that
>|||login_time indicates when that connection was opened (connected).
It is possible for a connection to be sitting idle after performing lots of
work.
Is the CPU time increasing for the connection that you are intersted in?

> If i want to get the SP execution time, then how to get it ?
Do you want to know when the last command was executed? If so check the
LastBatch column within sp_who2 for the particular spid.
If you want to know how long a particular stored procedure took to execute
you would need to be running a trace when the stored procedure was called.
I am not aware of a method to get execution time after the fact.
Keith Kratochvil
"Vikram" <aa@.aa> wrote in message
news:ereZcFEdGHA.4072@.TK2MSFTNGP05.phx.gbl...
>I used following query
> SELECT * FROM SYSPROCESSES
> where status = 'runnable'
> order by cpu desc
> login_time is for connection?
> that means a connection showing one hour means its live for 1 hour ?
> If i want to get the SP execution time, then how to get it ?

Friday, March 30, 2012

Profiler and cpu percentage

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Friday, March 23, 2012

processors replacement

Wednesday, March 21, 2012

Processor Utilization

Processor Licensing question

Tuesday, March 20, 2012

Processor

Friday, March 9, 2012

Process throttling

Process throttling

Wednesday, March 7, 2012

process running long

Procedure Time out

Blog Archive

About Me