Getting Started on AWS RDS

AWS, RDS
I'm expanding my skill set into AWS in a big way. So, one of the things I do when I'm learning a technology is to write blog posts about that tech. So, prepare yourself for a bunch of info on AWS. I'll be working with RDS, which will include SQL Server & PostgreSQL for certain. I'll also be posting quite a bit on AWS DevOps as I get into it. The usual stuff, including SQL Server, query tuning, Extended Events, Azure, Azure DevOps, and all the rest is not going away. I'm just adding another topic. Today, we're getting started on AWS RDS. Creating an RDS Instance The thing is, there's a lot of documentation available on getting started on AWS RDS, directly from Amazon. It's embedded neatly into the…
Read More

Let’s Talk About The Cloud

Uncategorized
I'm launching a new initiative called the Community Circle Cloud Conversation Club (c5 for short). It's going to a panel discussion lead by experts on cloud technologies. I'll be hosting it every Friday at 13:00 EDT starting next Friday, June 5. Every week will feature five different experts speaking on different topics. This coming week's experts are: Kellyn Pot'Vin-GormanBob PusateriFrank GeislerMindy CurnuttCarlos Robles Follow this link to take part on Friday. Our topic will be: "Why Platform as a Service is a Must For Cloud-Based Data Management" This is part of the Community Circle content hosted by Redgate. For all the details on the Community Circle go here. In a nutshell, it's community oriented training, videos, blog posts, classes and even a cookbook. All in support of your continued learning…
Read More

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

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

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

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

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

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

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

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