A Fine Slice Of SQL Server
Jordan Boich runs an experiment:
In my last post, I talked about how I was amazed that PostgreSQL doesn’t have a shared plan cache like SQL Server does. I wanted to create an experiment / lab where I could see this in action, so I built one. I’m going to be leveraging a free PostgreSQL database hosted on an Azure Flexible Server. I’ll also be using DBeaver to connect to my PG Database.
Click through to see what Jordan learned.
Leave a CommentBrent Ozar describes a pattern I often use:
Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.
When SQL Server gets that process wrong, switch to temp tables.
Read on for an example.
Leave a CommentDualcore DBA handles an overly broad class of query:
We have a query that SELECTs from the Users table that takes multiple optional parameters and filters the output based on those.
Whilst fairly easy to read and write, queries written in this way often underperform.
This is a very generous understatement. Click through for two ways to improve the performance of such queries, as well as the pros and cons of each.
Leave a CommentErik Darling has two problems:
All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…
why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.
Click through to learn more about both problems, including windows of time when SQL Server gets blackout drunk and can’t account for its time.
Leave a CommentBut what’s also strange here, too, is that SQL Server estimates one row very reliably for a date bucket. So, you know, you might be careful about that as well, especially if you’re returning far more than one row. You might be unhappy with the one row estimate.
It might bring you back to the bad old days of table variables and off histogram values, stuff like that. But, yeah, anyway, I had a point with all that. Let’s do this.
Click through to learn Erik’s point.
Leave a CommentDeborah Melkin concludes a video series on how to get execution plans in SQL Server:
I really wanted to put this together because I feel like understanding these differences is important to understanding how we can troubleshoot performance problems and where we need to be looking for these pieces of information to get that full picture of where to spend our time. It makes us better performance tuners. I hope you found this helpful and you leave with a better appreciation for these nuances.
Click through for the video, as well as a transcription on the blog post.
Leave a CommentDeborah Melkin continues a series on query plans:
This is the second part of the series. Hopefully you are all caught up on Part 1. If not, you can see that here.
Part 2 is about Actual Execution Plans. I have the (slightly edited for garbled words) transcript below for those who prefer to read, with references to places in the video. Otherwise, watch the video and let me know what you think.
Click through for the video and transcript.
Comments closedChad Callihan covers this month’s T-SQL Tuesday topic:
A lot of SQL queries or code can still “work,” but that doesn’t mean it’s good, especially with so much vibe coding and the like going on these days. When I think of signs I’ve noticed when seeing a query for the first time, one thing that will get my attention (besides seeing NOLOCK throughout) is when a string is being searched for surrounded by percent signs.
I’ve had instances of working with someone that was looking for certain error logs. We may know the log record starts with “Error 123” and could search a field for “Error 123%” in our query.
Click through for the consequences of a search on “Error 123%” versus “%Error 123%”. Sometimes it’s simply necessary to do the full search, but if you find yourself doing that frequently, it’s a sign that you could design the table better.
Comments closedLouis Davidson tries out a few query forms:
The post states that a query such as:
SELECT soh.SalesOrderID,(SELECT C.AccountNumberFROM Sales.Customer AS CWHERE C.CustomerID = SOH.CustomerID)AS CustomerAccountNumberFROM Sales.SalesOrderHeader SOH;will, by definition, execute that subquery on the
Customerobject one time per row in theSalesOrderHeadertable.But is this true?
Click through as Louis tests a few variants of this using SQL Server.
Comments closedI’m very excited for this because I’ve wanted to put something together about this topic for a long time.
What inspired me is that I’ve really come to appreciate that there’s different pieces of information collected with the execution plan itself depending on where I get the plan from. Understanding what’s collected, where, and why can help make a difference when trying to troubleshoot and performance tune.
I get most of my query plans from a shady vendor with an unmarked van in a Walmart parking lot.
Comments closed