Tips to Optimize Your SQL Statements - Part 2

Question

Tips to Optimize Your SQL Statements - Part 2

brian118

Hall of Fame

Points: 3506
More actions
May 7, 2015 at 12:16 am

#300434

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 -

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

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: 2128 More actions · Answer 2

"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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 SSC Eights! Points: 833 More actions · Answer 7

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

brian118 Hall of Fame Points: 3506 More actions · Answer 8

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 SSC Eights! Points: 833 More actions · Answer 9

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: 1004321 More actions · Answer 10

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)

brian118 Hall of Fame Points: 3506 More actions · Answer 11

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: 1004321 More actions · Answer 12

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)

dmfd SSC Enthusiast Points: 164 More actions · Answer 13

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