Tips to Optimize Your SQL Statements - Part 2

  • brian118

    Hall of Fame

    Points: 3506

    Comments posted to this topic are about the item Tips to Optimize Your SQL Statements - Part 2

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Some of it made sense but some I'm going to have to do some research on. Thanks for the opportunity to learn something today.

  • rchantler

    SSCrazy

    Points: 2123

    "You can change the Optimize for Ad hoc Workloads to True.This setting will not have a direct performance impact on SELECTS, however it will improve the efficiency of the plan cache thus relieving the server from excessive memory pressure."

    Good article. Would you mind adding a brief explanation re how changing this setting improves the efficiency of the plan cache? Even what you mean by the efficiency of the plan cache. Thanks.

  • SQLCereal

    SSC-Addicted

    Points: 451

    rchantler - The gist of it, is that only a plan "stub" will be used the first time that a plan is created. This plan stub is smaller in size, which takes up a significantly smaller portion of space in the plan cache, saving space that can be used for other plans or the buffer cache.

    Here is a good explanation from Microsoft - https://msdn.microsoft.com/en-us/cc645587.aspx

    Here is a post that provides more in depth information and some queries for investigating your cache - http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

  • brian118

    Hall of Fame

    Points: 3506

    Thanks for the links. I should have included them in the article.

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • brian118

    Hall of Fame

    Points: 3506

    rchantler (5/7/2015)


    "You can change the Optimize for Ad hoc Workloads to True.This setting will not have a direct performance impact on SELECTS, however it will improve the efficiency of the plan cache thus relieving the server from excessive memory pressure."

    Good article. Would you mind adding a brief explanation re how changing this setting improves the efficiency of the plan cache? Even what you mean by the efficiency of the plan cache. Thanks.

    rchantler, SQLCereal was so kind to post some links. Hope that it answer your queries.

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • brian118

    Hall of Fame

    Points: 3506

    Iwas Bornready (5/7/2015)


    Some of it made sense but some I'm going to have to do some research on. Thanks for the opportunity to learn something today.

    Thanks for your comment. If there is any part which you need further info on, please feel free to ask.

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • george.p

    Right there with Babe

    Points: 753

    Why would setting the recovery mode to simple make bulk inserts faster?

  • brian118

    Hall of Fame

    Points: 3506

    george.p (5/8/2015)


    Why would setting the recovery mode to simple make bulk inserts faster?

    See if this helps - https://technet.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • george.p

    Right there with Babe

    Points: 753

    Ah right I see - I assume it's two things then:

    1. If the log file is filled auto growth would slow things down

    2. Minimal logging physically writes less to the file, leading to less IO.

    I guess it's just one to be a bit wary of - it would only really affect bulk imports, not day to day inserts.

  • Jeff Moden

    SSC Guru

    Points: 996436

    I have to remind everyone that changing a database to the SIMPLE Recovery Model is one of the very worst things you can do if Point-In-Time Backups are in effect and, except for staging/temp user databases, such backups should generally in place.

    Switching a database from FULL to SIMPLE breaks the logchain. It can be restarted only by having a good full backup and taking a DIF or by taking another full backup.

    Minimally logged operations can be had in the BULK LOGGED Recovery Model without breaking the logchain. There is a caveat, though. Any logfile backup that contains a minimally logged operation must either be used in its entirety or not used at all during a restore. Obviously, both can interfere with a Point-in-Time restore.

    I also have to remind everyone that while some of these tricks will sometimes help certain types of code, they usually have very little impact compared to writing the code correctly. Some of these tricks might make a piece of code run 50 to 100% quicker. Writing the code correctly can actually make the code tens to thousands of times quicker and more resource efficient.

    There's no question that these tricks and good hardware are a bit important but if you want real performance, that's in the code itself. Likewise, it doesn't matter how many of these tricks you pull off or how good your hardware is, bad code can still cripple your server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • brian118

    Hall of Fame

    Points: 3506

    Jeff Moden (5/8/2015)


    I have to remind everyone that changing a database to the SIMPLE Recovery Model is one of the very worst things you can do if Point-In-Time Backups are in effect and, except for staging/temp user databases, such backups should generally in place.

    Switching a database from FULL to SIMPLE breaks the logchain. It can be restarted only by having a good full backup and taking a DIF or by taking another full backup.

    Minimally logged operations can be had in the BULK LOGGED Recovery Model without breaking the logchain. There is a caveat, though. Any logfile backup that contains a minimally logged operation must either be used in its entirety or not used at all during a restore. Obviously, both can interfere with a Point-in-Time restore.

    I also have to remind everyone that while some of these tricks will sometimes help certain types of code, they usually have very little impact compared to writing the code correctly. Some of these tricks might make a piece of code run 50 to 100% quicker. Writing the code correctly can actually make the code tens to thousands of times quicker and more resource efficient.

    There's no question that these tricks and good hardware are a bit important but if you want real performance, that's in the code itself. Likewise, it doesn't matter how many of these tricks you pull off or how good your hardware is, bad code can still cripple your server.

    Hi Jeff

    Thanks for your comments.

    I fully agree with you, that the database recovery model should only be considered to be changed IF the points you mentioned are OK to live without such as Point-In-Time Backups or if replication is needed etc. If fact that's why I specifically mentioned that these tips are more targeted to a DWH environment were normally point-in-time backups are not needed (at least from my experience).

    Also, no trick, state of the art hardware or SQL Server option can replace badly written code as you mentioned. I just wanted to point out that some options may help to improve performance in specific loading conditions. There is no magic wand to correct a badly written query!

    Regards

    Brian

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • Jeff Moden

    SSC Guru

    Points: 996436

    Understood and I also appreciate the fact that you brought up reworking queries in your article.

    I brought up the Recovery Model switch specifically because of the following post...

    brian118 (5/8/2015)


    george.p (5/8/2015)


    Why would setting the recovery mode to simple make bulk inserts faster?

    See if this helps - https://technet.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

    ... and that article takes you to a whole lot more reading that some people may choose to ignore, which could lead to a whole lot of trouble for them. I don't want people to think that switching from FULL to SIMPLE recovery is any kind of possible performance improvement that should actually be taken nor do I want them to think that a change from FULL to BULK LOGGED isn't without it's own hazards.

    I wanted to emphasize to any casual readers just how badly one can screw things up by making recovery model changes without knowing some of the possible major negative impacts it will have on the safety of the data especially for those that don't understand the differences you might find in a data warehouse compared to "live" data that is much more valuable. I've seen too many times where folks don't understand why their log files are useless and that the best they can do in a crunch is just a restore from a backup they took a week ago.

    Shifting gears a bit... I also consider the data in a DW to be as valuable as "live" data because a lot of people make the mistake of using a DW as a historical repository, not to mention the time that it would take to rebuild a day's worth of entries if the PIT backups failed on the DW and a restore was needed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • dmfd

    SSC Enthusiast

    Points: 164

    Thank you, Brian. More good stuff for me to add to my bucket of knowledge.

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

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