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.
The PASS Virtualization Virtual Chapter hosted a Q&A session with me last week. We talked about storage configuation options like VMDK/VHD vs RDM, how licensing works, what’s the biggest SQL Server I’m comfortable virtualizing, and much more:
For more tips, check out our virtualization resources page.
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.
Notes and Links
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.
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 Basics – Kendra Little
- The Okapis of Indexes – Jes Schultz Borland
- Heaps (Tables Without Clustered Indexes) – Kendra Little
Blocking and Isolation Levels
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.
Developers frequently make a big mistake: they tune out on discussions of storage performance and IO. Don’t fall into this trap! Understanding how storage impacts SQL Server performance helps you tune the right parts of your code and focus on what really matters. In this 30 minute video, Microsoft Certified Master Kendra Little demonstrates why developers need to understand the IO impact on writes and reads in SQL Server. She’ll show you how simple configuration changes can sometimes save hundreds of hours of developer time.
Interested in the tools or references in the video? Check out the links at the bottom of this post.
Links and References
Log shipping is a tried and true method in SQL Server for keeping a copy of your data on a secondary server. You have your primary and secondary server set up, and it’s working great. Are you monitoring it? Do you know what to do if you need to fail over to the secondary? Join Jes in this free 30 minute video to find out!
In case you missed it: Log Shipping Part 1: Preparing for Disaster!
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.
Whether you’re planning a major migration, a code release, or an update to Windows security patches, there’s always the risk that something may go terribly wrong. How can you prevent disaster? In this 30 minute webcast, Kendra will share the top three elements to planning a successful change for SQL Server.