2

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?

  • 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
| improve this answer | |

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