08Jun 2020 by Grant Fritchey 3 Comments

AWS RDS and SQL Server Deadlocks

AWS, RDS
What's the story with AWS RDS and SQL Server deadlocks? I'm approaching AWS RDS like I was taking on a new role at a new organization. Do we have backups in place? Yes, great. Can I test them? Yes. Do they meet our RTO & RPO? Yes. Moving on. What have we got for monitoring? AWS RDS has a good percentage of the fundamentals. Now, it's laid out a little oddly. You have the stuff going through CloudWatch which is largely OS oriented. Then you have enhanced monitoring, which you have to turn on, which covers eight key metrics for SQL Server. Finally, you can enable Performance Insights which gives you metrics on query behaviors (and yeah, any or all of these may be the subject of upcoming blog posts).…
Read More
02Jun 2020 by Grant Fritchey No Comments

Fun Fact: AWS RDS and system_health

AWS, RDS
Did you know that the system_health Extended Event session was running in your RDS instances? Well, it is. HOWEVER. This query, which works perfectly fine on my on premises instance of SQL Server, will fail: SELECT @path = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SELECT @path = @path + N'system_health_*'; WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl; Whereas, thanks to Aaron Bertrand, this query will work just fine: WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl;…
Read More
01Jun 2020 by Grant Fritchey 4 Comments

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
29May 2020 by Grant Fritchey No Comments

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
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