26May 2020 by Grant Fritchey No Comments

Database Fundamentals #25: Referential Integrity

Database Fundamentals, SQL Server
If you have been reading through all my fundamentals posts and following along, you have built a small sample database, loaded it with data, and learned how to retrieve the data from it. You’ve also learned how to relate one table to another through T-SQL JOIN statements. But that relationship is very temporary. It will last only as long as it takes for that query to run. To create a database that enforces the relationships between the tables, you need to work with declarative referential integrity (DRI), frequently shortened to referential integrity(RI). DRI is the foundation on which the relational part of the relational storage engine is built. It’s not just a nice thing to do for your database. It’s actually a fundamental piece of how SQL Server works. DRI…
Read More
18May 2020 by Grant Fritchey 2 Comments

Can We Get Row Counts After Execution?

SQL Server, T-SQL
The general idea for this question came from dba.stackexchange.com: could we, and if we can, how, get row counts after execution. I was intrigued with the idea, so I ran some tests and did a little digging. I boiled it all down in the answer at the link, but I figured I could share a little here as well. Properly Retrieve Row Counts After Execution The right way to do this is obvious and simple. Before you need it, set up an Extended Events session. Done. The only question is what goes into the Session. First blush, sql_batch_completed and/or rpc_completed. Both will return a rows affected value. Although, interestingly, the row_count value is documented as rows returned. However, it's both. But, if you really want to get picky, batches and…
Read More
11May 2020 by Grant Fritchey 7 Comments

Find Indexes Used In Query Store

SQL Server
One of the most frequent questions you'll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead. I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one. What if we queried the information in Query Store? Indexes Used in Query Store Now Query Store itself doesn't store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and,…
Read More
04May 2020 by Grant Fritchey No Comments

Database Fundamentals #24: More Filtering Data

Database Fundamentals, SQL Server
In this Database Fundamentals post we continue discussing the functionality of the WHERE clause. We started with the basics of the logic using things like AND, OR and LIKE or '='. Now, we'll expand into some other areas. Functions in the WHERE clause SQL Server provides you with all sorts of functions that can be used to manipulate strings, modify dates or times or perform arcane mathematical equations. The problem with these is that if you do them on columns in tables it can lead to performance issues. The trick then, is to not perform functions on the columns in the tables. We’ll cover this in more detail when we get to indexing, variables, and parameters. Just don’t get into the habit of putting functions on the columns in your…
Read More
27Apr 2020 by Grant Fritchey 7 Comments

How Does The CHOOSE Command Affect Performance?

SQL Server, T-SQL
Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance. On the face of it, I honestly don't think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing. T-SQL CHOOSE Command The CHOOSE command was added in SQL Server 2012. It's fairly straight forward. You supply an array and a numbered index for that array and CHOOSE will pull the matching value for that index. It works like this. We'll start with a simple proc and execute it: CREATE OR ALTER PROC dbo.CarrierAndFlag ( @SalesOrderID INT, @Flag INT ) AS BEGIN SELECT sod.CarrierTrackingNumber, CHOOSE(@Flag, 'A', 'B', 'C') AS Flag FROM Sales.SalesOrderDetail AS sod WHERE sod.SalesOrderID =…
Read More
20Apr 2020 by Grant Fritchey 4 Comments

The Very Best of Extended Events

SQL Server
Over the next couple of months, I'll be putting on a number of different sessions teaching about the tools supplied by Microsoft, for free, that can help you when tuning your queries. One of the most important of these tools is Extended Events. A couple of my sessions in the Redgate Community Circle livestream "Built-in Tools Make Query Performance Tuning Easier" will be on Extended Events. My livestreaming starts tomorrow, April 21, at 2pm Eastern. It will be recorded and made available for free. Follow the link for all the details, or, just subscribe to Redgate's YouTube account. I'm also going to be hosting a fundamentals introduction to Extended Events, "The Easy Way to Extended Events." Heck, I'm even going to be hosting a session showing how to use Extended…
Read More
16Apr 2020 by Grant Fritchey 4 Comments

Redgate Community Circle

Professional Development
I am very excited to announce that I will be taking my paid precon content "Tools for SQL Server Query Performance Tuning" and presenting it for free. This is all a part of a new initiative from Redgate Software called Community Circle. Read about that here. I'll be livestreaming the precon once a week on the Redgate Youtube. So, please tune in, every Tuesday at 13:00CDT. Or, you can watch the recordings of the livestream, which we'll host on Youtube, free, forever. Between classes, you can ask questions, make comments, and generally interact through a Discord server I've set up here. We'll be covering a whole slew of topics from Query Store to DMVs, SQL Server Management Studio to Extended Events, Execution Plans and more, lots more. Whether you watch…
Read More
13Apr 2020 by Grant Fritchey 10 Comments

Develop Resiliency

Professional Development
We are all going through some tough times. It's tougher for some more than others. Now is the time when you have to work on being resilient, and it ain't easy. However, there are things you can do to develop resilience. Like most things, it's actually a skill that can be learned. Let's start with a little disclaimer up front. I'm no expert on this topic. However, I do have a few bonafides and I'll share them so you know where I'm coming from. If you don't care, skip to the next section. My Training and Experience In Resiliency Without going into personal details, like everyone, one of my teachers has simply been life. At 57, I've done a few things and been a few places. I've gone through tough,…
Read More
06Apr 2020 by Grant Fritchey 6 Comments

What Prevents You From Securing Your Servers?

Database Fundamentals
Reading about how hackers are using SQL Server instances that are exposed on the internet AND have weak passwords to work into systems, I'm sitting here wondering why. I get it. A pretty hefty majority of these were simply set up by people who didn't know any better. Computers and databases are now so wonderfully easy, almost anyone can slap together a database server, and do. Simple ignorance leads them to misconfigure the systems and leave them exposed. So, while it's hard to forgive this ignorance, it's easy to understand. I sincerely doubt we'll ever be able to do much about it. I suppose if systems were designed to be secure from the moment of installation, that would help. Stuff like ElasticSearch having it's development servers have zero security out…
Read More
31Mar 2020 by Grant Fritchey 2 Comments

Unit Testing & Intelligent Query Processing

Uncategorized
Actually, these two topics don't have anything to do with one another. I just ran out of days to promote everyone individually who was taking part in putting on Redgate Streamed. Steve Jones Steve is one of the better people I know when it comes to the wisdom and need for testing. Speaking only for myself, I kind of hate setting up tests. Yes, I know how vital they are. Yes, I know they make an enormous positive impact on our ability to generate better code, faster. Yes, I know they're a fundamental aspect of DevOps. Yet, they're a pain the bottom. However, Steve has a way with them. He really does make them look easy. If you're like me and not a huge fan, then his session "The Basics…
Read More