SQL Server Identifying Connections that are utilising SQL from an application

Question

Is it possible to identify connections in SQL Server 2014 that are utilising SQL that is passed from an application as opposed to calling an SP ?

Answer 1

So you want to differentiate between the app calling stored procedures vs sending T-SQL directly? Sounds like an Extended Event trace would so. Assuming the programmers call these procedures correctly from the API (and not just send a text string with EXEC procname in it).

If you capture sqlserver.sql_batch_completed and sqlserver.rpc_completed I think you will cover the submissions into SQL Server.

When an app doesn't call a proc you should either see sql_batch_completed or rpc_completed. For the later, you can check out the object_name column and if it is sp_executesql, then you have the "not calling stored procedure" case.

When it does call a proc, you will have (hopefully, if they do it right) rpc_completed with something else in the object_name column.

Depending on how sophisticated you want to be, you can play around with event_counter and the histogram targets. Or even store the trace data in a table and query that table.