Post reply

dynamic sql

  • February 8, 2011 at 11:29 am

    #244908

    I'm a dba at a shop that uses alot of embedded dynamic sql and I concerned about whether the dynamic sql will use a cached query plan. I'm an old DB2 Mainframe DBA and at the time, using dynamic sql was a little taboo because it would have to created the execution plan on the fly everytime. I believe in sql server that's not the case but wanted some opinions about that. And if you have any helpful recommendations on tuning the application to make dynamic sql perform better.

    Thanks in advance

  • February 8, 2011 at 11:58 am

    #1283942

    ericwenger1 (2/8/2011)

    I'm a dba at a shop that uses alot of embedded dynamic sql and I concerned about whether the dynamic sql will use a cached query plan. I'm an old DB2 Mainframe DBA and at the time, using dynamic sql was a little taboo because it would have to created the execution plan on the fly everytime. I believe in sql server that's not the case but wanted some opinions about that. And if you have any helpful recommendations on tuning the application to make dynamic sql perform better.

    Thanks in advance

    It depends on how you build and execute the dynamic sql. There are basically two ways of doing it. You can run EXEC 'somestring'. This is problematic exactly as you describe because it will generate multiple execution plans over time (depending on the complexity of the queries of course). sp_executesql allows you to build a dynamic statement but provide it with parameters, a parameterized statement, which, effectively, is the same as a stored procedure. That's the better approach.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 8, 2011 at 12:03 pm

    #1283951

    Grant Fritchey (2/8/2011)

    ericwenger1 (2/8/2011)

    I'm a dba at a shop that uses alot of embedded dynamic sql and I concerned about whether the dynamic sql will use a cached query plan. I'm an old DB2 Mainframe DBA and at the time, using dynamic sql was a little taboo because it would have to created the execution plan on the fly everytime. I believe in sql server that's not the case but wanted some opinions about that. And if you have any helpful recommendations on tuning the application to make dynamic sql perform better.

    Thanks in advance

    It depends on how you build and execute the dynamic sql. There are basically two ways of doing it. You can run EXEC 'somestring'. This is problematic exactly as you describe because it will generate multiple execution plans over time (depending on the complexity of the queries of course). sp_executesql allows you to build a dynamic statement but provide it with parameters, a parameterized statement, which, effectively, is the same as a stored procedure. That's the better approach.

    Just to add to this point. I once traced a system where 90% of the EXECUTION time was compile time. Now mind you I had optimized the code to death but we were still stuck in the dynamic non parameritized queries. The contract was terminated before I could change that.

    Now that would have been a finely tuned web site if I could have finished THAT tweak.

  • February 8, 2011 at 12:47 pm

    #1283999

    Thanks and yes they are using sp_executesql, so that's a good think

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply