A Fine Slice Of SQL Server
Reitse Eskens looks at an oddity:
I was teaching a class and during an interesting discussing an attendee told me that views with a filter took a long time to produce results, even if the result set itself was quite small. I wanted to test this out for myself to see what was happening. I’ll take you along this short journey in this blog. The outcomes have been validated against a SQL 2017, SQL 2019 and SQL 2022 instance.
Click through for an example.
Comments closedGrant Fritchey sets up a showdown:
I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there’s already a query hint on a query, what happens when you try to force a similar query hint?
Yeah, OK, that is a weird one. I don’t know the answer, but I’m about to find out.
Click through for a very interesting demo. To be honest, I expected the opposite result, so this was surprising.
Comments closedRyan 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 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 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 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 closed