Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Monday, March 26, 2012
History of all SQL Executed
Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks
History of all SQL Executed
Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks
Monday, March 19, 2012
High Duration SQL 2005 Profiler
Hello everybody,
I have a very stranger problem that I need to understand...
In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.
Could to help me to identify why passes this?
SQL Server 2005 has the ability to show duration in microseconds.
This can be changed in the options of Profiler.
Maybe this option is turned on?
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||The Duration Field show in microseconds is turned off. The generate value in the profiler for duration field in miliseconds is very high and not real...|||This comes out to approximately 67 minutes. This is not necessarily wrong. A couple of things I've experienced in regards to long durations:
The job being executed is being blocked by something else and is continuously waiting on something else to complete before it can finish execution. The calling application is leaving the recordset open after calling. For example, if you use MS Access to open a table and don't bother to close it then it will show an extremely long duration (until the table is closed). Other applications could do the same thing if the author didn't properly include the code to close the recordset after retrieving the data. The client that's retrieving the data is on a particularly slow connection. Again - Duration is not the time it takes SQL Server to fulfill the request but the time it takes for the client to retrieve the data from the request and close the resultset. We once learned about a misconfigured network connection because durations were high while CPU and READs were low.|||The profiler duration field is in microseconds by default?|||No, its optional for SQL Server 2005.Jens K. Suessmeyer.
http://www.sqlserver2005.de
High CPU value in Profiler
We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.
Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>
|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>
|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.
Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>
|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>
|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
High CPU value in Profiler
We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CP
U
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in me
ssage
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CP
U
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in me
ssage
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
High CPU value in Profiler
We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hassan, do you have Books Online? You should. From there:
>> "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."
>> A
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>>
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> > Hassan, do you have Books Online? You should. From there:
> >
> > "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."
> >
> > A
> >
> >
> > "Hassan" <hassan@.hotmail.com> wrote in message
> > news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> >> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> >> unit for it ? Is it in ms(milliseconds) ?
> >>
> >> If its in milliseconds, what does that mean? Can a high duration of CPU
> >> means higher processing power or just longer time to process ? Please
> >> help me understand.
> >>
> >
> >
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"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."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "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."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hassan, do you have Books Online? You should. From there:
>> "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."
>> A
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>>
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> > Hassan, do you have Books Online? You should. From there:
> >
> > "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."
> >
> > A
> >
> >
> > "Hassan" <hassan@.hotmail.com> wrote in message
> > news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> >> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> >> unit for it ? Is it in ms(milliseconds) ?
> >>
> >> If its in milliseconds, what does that mean? Can a high duration of CPU
> >> means higher processing power or just longer time to process ? Please
> >> help me understand.
> >>
> >
> >
High CPU utilization
The CPU utilization on one of the servers is always around 50%, sometimes
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cumulated CPU time is 2742463. There were no other jobs running, just sqlserver agent-alert, which is default How to find the top cpu users? Thanks
Hi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
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
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cumulated CPU time is 2742463. There were no other jobs running, just sqlserver agent-alert, which is default How to find the top cpu users? Thanks
Hi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
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
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
High CPU utilization
The CPU utilization on one of the servers is always around 50%, sometimes
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cu
mulated CPU time is 2742463. There were no other jobs running, just sqlserve
r agent-alert, which is default How to find the top cpu users? ThanksHi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
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
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cu
mulated CPU time is 2742463. There were no other jobs running, just sqlserve
r agent-alert, which is default How to find the top cpu users? ThanksHi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
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
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
Subscribe to:
Posts (Atom)