A Fine Slice Of SQL Server
Dualcore DBA once shot a query in Reno, just to watch it die:
I love Query Store. I like to think of it as SQL Server’s built-in monitoring tool and I use it a lot to look at query history in terms of run times and IO metrics.
One thing I learned about Query Store recently is how it deals with queries that have been killed.
Read on to see how not all stopped queries are equal in the eyes of the Query Store.
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 CommentI’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 closedEmad Al-Mousa plays around a bit:
It’s a security mechanism designed to filter, and block unauthorized or malicious SQL query being executed against the database system before it reaches the database kernel itself. It acts as a specialized gatekeeper that ensures only “known good” queries are allowed to run. In a sense, you can compare it with WAF (web application firewall) in terms of protection mechanism.
SQL Firewall will provide protection against the following threats and attacks:
SQL Injection
Privilege Escalation
Data Exfiltration
Emad makes it very clear that this is not a viable technique, but it is also a fun enough idea that it’s worth checking out.
Comments closedErik Darling is on a hunt for bad queries:
That’s the funny part. Alright. Cool. With that out of the way, let’s look at this new store[d] procedure. Uh, I think I have to go to Management Studio. Yeah, I remember what that looks like. Alright. Cool. So, uh, this is, this is it. SPQuickieCache. Pay no attention to the terrible red squiggly underlines.
I think Erik made his AI business partner angry, as it didn’t strip out any of the filler words from the transcript. But this does look like a neat stored procedure.
1 CommentErik Darling channels an Italian economist:
In this video, I dive into an old-school approach to identifying SQL Server performance issues using SP Quickie Store and a novel method inspired by the Pomodoro [“Pareto” – ed] technique. Traditionally, Query Store surfaces queries that consumed a lot of CPU over the last seven days, but often these results are too broad for practical use. To address this, I’ve developed a multi-dimensional scoring system that evaluates queries based on their impact across several key metrics: CPU usage, duration, physical reads, writes, and executions. This approach helps pinpoint the most problematic queries more accurately, even when they run outside of typical working hours or are unparameterized. By sharing these insights, I hope to provide a practical tool for SQL Server administrators looking to optimize their databases without relying solely on modern monitoring tools.
The AI generated summary reminds me that I’ve been working for 25 minutes, so time to take a break.
I like the idea of calculating and calculating & displaying impact scores, as well as breaking it down into core components.
Comments closedErik Darling performs a rather late Airing of Grievances:
In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.
Click through for the video.
Comments closedKendra Little does some investigating:
Triggers can be tricky to observe in SQL Server. When you need to understand exactly what a trigger is doing and what it impacts, Query Store and Extended Events both have something to offer, but using them effectively requires navigating some confusing nuances. Query Store tracks query-level executions aggregated by query_id, but has a habit of generating a lot of query_ids with different context settings for triggers. Extended Events can capture trigger module starts and individual statement completions within triggers.
This post walks through setting up both observation methods on a test trigger, then compares what each one shows. You’ll see how Query Store data gets spread across multiple query_ids and context_settings_id values, a variety of ways to trace triggers in XEvents, and why the numbers don’t always match up exactly between traces and Query Store.
This is definitely worth the read. Kendra does a great job laying things out in a thoughtful manner and provides a lot of additional insight.
Comments closedNew query hint, ABORT_QUERY_EXECUTION is intended to be used as a Query Store hint to let
administrators block future execution of known problematic queries, for example non-essential queries causing high resource consumption and affecting application workloads.
Read on to see how the Judge Dredd of query hints works.
Comments closedErik Darling has a new tool available. This link goes to a video Erik created on the topic. Erik pokes fun at Microsoft naming, but I think Erik could make use of Microsoft marketing. That way, they can change the names of all of his procedures every six months. Oh, and three of them will be called sp_Mirroring while being quite different.
And the script is available on Erik’s GitHub repo.
Comments closed