difference between a SQL:BatchCompleted and SQL:StmtCompleted eve

When i execute a SQL Statement and trace it through the profiler.. why there
is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted for
a single SQL Statement?
For example
the statement
select * from office
SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13I may be way off, but there can be more than one statement per batch...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:A24FAD9F-138D-42A1-9F9D-97D96F7EBE6A@.microsoft.com...
> When i execute a SQL Statement and trace it through the profiler.. why
> there
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted
> for
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>|||Mahesh wrote:
> When i execute a SQL Statement and trace it through the profiler..
> why there is a differnce in counters for SQL:BatchCompleted and
> SQL:StmtCompleted for a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
SQL:BatchCompleted is for the entire batch, which could be more than one
statement. SQL:StmtCompleted is for a single statement.
The duration counter in SQL Trace is not super accurate. You can
download a more accurate timer from SQLDev.net (for testing). However,
there is more to do to complete a batch than a single statement, even if
what the end user sees is pretty much the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You expect a batch to be larger because it is a collection of statements
submitted together.
Your SQL:StmtCompleted may only be the call to a stored proc (and if you are
not capturing SP:StmCompleted events you will not capture the granular level
of calls within the proc). The SQL:BatchCompleted is an event for the who
batch.
HTH,
John Scragg
"Mahesh" wrote:

> When i execute a SQL Statement and trace it through the profiler.. why the
re
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted f
or
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>|||My guess is that batch contains some degree of compilation time, as compilat
ion is made at the batch
level, not the statement level. I'd be surprised if SQL Server could keep tr
ack of compile time per
statement in a batch. Of course, batch can include other things as well, lik
e meta-data access etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:A24FAD9F-138D-42A1-9F9D-97D96F7EBE6A@.microsoft.com...
> When i execute a SQL Statement and trace it through the profiler.. why the
re
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted f
or
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>

Determining the cause of tempdb growth?

I'm trying to determine the cause of tempdb to grow from ~ 30MB to well over
40GB. I have a SQL Profiler trace that was run at the time of this growth
but I don't see any particular SQL statements that consumed a lot of CPU or
performed a lot of reads (maybe the maximum was 50000 reads).
Any ideas as to what I could do to identify this problem?
Thanks in advance.Creation of temp tables and ORDER BY statements can cause tempdb growth. = Tempdb may also be used by other (SQL Server internal) query =optimization processes.
With that said, 40GB does sound rather large. How large are your databases?
What type of operations were being performed during the growth?
-- Keith
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message =news:OPLVdaYmDHA.1800@.TK2MSFTNGP10.phx.gbl...
> I'm trying to determine the cause of tempdb to grow from ~ 30MB to =well over
> 40GB. I have a SQL Profiler trace that was run at the time of this =growth
> but I don't see any particular SQL statements that consumed a lot of =CPU or
> performed a lot of reads (maybe the maximum was 50000 reads).
> > Any ideas as to what I could do to identify this problem?
> > Thanks in advance.
> >