A Fine Slice Of SQL Server
Ryan Lambert looks at a code change:
This improvement adds new detail to the output provided from Postgres 15 when running
EXPLAIN (ANALYZE, BUFFERS) <query>. This post explores this feature along with a couple different ways the reported I/O timing interacts with performance tuning.
Read on to see how you can enable this feature and what it can do for you.
Comments closedHugo Kornelis looks at the execution plan:
Welcome to part twenty-one of the plansplaining series, where we will continue our look at execution plans for graph queries. In the previous post, we looked at the internal structure of node and edge tables, and discovered that they have a few hidden columns. Now let’s look how those columns are used in graph queries.
Read on for the example and a deeper dive into how graph tables actually work.
Comments closedErik Darling has a video for us. There’s no graf that I can include here, so I’m stuck having to come up with my own explanation…
This is an interesting video covering a fairly new feature in sp_WhoIsActive, as well as giving us some good information around the numbers meaning pages rather than (something)bytes, yet the memory_info column gives us results in kilobytes.
Also, be sure to grab version 12 of WhoIsActive.
Comments closedMatthew McGiffen defends a practice:
If someone else comes along to look at this code they don’t know what the value of zero means. My code’s not clear. Worse I might refer to the same value multiple times in my procedure, so if I need to change it later I have to change it in multiple places.
Good practice from other coding languages would say that I replace it with a meaningfully named constant. As mentioned, in T-SQL we don’t have constants so I’ll compromise and use a variable:
And that good idea is where everything goes off the rails. There are a few places where T-SQL offers you “close enough” to other programming languages but where performance suffers as a result: ‘constant variables” like in this example, nested views and user-defined functions for encapsulation, etc.
Comments closedJose Manuel Jurado Diaz has a script for us:
During our last session in SQL Data Saturday, we received a question about if it is possible to know all the conversion implicit captured by Query Data Store. In the following example, I would like to share with you an example how to capture this considering among of SQL Antipatterns.
Basically, in sys.query_store_plan we found the column called query_plan that contains the text of the execution plan. With this information plus other Query Data Store DMVs we could see the information required.
Read on to see how you can shred out implicit conversions from the Query Store plans.
Comments closedDany Hoter looks at some KQL query plans:
Each visual on the page is going to summarize data from one or more queries and add the summarize part of the query.
If your model contains multiple tables in direct query with relations between them, the connector will generate joins between the tables.
Selecting values in filters will create multiple where conditions.
In order to see the final query and understand the performance implications of each query and the total query load created by a report, you need to use the command “.show queries” in the context of the database.
Click through for Dany’s notes on the topic, including a few tips on what to look for.
Comments closedDennes Torres finds some anti-patterns:
One of the new Extended Event available in SQL Server 2022 is the query_antipattern. This extended event allows to identify anti-patterns on the SQL queries sent to the server. An anti-pattern in this case is some code that the SQL Server optimizer can’t do a great job optimizing the code (but cannot correct the issue automatically).
This is a very interesting possibility: Including this event in a session allow us to identify potential problems in applications. We can do this in development environments to the the problems earlier in the SDLC (Software Development Life Cycle). Let’s replicate some examples and check how this works.
Dennes shows two examples and notes that there are five total listed in the Extended Event, but that the documentation is a bit lacking to explain their intent.
Comments closedGreg Dodd has an Extended Events session for that:
Do you ever have a stored procedure that you know is performing badly and needs tuning, but you’re struggling to capture when it is run or what parameters were passed in?
I had this problem recently, I knew that a particular stored procedure was running slowly for some parameters, but figuring out what the bad combination was proved to be very difficult. What if, instead of trying to guess what the parameters were, I just captured all of the time that the stored proc ran, along with the run time?
This isn’t something you’d want to run for everything at all times, I’m sure, but this can be quite important when you’re fighting one poorly-performing stored procedure.
Comments closedAaron Bertrand threatens to angry up the blood:
I’ve long been a huge advocate for always referencing objects with a schema prefix in SQL Server.
In spite of what may be a controversial title to many of my regular blog readers, I don’t really want you to stop that practice in most of your T-SQL code, because the schema prefix is important and useful most of the time. At Stack Overflow, though, there is a very specific pattern we use where not specifying the schema is beneficial.
Mind you, Aaron’s use case is a rather niche example, so I don’t plan on burning him in effigy. Just maybe singeing him a tiny bit.
Comments closedWell, if SQL Server is trying to separate the read from the write, why don’t I just do that myself? I had the idea to read the data I needed in an INSERT…SELECT statement, writing into a memory-optimized table variable (motv). I could make sure the read included all the columns I need to calculate the new value, including the CASE statement I had in the SET clause of the UPDATE.
Click through for Jared’s explanation of how to implement it, as well as the circumstances in which it might be faster than what you get by letting a single T-SQL statement handle the job.
Comments closed