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 4 (of 4 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: 246977 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004401 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)