Redgate published a report that many of you helped with by providing information. First, thanks! Your info really helped. Second, do you want to see it? You can. Go here and get a copy. However, want to talk about it? Anthony Nocentino and I are hosting a little chat this week on Wednesday at 4-5 BST, 10-11 Central. You can click on this to get registered. Please do. It's going to be great information and, since it'll be live, you can ask questions. It will be recorded and you can watch it later. However, where's the fun in that? Join in. Be there live.
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;…
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 =…
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…
I sincerely believe the key to your future as a DBA is your ability to automate everything you do. However, the single hardest thing that you have to do is keep up with the changing business and IT environment, which means, deployments. Lots of deployments. How are you going to get that done? Cathrine Wilhelmsen I have to admit, publicly, when I first met Cathrine, I was not the nicest person. I just couldn't believe that this person who sounded like they were from Ohio was actually Norwegian. I asked for her drivers license. Thankfully, Cathrine forgave me my rudeness and I'm very grateful for that. The reason I'm grateful is, she's seriously on top of her game when it comes to automation. At the upcoming Redgate Streamed event, April…
Maybe you're using DevOps within your database development and deployment. Maybe you're not. Maybe you're automating all the things or maybe you've got a completely manual set of processes. Fact is, Redgate would like to know. If you can spare a couple of minutes to swing by this link and fill out this survey, I'd sure appreciate it. The fact is, database deployments, regardless of the database, regardless of it being relational or not, can be very difficult. The core of the problem is retention of the existing data. If it was possible to deploy databases the same way we deploy code, throw away the old one, install the new one, usually in a single step, that would be great. However, unfortunately, throwing away databases usually gets organizations quite upset.…
I've been teaching a lot more about SQL Injection lately (including blog posts). I've been doing this because, despite this being a 21 year-old problem with well defined solutions, we're still dealing with it. Recently, while sitting in the speaker room at Techorama Netherlands (fantastic event, strongly recommended), I had the opportunity to spend a little time with Niko Neugebauer. I was freaking out because my demos were failing (fixed 'em finally). Niko was talking to me about the new Feature Restrictions and their effect on SQL Injection in SQL Server 2019. I didn't know what he was talking about, so I had to look it up. Of course, top resource, Niko's blog. Feature Restrictions in SQL Server 2019 The Feature Restrictions in SQL Server 2019 are actually being added…
I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It's not that we're going to get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…
I'm going to start with a sentence that makes a lot of people crazy; As a DBA and database developer, I love Entity Framework. That's right. Entity Framework is the bomb. It's amazing. It does wonderful stuff. Entity Framework makes the developers lives and jobs easier. It makes my life and job easier. Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That's the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell. One nit that I've always had with Entity Framework is that it's very…
I am quite excited to announce that the latest, most up to date, and by far the largest, copy of my book on query tuning is now available. 900 pages of information on how to improve the performance on your Azure SQL Database and SQL Server instances has been published. You can get your hands on the digital copies now and the print copies will be available shortly. Follow this link to Amazon to get your copy. Special, public, and heart-felt thanks to my technical editor, my hero, Joseph Sack. He's worked with me through four editions of this monster and the book wouldn't be what it is without him. If you'd like to learn some of the material in the book, I'll be presenting an all-day seminar two more…