Blog

Are you frustrated by third party applications that you can’t change, but you have to support? Tired of beating your head against the wall when your users complain about things you can’t fix? In this 30-minute session, Brent Ozar will show you his favorite tricks to get the most performance without losing support. He’ll show you how to interact with vendors and get what you want – without getting heartburn:

Like that video? We’ve got half a dozen more scheduled for upcoming Tuesday lunches. Click the boxes you want and sign up for free.

Are you getting the most out of your TSQL queries? Most people still write queries like they’re using SQL Server 2000, but there’s lots of new functionality that can make your TSQL simpler to read, write, and support.

Kendra Little introduces you to five cool features that you should think about using to improve your SQL Server queries (along with the time the demo on that feature starts):
1) The glory of the OUTPUT clause – 4:53
2) Over and Partition By – 12:23
3) The APPLY operator for reusable computations – 16:56
4) EXCEPT and INTERSECT – 22:03
5) The Magic of indexed computed columns – 24:24

Looking for the scripts from the webcast? Scroll on down to below the video for the download link.

Scripts and Links

Need to check your database compatibility level? Our sp_Blitz script does that and much more.

Download demo scripts from the webcast here: 5-TSQL-Features-Youre-Missing-Out-On-Demo-Scripts.zip

Questions from the Webcast

Here are some great questions we got from folks during the webcast which I didn’t have time to answer live, but caught up with after the show.

From John on subqueries

Question: Is the difference with using a subquery vs Over/Partion is the subquery is processed for every row and the over isn’t?

Answer: SQL Server is too tricksy to make things so simple to remember. Subqueries won’t necessarily get processed repeatedly for every row as a general rule. The optimizer will do its darndest to rewrite things as efficiently as it can in the time it is allowed for optimization. The behavior depends on the query and the version of SQL Server.

Special things are definitely done when you bring in OVER, though. If you’d like to nerd out on how this works, good news! Paul White wrote a detailed post on this very topic. (Warning: brain melting may ensue.)

From Riley on CROSS APPLY vs CTEs

Question: How does the CROSS APPLY performance compare to using a CTE, where the Subtotal Plus Tax is computed in the CTE and then added to in the final SELECT? Do they generally result in the same plan?

Answer: As as general rule, I don’t expect CROSS APPLY and CTEs to get the same plans– I’ve seen many cases where they do not.

In this specific case for making computations reusable, the plans are very different. The APPLY operator lets you put in the calculation without a FROM clause or a correlation– it uses the same Compute Scalar operator that the original version of the query used. If you put the calculation in a CTE you have to essentially make it a subquery, and that’s not necessarily something SQL Server’s going to be able to normalize out. (I did a quick test of this particular scenario just to see if it was more clever than I thought in 2012, and it gets a much more expensive plan.)

From Donald on computed columns

Question: I thought Computed columns were a bad idea ?

Answer: Not at all! Like anything else, you can do something terrible with them, but they can also be absolutely brilliant by simplifying code or improving performance. Persisting  and sometimes indexing the computed column can be very powerful when it’s a frequently read calculation.

From Grace on portability/ANSI standard

Question: Are these statements compatible with ANSI SQL or are they specific to MS T-SQL?

Answer: All my demos and comments here are specific to TSQL, specifically the Microsoft SQL Server implementation. Some of these are likely in the ANSI spec, but SQL Server very frequently doesn’t adhere to ANSI SQL specs– it likes to do it’s own thang. So I wouldn’t count on it!

From Mike on mikes

Question: Hey guys, what microphone setup do you use.. the sound is good. I moderate some PASS VC’s and have to get a new microphone…

Answer: Brent, Jeremiah and I all use Yeti Microphones. We love ‘em. Glad the sound is good live! It suffers a little in the recordings we publish of the webcasts because the audio is recorded through our online webcast service. For trainings we record at home, the Yeti really gets to shine.

Hungry for more on making queries better? Check out our TSQL Training.

Writing queries with date and time data types can be tricky! Join Kendra to learn tips and best practices for selecting the right data types, choosing the best performing functions to round (or truncate) values, and how to avoid common pitfalls with dates and times in SQL Server. If you have one year of experience writing T-SQL queries, this free 30 minute webcast is for you.

http://www.youtube.com/watch?v=lhPkvjQy0us

Notes and Links

Datetimeoffset

A quick note about datetimeoffset. This is one of the more confusing topics of the webcast, because figuring out how to make applications timezone aware is tricky. A rule of thumb: if you truly need to store the time zone “offset” of the value from where the data originated for legal or research purposes (“What time did the data appear to be from the perspective of the user?”), then this is the datatype for you.

For display purposes in most applications, it’s more efficient to normalize off the time zone preference of the individual user in a separate table, then store datetime in a single standardized timezone like UTC.  Essentially, since people travel around and governments change timezones, persisting the information of what timezone someone was in when they did something is usually not worth it, because it’s usually not valuable information.

A great detailed discussion (with cartoons!) of circumstances where you might choose datetimeoffset vs datetime/datetime2 is in this StackOverflow question.

Posters

Poster downloads are over this way.

You’re a developer who wants to learn more about SQL Server, and you like learning by watching videos. Here’s some of our favorite 30-minute videos targeted at developers:

Index Design

• Index Basics – Kendra Little
• The Okapis of Indexes – Jes Schultz Borland
• Heaps (Tables Without Clustered Indexes) – Kendra Little

Blocking and Isolation Levels

• There’s Something About Nolock – Kendra Little
• Five Ways to Fight Blocking – Kendra Little

Query Design and Tuning

• Let’s Talk About Joins – Jeremiah Peschka
• Developer’s Guide to Dangerous Queries – Jeremiah Peschka
• How to Measure T-SQL Improvements – Kendra Little
• Windowing Functions in SQL 2012 – Jeremiah Peschka

Troubleshooting in Production

• First Responders Kit – Kendra Little
• What’s Going On in My SQL Server? – Kendra Little
• The Mystery of Query Timeouts – Kendra Little

SQL Server Setup & Usage

• How Does SQL Server Store Data? – Brent Ozar
• Developers and the Transaction Log – Kendra Little
• Completely Legal Performance Enhancements – Jeremiah Peschka
• SSMS: More than Meets the Eye – Jes Schultz Borland

Whew – that’s a lot of content! Don’t try to conquer them all in one sitting – instead, consider setting up a developer lunch & learn session once a week. The company buys pizza, and everybody watches one of these videos together in a conference room. Afterwards, talk about the lessons from the video and how they apply to the code challenges you’re facing.

If you’ve got Tuesday lunchtime free, check out our upcoming webcast lineup, too. Enjoy!

Get frustrated when you read conflicting opinions on the web? Me too — I can’t go to sleep when someone’s wrong on the Internet, but it’s tough to correct everybody. In this 30-minute session, I’ll explain the most common bad advice that I see, explain why it’s wrong, and show you how to set up your SQL Server for speed, not slowness.

Before you join us in Atlanta for our 2-day training class (or if you’re just thinking about it), let’s talk about your environment. In this 30-minute session, we’ll give you some vital statistics to check in your SQL Server before you leave work, and we’ll explain how our upcoming training class will help you improve those metrics.

In the video, we discuss bringing the results from a few troubleshooting queries – here’s the links:

• Wait Stats Triage – get a snapshot of what SQL Server has been waiting on.
• Get Database Sizes – excellent StackOverflow question and answer.
• Which Tables are Cached in Memory – check the buffer pool.
• Top 20 Resource-Using Queries – from the plan cache.
• Check Config Settings with sp_Blitz™ – quick diagnostic utility.

To get the most from the training class, run those queries ahead of time and bring the results to the training session.  You’ll learn to identify common T-SQL antipatterns, improve your indexes, and make your database faster.

Need help convincing your boss you should go? Here’s a business justification PDF you can hand ‘em.