November 28, 2006 at 4:01 am
Hi,
I am using SQL Server 200 (sp4) on Windows server 2003, can someone tells me what SP:completed & SQL:StmtCompleted events are used for?
When I am profiling SQL server during load test I only get SP:Starting but no SP:completed event if I know that the procedure was executed successfully (insert were done)
November 28, 2006 at 6:01 am
Are you using any filters?
November 29, 2006 at 3:13 am
I am filtering only on databaseID, when I run the following query
SELECT
left(cast(textdata AS varchar(4000)),charindex(' ',cast(textdata AS varchar(4000))) )AS ProcStoc,
SUM(case WHEN EventClass IN (42) THEN 1 ELSE 0 END ) AS ' SP:Starting',
SUM(case WHEN EventClass IN (43) THEN 1 ELSE 0 END ) AS ' SP:Completed'
FROM
dbo.LOAD_TEST_V_28_11_06
WHERE
TextData LIKE 'sp_[_]%'
GROUP
BY left(cast(textdata AS varchar(4000)),charindex(' ',cast(textdata AS varchar(4000))) )
ORDER
BY 2
I get
| ProcStoc | SP:Starting | SP:Completed |
| spi_tCommand |
November 29, 2006 at 10:56 am
I am assuming that you are using either a Profiler or a server trace which is writing the data to the the database table called: "dbo.LOAD_TEST_V_28_11_06 "
In either case, when creating Profiler Trace, under Events tab, choose TSQL and then add SQL:StmtCompleted.
Once trace is started, with this event chosen, you should get SQL:StmtCompleted events.
November 30, 2006 at 8:45 am
You are correct I am using a server trace and load data into LOAD_TEST_V_28_11_06 , what I cannot explain is why I have
8 sp:started and only 3 sp:completed when calling spi_tCommand stored procedure.
This procedure insert records in a table and when I check the table 8 new records have been added so I should also have 8 sp:completed events in my trace, don't you think?
December 1, 2006 at 8:14 am
That is true. As far as I understand you should.
1. Do you have another dataset to check this (another run of profiler trace)? If so, check that and see if pattern is the same.
2.
- Turn on profiler trace - don't log the output - just view it as it goes
- Execute one of the faulty sps and see if the result is the same
Also, do you issue a COMMIT statement after you perform an INSERT?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply