I've been seeing more and more noise about how ChatGPT can help with technical stuff, writing code, all that. Well, I wanted to know how best to set up Query Store in SQL Server 2022, so I asked it. I'm not going to say this any other way, ChatGPT lied to me. Here's the conversation. I'm in italics. The Conversation With ChatGPT What are the best settings for Query Store in SQL Server 2022 Query Store is a powerful feature in SQL Server 2022 that helps in monitoring query performance and identifying query regressions over time. However, setting up Query Store requires careful consideration of several factors, including the size of the database, the frequency of queries, and the query workload. Here are some best practices for configuring Query Store…
Your mission, if you chose to accept it, was to share a single tip/hint/fact/something that you wish you'd known about PostgreSQL when you were just getting started learning it. Well, we've got several people who have put together some excellent posts. Let's go over 'em all, in no particular order. First up, Lætitia Avrot has a post all about the PostgreSQL documentation and it is well worth the read. I couldn't agree more with all her points. Plus, I love, love, LOVE the summary list of pointers to documentation broken down by role. Thanks so much for sharing all this. I also wrote a post about the docs. Lætitia covers them better. Next, Andreas Scherbaum has some very cool tips for using psql. My knowledge here is a bit slim.…
Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I'm sure I've said this before, so please allow me to repeat myself. The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can't look at two plans, with two costs, and say, "this plan will perform better." Instead, you can say, "this plan has a lower estimated cost."…
Wouldn't it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don't know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event. Bulk_insert_usage The BULK INSERT command is extremely useful within SQL Server. It's a way to move data into the database and provide some formatting on the way, efficiently, all through T-SQL. Hard to argue with the utility. Obviously, if you're doing traditional data collection through Trace or Extended Events, you'll see BULK INSERT commands within the T-SQL. However, Extended Events provides a specific event that tracks just the behavior of BULK INSERT: bulk_insert_usage. Documentation on this is somewhat sparse. Some of the best is from a standard source, Jason…
Just a suggestion, but I'd say you should look into Chocolatey. Let me explain why. Sabbatical For those who don't know I was recently on a six-week sabbatical from work (thank you Redgate) and I tacked a week of vacation to that. While I did clean out email during that time (can you imagine coming back to seven weeks worth... <shudder>), I didn't do software updates of any kind. In the meantime, Docker was updated. VSCode, SSMS, a whole slew of others. Not to mention, the busy little beavers at Redgate released umpty-million updates. My machine needed love, so I typed the following: choco upgrade all -y Then I went to get some coffee. Why? Because all that software that was out of date, it was getting updated, automatically by…
Redgate Software has a policy wherein every 5 years, employees receive a 6-week paid sabbatical. Well, I'm up to year 11 (I skipped a year my first time), so it's that time for me. First, thank you Redgate. I've loved working for you for 11 years. I'm looking forward to just as many more. Second, I've already written and scheduled a bunch of blog posts, so you'll still be seeing activity here. Third, I'm supposed to completely disconnect, but I won't. I'll be checking email, posts & messages, but I'll be slow on the response. Apologies if I don't get back to you quickly. See you in 7 weeks (I tacked on some vacation time. HA!).
For two years and three months, since April 2020, I've posted a tweet saying "Good Morning!" on every workday. I think I missed one. I was late for a couple. The messages were meant to be helpful. I used the word kind a lot. A lot. I tried to avoid lectures, and still did it sometimes. I tried to always be uplifting and positive, yet was still a downer occasionally. I've received many thanks for the tweets, in public and private. I cherish every one. Thank you! I also received quite a few "how dare you" and "you're not qualified" messages. For those, my answers are simple. I dare fine. You're right, I'm not qualified, doing it anyway. However, I'm drawing all that to a close. See, I'm going on…
TLDR: There ain't one. I was privileged last week to be able to present a couple of sessions at the SQL Server and Azure SQL Conference (great event, I recommend it). One of my sessions was an intro to query tuning. Basically, I went through a bunch of common code smells and suggested different possible solutions. The one thing that came up, both from my own stories and the questions from the (engaged) audience, is just how much everyone wants a magic, run fast, switch. Query Tuning The Easy Way Here you go. The single easiest way to make your queries run faster: Just throw money at the problem. Buy more hardware. Buy bigger hardware. Get more disks and disk controllers (not just more disks). Go to the next highest…
I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio. Go and get version 1.35 of the tool. Right now. DUDE! You have 1.35 of Azure Data Studio? Cool. Now, go to the menu bar. Click on "File." Click on "Preferences". Click on "Settings". Now, type the following into the search box: workbench editor enable preview. You should see this: Check the box below where it says "Workbench > Editor: Enable Preview" just like I have…
While teaching about Extended Events and Execution Plans last week, Jason, one of the people in the class, asked: Is there a way in Extended Events to find queries using a Key Lookup operation? Sadly, the answer is no. However, you can query the Execution Plans in cache or in the Query Store to find this. Thanks for the question Jason. Here's your answer. Finding Key Lookups Since we can't have Extended Events just feed us the information, we have to query the plans. That means pulling out data from the XML. So, to find queries that are using the Key Lookup operator, we can do this: SELECT DB_NAME(detqp.dbid), SUBSTRING( dest.text, (deqs.statement_start_offset / 2) + 1, (CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset END - deqs.statement_start_offset ) / 2…