2

This question already has an answer here:

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?

marked as duplicate by Jon Seigel, Aaron Bertrand Apr 9 '14 at 17:32

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Run a brief server side trace and you will find it out if it is creating or just running it. – Kin Shah Apr 9 '14 at 15:43
2

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.