Is this application creating stored procedures on the fly? [duplicate]

Question

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue.

When looking at *sys.dm_exec_sql_text*, I see a lot of queries starting with "CREATE PROCEDURE", of which some are over 1000 lines of code. Is the application creating stored procedures on the fly or is it merely executing these stored procedures? I thought executing a stored procedure is started using "EXEC"?

If the application is constantly (re)creating the procedures, is it likely that this causes performance issues?

Answer 1

sys.dm_exec_sql_text presents the full text of the procedure including it's CREATE PROCEDURE command.

To get the actual query that was executed, you'll need to join to sys.dm_exec_query_stats, where the start/end offset values of the procedure's SQL are stored.

SELECT
    deqs.execution_count,
    deqs.last_execution_time,
    SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext    
FROM sys.dm_exec_query_stats AS deqs 
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest