04Oct 2021 by Grant Fritchey 5 Comments

PASS Data Community Summit 2021: I’m Excited!

Professional Development
I sincerely hope this isn't the first time you're hearing about the Pass Data Community Summit that's coming up in just a few weeks on November 8-12, 2021. But, just in case you haven't heard, let me tell you about a few things I'm personally excited about. Networking! There is a ton of stuff getting worked on to make this an interactive event. Even though this will be an online event (and a free one), we're doing everything we can think of to get you a community experience. We're providing mechanisms for all sorts of different networking. You'll be able to give a few virtual hugs to those you've missed. Learning! Check out the list of sessions. Everything from straight up, good, old fashioned query tuning in SQL Server, to…
Read More
27Sep 2021 by Grant Fritchey 1 Comment

Login Timeouts

You Can't Do That In Profiler
I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn't see anywhere else, let me share it with you. Extended Events You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up "timeout" in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It's not related to the login timeout. So, look up "connection" or "log in". You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login…
Read More
20Sep 2021 by Grant Fritchey No Comments

Containers: A Short Rant

Containers
I find myself doing more and more work with containers. Yet, I also find that a lot of people seem to be resistant to the concept. I'm always surprised when technologists reject technology without fully understanding what it does. Let's talk about this just a little. Containers Are Virtual Machines OK, not really. Containers are not actually, literally, virtual machines. However, containers are, conceptually, very similar to virtual machines. The key difference is, a container carries what it needs from the operating system it was created from in order to function. But, except for that, these things are just an extension of the concepts behind virtualization. Now, I know, in 2021, you are using virtual machines, in whole, or in part, to manage your IT infrastructure. You may host them,…
Read More
13Sep 2021 by Grant Fritchey 6 Comments

Rewriting The Query Tuning Book

Uncategorized
While I have not yet signed the contract, I have submitted an outline and proposal for a new version of my book on query performance tuning. Most of the information in the existing book is still very valid and immediately applicable. However, some of the information is out of date. Other pieces can be tweaked to tell a better story. A little bit of it is just wrong or has aged out of applicability. Because of all this, I'm not simply going to update the existing book. Instead, this time, it's a complete, from scratch, rewrite. All the way. I'm planning to drop entirely the chapters on hardware. I'm doing this for a bunch of reasons. One, hardware has changed radically over the years. Of all the information in the…
Read More
19Jul 2021 by Grant Fritchey No Comments

Filtering Extended Events Using Actions

SQL Server, You Can't Do That In Profiler
Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don't have to be collected in order to put them to work filtering your Extend Events. Using Actions To Filter Extended Events Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you're setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed…
Read More
06Jul 2021 by Grant Fritchey No Comments

Permissions Needed To Force Plans in Query Store

SQL Server
I was recently asked what permissions were needed to force plans in query store. I'm sure I knew at one point, but at the moment I was asked, I couldn't remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here's what I found. Permissions in Query Store Look through the blog, you'll find I'm pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy's work on that book, I just helped out). I haven't addressed security and Query Store. You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev…
Read More
29Jun 2021 by Grant Fritchey 2 Comments

Virtual Presentations: A Presenters Perspective

Uncategorized
While we are clearly beginning to see in-person events on the calendar, the vast majority of presentations, events, talks, etc., are virtual. There are a lot of positives to all these virtual presentations. People who can't travel, for whatever reason, can get access to presentations they might otherwise have never seen. A lot of the virtual presentations are recorded, so you can watch, or re-watch, at your leisure. Also, with the recordings, you can repeat sections, speed things up, slow things down, do more, to get more, with the presentation. Further, because so many of the virtual events are free, or radically reduced in cost, we have seen something of a democratization of presentations. In short, there are a lot of positive features for this currently prevalent approach. But you…
Read More
14Jun 2021 by Grant Fritchey 2 Comments

Find Queries Using a Key Lookup Operator

SQL Server
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…
Read More
24May 2021 by Grant Fritchey 2 Comments

Database Fundamentals #30: Create Foreign Keys With T-SQL

Database Fundamentals
You can create foreign keys using TSQL roughly the same way as you created primary keys. You can either use the ALTER TABLE statement to add the foreign key, or, if you already have the parent table created along with it’s primary key, you can use the CREATE TABLE statement to include foreign key constraints. The restrictions for creating foreign keys are still the same when using TSQL. Adding a Foreign Key Using the ALTER TABLE statement is very straight forward as before. This script will create a foreign key relationship between the Personnel.Person table and the Personnel.PersonAddress table: ALTER TABLE Personnel.PersonAddress ADD CONSTRAINT PersonAddress_FK_Person FOREIGN KEY (PersonID) REFERENCES Personnel.Person (PersonID); The ALTER TABLE and ADD CONSTRAINT statements are the same as what you saw before. They respectively refer to…
Read More
10May 2021 by Grant Fritchey No Comments

Query Compile Time

SQL Server, You Can't Do That In Profiler
A question that came up recently was how to track the query compile time. It's actually a pretty interesting question because, there aren't that many ways to tell how long it took to compile the query, and they don't necessarily agree. For most of us, most of the time, compile time for a given query doesn't matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters. How To See Query Compile Time If you want to see how long it takes a query to compile, you have, to my knowledge, three options. The first, and possibly easiest, is to look at the plan properties on an execution…
Read More