SQL Server Performance

sp_executesql SQL2005 Express

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by thakor, May 30, 2011.

  1. thakor New Member

    Hello,

    I was forced to use dynamic queries in our project. One of our databases is SQL2005 EXPRESS and I have the following problem:

    Our application has problems to execute stored procedure - it hangs up. The procedure calls sp_executesql in its body. I've examined the code and found that the SQL server cannot execute the sp_executesql command - it hangs up. The query was quite simple. I can execute any other query (not using sp_executesql) without problems.

    Finally, I've connected with SQL Server Profiler to the database and run the query. It has been executed quickly and without any problem.

    I suppose there is some service needed for sp_executesql that was started by the profiler. Am I right? Does anybody know what the service is?

    Tom
  2. Jahanzaib Member

    execute this query when it hangs

    Select st.text,sp.* from sys.sysprocesses sp
    cross apply sys.dm_exec_sql_text(sp.sql_handle) st
    order by sp.cpu desc

Share This Page