Query to find what current query the procedure is executing.

Question

Query to find what current query the procedure is executing.

adilahmed1989

Right there with Babe

Points: 739
More actions
November 17, 2017 at 3:25 am

#333652

Dear All,
Have a job on a server in which it executes a stored procedure that is present in other linked server; now this job's time -frame is taking more than 2 hours to get completed.
The query text that is calling this specific stored procedure in the linked server is :
EXEC Linkeservername.[Tabelname].[dbo].[SP name] @ScalarVariable name.
Need a query in which i need to find out what query text the above sp is executing that is taking longer time to complete.
Any help on it will be appreciated !
Regards,
Adil

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Do you have any control on LinkedServer and is it a SQL SERVER ? If yes then you can use sp_whoisactive SQL Server Monitoring Stored Procedure by Adam Machanic
Please read the complete documentation for more detail in the link.
Hope it helps.
Note: This needed to be deployed on the LinkedServer

adilahmed1989 Right there with Babe Points: 739 More actions · Answer 2

Yeah we have controls over the linked server, unfortunately we cannot deploy a new procedure since it is a high maintained Production server and have to get through lots of management permissions to deploy the same.

Is there any alternative way to find it or something like a simple T-SQL Query which i can run over the server to find it.

Regards,
Adil

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

adilahmed1989 - Friday, November 17, 2017 5:03 AM
Yeah we have controls over the linked server, unfortunately we cannot deploy a new procedure since it is a high maintained Production server and have to get through lots of management permissions to deploy the same.
Is there any alternative way to find it or something like a simple T-SQL Query which i can run over the server to find it.
Regards,
Adil

Work with the DBAs for the Prod server and ask for their help.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

adilahmed1989 - Friday, November 17, 2017 3:25 AM
Dear All,
Have a job on a server in which it executes a stored procedure that is present in other linked server; now this job's time -frame is taking more than 2 hours to get completed.
The query text that is calling this specific stored procedure in the linked server is :
EXEC Linkeservername.[Tabelname].[dbo].[SP name] @ScalarVariable name.
Need a query in which i need to find out what query text the above sp is executing that is taking longer time to complete.
Any help on it will be appreciated !
Regards,
Adil

You're not actually using a [Tablename] instead of a [Databasename} are you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)