Trying to trace Stored procedure execution from Application

Question

I am a DBA and trying to trace a poor performing stored procedure from SQL Server end. I am tracing sp_completed and rpc_completed. After tracing a general workload I am seeing a lot of calls that uses sp_executesql. I do not have access to the dev at this point to check how they are calling, so my question is whether they are explicitly calling sp_executesql or is it something like ADO.net calls that are converting it to it. I tried the below powershell script and traced it but it is not calling sp_execute

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection #1
$SqlConnection.ConnectionString = "Server=clust1;Database=repl;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand   #2
$SqlCmd.Connection = $SqlConnection   #2
$SqlCmd.CommandText = "test"
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter #3
$SqlAdapter.SelectCommand = $SqlCmd     #3
$SqlAdapter.SelectCommand.CommandType = [System.Data.CommandType]'StoredProcedure' #4
[int]$param=111
$SQLCmd.Parameters.Add("@a",[system.data.SqlDbType]::Int) | out-Null ##5
$SQLCmd.Parameters['@a'].Direction = [system.data.ParameterDirection]::Input ##5
$SQLCmd.Parameters['@a'].value = $param ##5
$SqlCmd.prepare()
$SqlConnection.open()
$a=$SQLCmd.executenonquery()

Answer 1

I executed your powershell code and I did not see sp_executesql call, just like you. But this is expected. Sp_executesql is a stored procedure that is there for folks who do not use stored procedures. Instead of just sending the SELECT (for instance) as pure text, they use sp_executesql to facilitate plan re-use and lessen risk for parameter sniffing.

But you tell ADO that you want to execute your own stored procedure, so why use sp_executesql? Instead it executes your procedure directly as an rpc_completed event.

I removed your ...commandtype = stored procedure... and changed the command text to "SELECT * FROM sys.columns where object_id = @a" and now I indeed see rpc_completed with sp_executesql.