Find Stored Proc name from ObjectId in Profiler

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  February 24, 2017 at 12:42 pm

  #335494

  
  I have SQL Diagnostic Manager telling me that a database is experiencing 4-6 minutes of blocking that involves the below UPDATE statement blocking the SELECT.  This is a third party app so I didn't write this and can't change it on my own so no need to pick it apart.  My question is this.  How can I figure out what stored proc is called that contains these statements?  

  update AGENTSTATUS set LASTRUNGMT = @P0 , MAIL = 0, VERSION_BUILD = @P1 , MACHINE_NAME = @P2 , SERVERGROUP_IDX = @P3 , LASTRUN_DATA = @P4 where IDX = CONVERT(CHAR(32), @P5 ) and LASTRUNGMT < @P6 

  SELECT IDX, LASTRUNGMT from AGENTSTATUS WHERE AGENTTYPE = @P0 AND AGENTNAME = @P1 

  If I run the below then I get no results.

  SELECT OBJECT_NAME(object_id)
  FROM sys.sql_modules
  WHERE definition LIKE '%SELECT IDX, LastRunGMT%'

    SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type = 'P' AND sm.definition LIKE '%UPDATE AGENTSTATUS%'
ORDER BY o.type;
GO
'

  Can I assume these statements are not from a stored procedure since the above returns no results?  I assumed they were because they show up in Profiler from EventClass SP:StmtStarting and SP:StmtCompleted.  The objectId returned in Profiler for the UPDATE statement is 550483026.  For the SELECT the ObjectID is 496926629.  Can I leverage that at all?
  

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 24, 2017 at 1:17 pm

  #1930264

  That may be a safe assumption. Before accepting the assumption that it is not a proc, rerun your query against sys.all_sql_modules.

  You could also query sys.objects for the objectid in question. You may need to query more than just the one database that is related to the application.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  February 24, 2017 at 1:43 pm

  #1930272

  SQLRNNR - Friday, February 24, 2017 1:17 PM

  That may be a safe assumption. Before accepting the assumption that it is not a proc, rerun your query against sys.all_sql_modules.

  You could also query sys.objects for the objectid in question. You may need to query more than just the one database that is related to the application.

  So if I run the below I still get nothing.  I can see if there are other databases used for this app.  Perhaps the App Server is calling a proc from a different database that queries this one.  I suppose these statements could just be inline SQL too.

    SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.all_sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id

  WHERE O.object_Id IN (

  496926629,
550483026
)

  --WHERE o.type = 'P' AND sm.definition LIKE '%UPDATE AGENTSTATUS%'
ORDER BY o.type;
GO

• Eddie Wuerch

  SSChampion

  Points: 12540

  More actions

  February 24, 2017 at 6:06 pm

  #1930293

  lmarkum - Friday, February 24, 2017 12:42 PM

  [...]
  update AGENTSTATUS set LASTRUNGMT = @P0 , MAIL = 0, VERSION_BUILD = @P1 , MACHINE_NAME = @P2 , SERVERGROUP_IDX = @P3 , LASTRUN_DATA = @P4 where IDX = CONVERT(CHAR(32), @P5 ) and LASTRUNGMT < @P6 

  SELECT IDX, LASTRUNGMT from AGENTSTATUS WHERE AGENTTYPE = @P0 AND AGENTNAME = @P1 
  [...]
  Can I assume these statements are not from a stored procedure since the above returns no results?  I assumed they were because they show up in Profiler from EventClass SP:StmtStarting and SP:StmtCompleted.  The objectId returned in Profiler for the UPDATE statement is 550483026.  For the SELECT the ObjectID is 496926629.  Can I leverage that at all?
  

  The parameters @P0, @P1,@P2, @P3, and @P4 would indicate that the statement has been auto-parameterized. This only occurs on ad-hoc batches.

  The object IDs can be used to get the procedures:
  USE MyDatabase; -- replace with your db name
GO
SELECT * FROM sys.objects WHERE object_id IN (550483026, 496926629);

  -Eddie

  Eddie Wuerch
  MCM: SQL