February 24, 2017 at 12:42 pm
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?
February 24, 2017 at 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.
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
February 24, 2017 at 1:43 pm
SQLRNNR - Friday, February 24, 2017 1:17 PMThat 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
February 24, 2017 at 6:06 pm
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 < @P6SELECT 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply