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.
> >

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
Tom
You can use DBCC PAGE. Google and you will find how to use it. It will return object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:

> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
TomYou can use DBCC PAGE. Google and you will find how to use it. It will return object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
TomYou can use DBCC PAGE. Google and you will find how to use it. It will retur
n object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...seagreen">
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:

> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>