Profiler

Question

Post reply

Profiler

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
October 31, 2002 at 11:59 am

#64612

sql server 2000 profiler example:

EventClass:

SQL:BatchCompleted

Duration:

47310

CPU:

37829

Reads:

230

Writes:

0

What is the CPU part indicating here. That this sql batch took 37 seconds on a CPU's time?

If so, wouldn't it be good to get the % of CPU utilization during this time? I don't see a data column for this in profiler.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Antares686

SSC Guru

Points: 125444

November 1, 2002 at 5:26 am

#440038

Yes 37 seconds of CPU time used. And yes it would be nice but for now the best you can get is thru PerfMon or Task Manager on SQL as a whole, no break out of each query.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

Simon Sabin

SSCrazy Eights

Points: 8142

November 1, 2002 at 2:36 pm

#440091

Can you not divide the cpu time by actual duration by the number of processors? This should give you the number you want

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I got to thinking about this and it doesn't really matter as %CPU utilization is a process relative thing. You always have threads on the CPU of which many are waiting. When two item need to be processed the CPU utilization will track how much of the CPU comes alive total and then give you a percent of the time the threads are active for a particular process, consuming parts of the CPU. Even if you had a percentage, unless it consumed 100% of the utilization itself then it is unimportant in a relative schema. However, if during the process the server bombed out on processing you can bet it was at 100% and that query needs some major fixing.

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 4

BOL:

Threads allow complex applications to make more effective use of a CPU even on computers with a single CPU. With one CPU, only one thread can execute at a time. If one thread executes a long running operation that does not use the CPU, such as a disk read or write, another one of the threads can execute until the first operation completes. Being able to execute threads while other threads are waiting for an operation to complete allows the application to maximize its use of the CPU. This is especially true for multiuser, disk I/O intensive applications such as a database server.

Computers with multiple microprocessors, or CPUs can execute one thread per CPU at the same time. If a computer has eight CPUs, it can concurrently execute eight threads.

---

Antares686,

I agree that the %CPU utilization is a process thing, but there must be a way to find the % CPU Utilization for a specific thread. Then one can calculate the %increase in CPU utilization due to a specific query execution. This would be very useful.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

It would never be actual only average and is relative to other threads on the server running. If was only thread it would one thing, but when other threads are involved the context switch between threads will change the CPU utilization. Also, variances in other system activity will affect the average results (HD activity, nic traffic, completion ports firing, data in memory paged or not). So there really would never be a good way to tell from the value it would provide. However, when you run a query if the servers memory utilization jumps a significant amount (using perfmon or taskman) and remains that way for the duration of the query, dropping when down then it does show that query has a high impact to resources. Usually, I watch these when server hits 57% or better and sits (usually notice slow downs in processing at that point) and wait until drops off and see what query completed at that point.

Now you are right in saying it would be somewhat helpfull but the data is still not a good measurement of a single query especially if it spit out small values. The duration is nice in that it is definable as actuall and not average.

If you feel that CPU Utilization would be a big bennifit then make the suggest on the MS product site, they may also have other reasons why it doesn't work that I haven't even thought of.

Sorry, if this doesn't make sense let me know, I am not best at explaining the threading model and utilization. But I have a friend who is good and maybe I can get himn to jump in (he is a bit scary thou when he talks).

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 6

This is the way I see it,

1. A Client connection to SQL Server

2. SQL Server assigns one of the available threads it has assuming there are free ones(MAX WORKER THREADS)

3. Client Batch Request

4. Results returned

5. Client disconnected

6. Above thread freed and available for re-scheduling.

- Should be able to get stats on the one specific thread.

Now, if you look at the performance monitor:

Object: Process

Counter: Thread Count. (Select sqlserver)

- This will be the total number of threads that SQL Server uses in total. Good but should be able to drill down on these threads.

Now assuming no context switching and no disk queuing, no CPU queuing, lots of memory for data & procedure cache, etc.

(No contention for resources at all.)

- If there was a Thread counter with associated CPU Utilization, then is would be possible to see the CPU Utilization for a specific query. This would be great to see that number.

This 'Thread CPU Utilization' may already be possible but I have not seen it anywhere.