Post reply

Adhoc Vs Prepared/Procs - SQL Queries

  • February 25, 2014 at 7:28 am

    #283999

    Hi All,

    I'm doing some performance evaluation at my company. I've been running a few different DMV's, one of which is the following,

    SELECT size_in_bytes

    , cacheobjtype

    , objtype

    , usecounts

    , TEXT

    FROM SYS.dm_exec_cached_plans CP

    CROSS APPLY SYS.dm_exec_sql_text(cp.plan_handle)

    WHERE objtype = 'Adhoc'

    ORDER BY usecounts DESC

    I noticed thousands of these results being returned. One query for instance is a select statement looking for a single value, and that query has run about 700k times in a relatively short period. Also, almost all of these queries are looking for primary keys. I believe this could be causing some significant IO issues. In practice, I believe the best way to get around this would be to create and call stored procedures for extremely common sql queries, which would decrease the number of compilations. Anyone have any thoughts? I'm afraid I'm going to run into a lot of pushback from development about changing up a lot of their code logic.

    Thanks

  • February 25, 2014 at 7:49 am

    #1691891

    Creating a procedure versus TSQL execution isn't going to buy you any performance gain as the execution plan for it will be the same. But I am curious, have you set the Optimize for Ad Hoc workloads at the server level? Enabling this option can assist in performance gains by relieving memory pressure by not storing the single time used compiled plans.

    Example:sp_CONFIGURE 'show advanced options',1

    RECONFIGURE

    GO

    sp_CONFIGURE ‘optimize for ad hoc workloads’,1

    RECONFIGURE

    GO

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • February 25, 2014 at 7:54 am

    #1691896

    Doesn't the execution of thousands of ad-hoc queries bloat the procedure cache? I though that would cause some performance degradation.

  • February 25, 2014 at 8:06 am

    #1691905

    Yeah, but it'll hit CPU and memory, not IO.

    The question is, are all the queries the same, but varying by some parameter value? If so, then optimize for ad hoc can help, but you'd be better off with parameterized queries (stored procedures being one method). If these things are just all over the map, then I'd go with optimize for ad hoc.

    "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 25, 2014 at 8:11 am

    #1691918

    JoshDBGuy (2/25/2014)

    Doesn't the execution of thousands of ad-hoc queries bloat the procedure cache? I though that would cause some performance degradation.

    Absolutely.

    Even (IIRC) with the optimize for Ad-Hoc queries sticking plaster on, you can still get cache bloat. For example

    <SomeQuery> WHERE KeyValue = '1'

    it'll just store a stub - won't store a plan (obviously it still has to generate one)

    <SomeQuery> WHERE KeyValue = '2'

    Will store the plan

    <SomeQuery> WHERE KeyValue = '3'

    re-uses the plan

    <SomeQuery> WHERE KeyValue = '11'

    it'll just store a stub - won't store a plan

    also

    <SomeQuery> WHERE Keyvalue = '11'

    Will store a stub - won't store a plan (query text is different)

    <SomeQuery> WHERE KeyValue = '12'

    Will store a plan

    <SomeQuery> WHERE Keyvalue = '12'

    Will store a plan

    If you have a parameterised stored proc, one (or two) plan compilations, one stored plan.

    Microsoft wave worked hard at improving the sticking plasters for this, but it's still not as efficient as doing it properly.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • February 25, 2014 at 8:14 am

    #1691919

    Grant Fritchey (2/25/2014)

    Yeah, but it'll hit CPU and memory, not IO.

    The question is, are all the queries the same, but varying by some parameter value? If so, then optimize for ad hoc can help, but you'd be better off with parameterized queries (stored procedures being one method). If these things are just all over the map, then I'd go with optimize for ad hoc.

    Ah ok, misunderstanding on my end. But yes, most statements look something like this,

    select * from where [field] = 1

    select * from where [field] = 2

    select * from where [field] = 3

    /* ETC.*/

    Of the example listed above, I see about 30k of such ad-hoc queries.

  • February 25, 2014 at 8:18 am

    #1691924

    Yep. Those are the kind that I'd replace with some kind of parameterized query.

    If you can't for some reason, then optimize for ad hoc, absolutely, and, possibly, forced parameterization. That will make SQL Server apply parameters internally to more queries, which can help in the situation you're in (assuming you can't create procedures).

    "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 25, 2014 at 8:23 am

    #1691927

    Great thanks.

    Unfortunately, the issue I run into is a request to provide numbers for performance enhancement. i.e. "If you do this, how much faster will our system be?" That's very difficult to pinpoint, I just know it won't use as much memory/cpu. So forced parameterization and optimize for ad-hoc may be my only option.

  • February 25, 2014 at 8:36 am

    #1691929

    When you say "700k times in a relatively short period", how many selects per second do you estimate they are running? SQL is fast, but I can kill a box with an infinite loop error.

  • February 25, 2014 at 8:41 am

    #1691931

    Grant Fritchey (2/25/2014)

    Yep. Those are the kind that I'd replace with some kind of parameterized query.

    If you can't for some reason, then optimize for ad hoc, absolutely, and, possibly, forced parameterization. That will make SQL Server apply parameters internally to more queries, which can help in the situation you're in (assuming you can't create procedures).

    Grant, I am curious, how would replacing this with a parameterized query help in this case? Considering the parameter will always be the same data type? I mean, select * from where [field] = 1 is going to yield the same execution plan as

    select * from where [field] = @Parameter

    Isn't it? I'm asking because I am curious to know as well 🙂

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • February 25, 2014 at 9:52 am

    #1691967

    MyDoggieJessie (2/25/2014)

    Grant Fritchey (2/25/2014)

    Yep. Those are the kind that I'd replace with some kind of parameterized query.

    If you can't for some reason, then optimize for ad hoc, absolutely, and, possibly, forced parameterization. That will make SQL Server apply parameters internally to more queries, which can help in the situation you're in (assuming you can't create procedures).

    Grant, I am curious, how would replacing this with a parameterized query help in this case? Considering the parameter will always be the same data type? I mean, select * from where [field] = 1 is going to yield the same execution plan as

    select * from where [field] = @Parameter

    Isn't it? I'm asking because I am curious to know as well 🙂

    Yep. You're right. It's going to result in the same execution plan.

    Once.

    He has thousands of execution plans right now for the same query. The goal is to parameterize the query so that you get one that is reused vs. thousands that are all the same.

    "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 25, 2014 at 9:54 am

    #1691973

    Gotcha. I missed the obvious 😉

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • February 25, 2014 at 10:37 am

    #1691996

    Thanks again. Some great information.

Viewing 13 posts - 1 through 13 (of 13 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy