04Apr 2022 by Grant Fritchey No Comments

Two Years of “Good Morning!”

Professional Development
Two years ago at the start of the pandemic, I wasn't feeling great about things. I saw that quite a few others weren't all that thrilled about how things were going either. So, to help my own mood, and to try to be a service to others, I started tweeting a little "Good Morning!" post every work day. I filled them with happy thoughts, tips I'd read on handling depression, suggestions I learned on health, anything that would help people, just a teeny amount, at the start of the day. Now, I'm not some licensed therapist or anything like that. I just wanted to do something to help others where I know I needed help. That's it. Two years later and I've written a bunch of tweets. Based on feedback…
Read More
14Mar 2022 by Grant Fritchey No Comments

Execution Plan Properties

Uncategorized
I've watched several people recently go straight to XML when reading execution plans because they didn't know about the execution plan properties in the first operator. Now, don't get me wrong. If going straight to the XML is working for you, that's fine. Keep doing it. I'm absolutely not questioning how anyone does things. I just want people to know that "hidden" information isn't so much hidden as much as it's not too obvious. First Operator I've written before about the first operator in an execution plan (here, as well as here, and here, and even a problem with them here). I don't have a whole lot to add to those posts. If you look around at other blog posts I've done on execution plans, I use the properties of…
Read More
07Mar 2022 by Grant Fritchey 4 Comments

Query Plans in Azure Data Studio

SQL Server
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…
Read More
28Feb 2022 by Grant Fritchey No Comments

Database Fundamentals #31: Unique Constraints from the GUI

Database Fundamentals, SQL Server
In the last few Fundamentals posts you were introduced to a couple of ways to limit and control the data stored in the tables in your database. A primary key won’t allow a duplicate value. A foreign key won’t allow a value to be added that doesn’t already exist in the parent table and it will prevent data from being deleted. These are types of constraints on data in your database. There are a bunch of other ways to constrain the data in an effort to ensure that the data stored is exactly what the business needs. The next few Fundamentals posts will cover several methods of limiting data. Unique Constraints to Stop Duplicates When the concept of the primary key was introduced earlier in the series, two different types…
Read More
22Feb 2022 by Grant Fritchey No Comments

Azure Data Studio Intellisense

SQL Server
I recently saw a question about the Azure Data Studio Intellisense: "Why won't intellisense in Azure Data Studio work with different schemas?" Immediately I thought, "Wait, it does." But, testing is your buddy. Azure Data Studio Intellisense Azure Data Studio Intellisense is on by default. Also, I like it a little better than the one in SSMS because it will start trying to help you, as you type (like a 3rd party software I can't live without). However, they're partly right. Let's say I want 'Person.Address' so I start typing like this: It doesn't know that I have a table named 'Person.Address'. It's trying to be helpful. You can even see how it's doing a form of wild card search, suggesting that maybe 'db_accessadmin' is what I want since it…
Read More
14Feb 2022 by Grant Fritchey No Comments

Extended Events Session Properties

SQL Server
I like Extended Events and I regularly use the Session Properties window to create and explore sessions. I'm in the window all the time, noting it's quirks & odd behaviors, even as it helps me get stuff done. However, found a new one. Let me tell you about just a few of them. Session Properties Window When you open the Extended Events session properties window for an existing session, in SSMS 18.1, it should look something like this: See the problem? Well, that is the problem. Here, look after I resize it: There it is. At the bottom. By default, the window isn't sized correctly so you see everything. In fact, I'm in the habit of maximizing the window, just because it makes it easier to work with. However, I…
Read More
09Feb 2022 by Grant Fritchey 6 Comments

SQL Server, PostgreSQL and the Future

PostgreSQL, SQL Server
This is just a quick note to talk about the future, mine, yours and this blog. First, I'm not abandoning SQL Server. I'm actively working on a revision of the query performance tuning book (something I should be working on instead of writing this post). SQL Server is my first, and best, database love. We're not going anywhere, SQL Server and I. You can expect more posts on SQL Server, query tuning, execution plans, DevOps, community, #sqlfamily, and everything I've blogged about for the last, ooh, 14 years next month. Second, our family is growing. Just like when a family brings home a new baby, well, I'm adding another database. It doesn't mean I don't love my first database. I still do. We're just going to ALSO be talking about…
Read More
07Feb 2022 by Grant Fritchey 1 Comment

Query Your Statistics: dm_db_stats_properties

SQL Server
We've always been able to look at statistics with DBCC SHOW_STATISTICS. You can even tell SHOW_STATISTICS to only give you the properties, STAT_HEADER, or histogram, HISTOGRAM. However, it's always come back in a format that you can't easily consume in T-SQL. From SQL Server 2012 to everything else, you can simply query sys.dm_db_stats_properties to get that same header information, but in a consumable fashion. dm_db_stats_properties You can pretty easily query the function dm_db_stats_properties: SELECT ddsp.object_id, ddsp.stats_id, ddsp.last_updated, ddsp.rows, ddsp.rows_sampled, ddsp.steps, ddsp.unfiltered_rows, ddsp.modification_counter, ddsp.persisted_sample_percent FROM sys.dm_db_stats_properties(OBJECT_ID('Production.Location'), 1) AS ddsp; You just have to pass in the object_id value and the stats_id value and you're off to the races. I can hear you. Why? So we can do things like this. What if I want to look at statistics that have fewer…
Read More
03Feb 2022 by Grant Fritchey No Comments

PostgreSQL Restore in Azure

Azure, PostgreSQL
I recently wrote an article about PostgreSQL restores (and by extension, backups) over on Simple-Talk. The restore process within PostgreSQL, without 3rd party involvement, can be a little tricky. However, when you are using a Platform as a Service offering, like Azure Database for PostgreSQL, things get quite a bit easier. Let's explore this just a little. Restores Come From Backups I know we all know this, but as I said in the article over on Simple-Talk, discussing backup strategy is a mistake. You should instead be discussing a restore or recovery strategy. How do you intend to get your database back? How much data are you prepared to lose? How long should a restore take? Answering these questions leads you to a Recovery Point Objective (RPO), which tells you…
Read More
24Jan 2022 by Grant Fritchey 3 Comments

Which Columns Are Looked Up?

Uncategorized
A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let's take a quick look. Which Columns Are Looked Up? Let's take an example query: SELECT p.NAME, AVG(sod.LineTotal) FROM Sales.SalesOrderDetail AS sod JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE sod.ProductID = 776 GROUP BY sod.CarrierTrackingNumber, p.NAME HAVING MAX(sod.OrderQty) > 1 ORDER BY MIN(sod.LineTotal); There is a nonclustered index on the ProductID column. Depending on the value, it's going to reduce the rows returned extremely well. So, the optimizer is likely to pick that index. Here's the index definition: CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail (ProductID ASC) In…
Read More