SQL Server Performance

exec Vs sp_executesql Performance

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by apat, Oct 22, 2009.

  1. apat New Member

    I am finding weared behaviour on different servers I work with. All of them have same SP being called from front end to fetch data. SP is using sp_exeutesql and it takes only few seconds to be executed on two of the servers and takes 15 minutes on one server. If I change sp_executesql to exec on that server (where it takes 15 min) then it is faster. What could be the possible reasons and solutions for this? appriciate any help...
  2. Adriaan New Member

    Strange, if anything you would expect it to be slower with EXEC only. Did you try a third time with sp_ExecuteSQL?
    If the database was recently added to the "slow" server, perhaps there was no relevant execution plan in buffer. After the first execution, an execution plan was available when you ran the statement with EXEC, which would then perform better.
    Are you connecting to the different servers with the same credentials?
    Are the servers in the same domain?
    Does the query involve any remote servers?
    Does the query include object names without an owner prefix?
  3. FrankKalis Moderator

    I think you need to provide more information, such as execution plans. hardware config, etc..
  4. satya Moderator

    The performance cannot be guaranteed when the statements are executed using SP_EXECUTESQL.
    See what is referred in BOL:
    When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. SQL Server compiles the Transact-SQL statement or statements in the string into an execution plan that is separate from the execution plan of the batch that contained the sp_executesql or the EXECUTE statement. The following rules apply for self-contained batches:
    • The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are not resolved until they are executed.

      • The Transact-SQL statements in the executed string do not have access to any variables declared in the batch that contains sp_executesql or the EXECUTE statement. The batch that contains sp_executesql or the EXECUTE statement does not have access to variables or local cursors defined in the executed string.

        • If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running.

Share This Page