Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Profiler questions Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 1:02 PM
Points: 5, Visits: 10
I am new to sql profiler, below is the output of a sql profiler trace and I have questions listed for each event captured. Looking for clarity. Thanks in advance

Dean-O

When this one executes, does it pass the value of @p1 to the next RPC?
If not, what does it do with it?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=54
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1


What does it do with @p1, @p3, @p4?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=56
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4


What do these last three do?
They run select statements, but what for?
What do they do with the data they select?

Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'
---------------------------------------------------------------------------------------
Event Class = SQL: BatchCompleted
TextData = INSERT INTO ngn_ids( id_name, id_value, in_update ) VALUES ( 'Histories_MedSurg', '557D78F4-F830-47C6-BE87-23F567F2FC3C', 1 )
---------------------------------------------------------------------------------------
Event Class = SQL: BatchCompleted
TextData = DELETE FROM ngn_ids WHERE id_name = 'Histories_MedSurg' AND id_value = '557D78F4-F830-47C6-BE87-23F567F2FC3C'


Why does it show Trace Start here?
Shouldn't this be at the start of the trace?

Event Class = Trace Start

---------------------------------------------------------------------------------------
Event Class = Trace Stop
Post #1457904
Posted Wednesday, May 29, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Applications using OLE DB/ODBC/ADO.NET can use prepared sql statements and that's what sp_prepexec is essentially. The output parameter is the handle for the prepared sql statement so that if the application runs the same statement many times it can just use the handle when calling sp_execute although usually you will see sp_prepare followed by sp_execute, not sp_prepexec.

For the second one:

sp_prepexecrpc is used by ODBC to call a stored procedure. I'm guessing the 3 variables you mention are additional parameters required for the stored procedure, ng_update_person.

The next 3 questions are a bit harder to answer especially since only 1 is a select and the other 2 are an insert and a delete. I can't tell you what the application does with the results of the select statement. I assume it displays the data in some form to the user.

The last questions about why trace start shows later would probably be because the results are not ordered by time, but by event class.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1457910
Posted Wednesday, May 29, 2013 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 1:02 PM
Points: 5, Visits: 10
Thanks for pointing out I had these events sorted by type of event instead of event start time. I sorted them by event start time, so they make a little more sense... but still have some questions...

Event Class = Trace Start
---------------------------------------------------------------------------------------

What does this do?
Event Class = RPC: Completed
TextData = exec sp_unprepare 26
---------------------------------------------------------------------------------------

Does select @p1 actually execute the SQL statement or does it get the returned value of the sql statement?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1

---------------------------------------------


I don't understand the select @p1, @p3, @p4

It has executed the SQL, so @p1 stores the result of the SQL, right?

is select @p1, @p3, @p4 actually a select statement? if not what is it doing in that select line?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4

---------------------------------------------

What does this do?
Runs a select statement, but what for?
What does it do with the data selected?

Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'

_____________________________________
Post #1457929
Posted Wednesday, May 29, 2013 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
rochesterd (5/29/2013)
Thanks for pointing out I had these events sorted by type of event instead of event start time. I sorted them by event start time, so they make a little more sense... but still have some questions...

Event Class = Trace Start
---------------------------------------------------------------------------------------

What does this do?
Event Class = RPC: Completed
TextData = exec sp_unprepare 26
---------------------------------------------------------------------------------------


This removes the prepared sql statement from the SQL Server. You should see one of these for every sp_prepexec or sp_prepare/sp_execute combination, unless you end the trace before a prepared sql statement is no longer needed.


Does select @p1 actually execute the SQL statement or does it get the returned value of the sql statement?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1

---------------------------------------------


Select @p1 is just returning the value to the application so the application can re-use it with another sp_execute or with sp_unprepare


I don't understand the select @p1, @p3, @p4

It has executed the SQL, so @p1 stores the result of the SQL, right?

is select @p1, @p3, @p4 actually a select statement? if not what is it doing in that select line?

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4

---------------------------------------------



@p1 is the handle for the sql statement, @p3 and @p4 are output parameters for the call and are returned so the application can use them if needed.



What does this do?
Runs a select statement, but what for?
What does it do with the data selected?

Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'

_____________________________________


This is a select statement being run by an application so that a user can get data about the person specified in the where clause. What the application/user does with the data isn't something anyone on the forum can know because we don't have the application. If you want to know what the application does with the data you need to get a copy of the application, use it, and see what happens in the application when this select is run, work with a person who uses the application regularly to find out what is done with the data, or talk to the vendor/app devs.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1457940
Posted Wednesday, May 29, 2013 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 1:02 PM
Points: 5, Visits: 10
Excellent, I think I am getting the sql profiler speak...

just for clarity...

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1

You wrote: Select @p1 is just returning the value to the application so the application can re-use it with another sp_execute or with sp_unprepare

So since this was a select statement, @p1 is just a handle to the sql select statement, not the value of result set of in_update values? is the result set of values just sent automatically back to the calling application?

-------------------------------------------------------------------

Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4


You wrote:@p1 is the handle for the sql statement, @p3 and @p4 are output parameters for the call and are returned so the application can use them if needed.

So @p1 is the handle again, not the returned value of zero or rows updated, right?
And is @p3 zero
and is @p4 'ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
which are both sent back to the calling application?
Post #1457943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse