One of my favorite events of the year is the SQL Saturday in Silicon Valley. They've had four of them and I've gone to three (had to miss last year, scheduling conflict). It's a fantastic event and Mark Ginnebaugh (b|t) does a great job putting it together. In fact, this year, we got to listen to Ross Mistry and T.K. Rengarajan have a "fireside chat" for the keynote. For those who don't know, Mr. Rengarajan is just a VP at Microsoft. Yeah, he simply runs the ENTIRE FLIPPING AZURE DATA PLATFORM. That's all. They had a few demos and showed us unreleased code and new versions of SSMS not yet available publicly (including functionality around Query Store and execution plans, my little heart was going pitter-pat). Anyway, if you missed it, you…
With all the cool kids posting about beginners and interview questions, I thought I'd toss my favorites out there, from the brief-case gang point of view. These are the technical phone-screening questions I use after I look at a resume. There are only 10. They're simple. Stupid simple. Silly even. Yet, I can count on eliminating 4 out 5 people who have a resume that looks like a qualified DBA. I've seen people with 10 years experience fail on these questions. I'm only going to provide the questions. If you can't find the answers on your own, you're already disqualified: What is the difference between a clustered and non-clustered index? No, don't tell me that one is clustered and the other is not. I don't need specific low-level information on this, just…
A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn't know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that…
My name is Grant Fritchey. I have more than thirty years' experience in IT working in development and database administration. I work for Red Gate Software as a Product Advocate. I write articles for publication at SQL Server Central and Simple-Talk. I'm the Author of "SQL Server Execution Plans" and "SQL Server Query Performance Tuning." I helped co-Author "Query Store for SQL Server 2019", "Expert Performance Indexing", "SQL Server MVP Deep Dives 2", "Beginning SQL Server 2012 Administration" and "Pro SQL Server 2012 Practices." I present at conferences and user groups around the world. I am available for part-time, short-term, consulting contracts. Since 2009 I have been awarded as a Microsoft SQL Server MVP. I have received the AWS Community Builder award for the last four years. In 2014 I…
The invitation this month for #PGSqlPhriday comes from Dian Fay. The topic is pretty simple, database change management. Now, I may have, once or twice, spoken about database change management, database DevOps, automating deployments, and all that sort of thing. Maybe. Once or twice. OK. This is my topic. I've got some great examples on taking changes from the schema on your PostgreSQL databases and then deploying them. All the technical stuff you could want. However, I don't want to talk about that today. Instead, I want to talk about something really important, the concept of rollbacks when it comes to database deployments. Why Are Rollbacks Difficult? The entire purpose of your PostgreSQL database is to persist, that is to store, the data. Then, of course, offer up a way…
I was talking with some developers from my team about monitoring, and I said, "We all use the same tools," referring to other monitoring software. Then, it hit me. How is AWS collecting monitoring data on it's RDS servers, specifically, the SQL Server instances. So, I set out to determine what happens on AWS RDS when it comes to the native monitoring. The Setup This part should be as obvious as it is easy. I'm going to use Extended Events. I've written before about how AWS RDS supports Extended Events, so I won't repeat all that here. I'll just leave you with the session I'm running to see what happens on AWS RDS: CREATE EVENT SESSION [ExEventTesting] ON SERVER ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed ADD TARGET package0.event_file (SET filename…
Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I'm an idiot. As we needed proof. To be a little fair to me, I love what Distributed Replay could have been, with a little more love. However, fact is, it's on the deprecation list for 2022. Which means, what minimal amount of love, if any, that Microsoft was giving to it, it's all gone, forever. Unlike the Little Engine That Could, turns out that Distributed Replay was the Little Engine That Almost Could, But Didn't. Really Didn't. Let's discuss it a bit. Distributed Replay The concept is wonderful. Capture a bunch of queries from your production system. Replay them on a non-production system for testing. Add in the idea of being able to chain together…
In the last few Fundamentals posts you were introduced to a couple of ways to limit and control the data stored in the tables in your database. A primary key won’t allow a duplicate value. A foreign key won’t allow a value to be added that doesn’t already exist in the parent table and it will prevent data from being deleted. These are types of constraints on data in your database. There are a bunch of other ways to constrain the data in an effort to ensure that the data stored is exactly what the business needs. The next few Fundamentals posts will cover several methods of limiting data. Unique Constraints to Stop Duplicates When the concept of the primary key was introduced earlier in the series, two different types…
Everyone knows that you only get a single clustered index, right? Wouldn't it be great though if you could have two clustered indexes? Well, you can. Sort of. Let's talk about it. Two Clustered Indexes First I'm going to create a table: DROP TABLE IF EXISTS dbo.od; GO SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, pod.RejectedQty, pod.StockedQty, pod.ModifiedDate INTO dbo.od FROM Purchasing.PurchaseOrderDetail AS pod; With that in place, let's start with a clustered index: CREATE CLUSTERED INDEX TestCIndex ON od (ProductID); And, a query to test with: SELECT od.PurchaseOrderID, od.PurchaseOrderDetailID, od.DueDate, od.OrderQty, od.ProductID, od.UnitPrice, od.LineTotal, od.ReceivedQty, od.RejectedQty, od.StockedQty, od.ModifiedDate FROM dbo.od WHERE od.ProductID BETWEEN 500 AND 510 ORDER BY od.ProductID; This results in the following execution plan: OK. Well done, Grant. That's how a clustered index works. The…
Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post because there was a really bad checklist of performance tips making the rounds (pretty sure it's still making the rounds). The checklist recommended a whole bunch of silly stuff. One silly thing it recommended was to simply substitute ALL columns (let me emphasize that again, name each and every column) instead of SELECT * because "it was faster". My post, linked above, showed that this statement was nonsense. Let's be clear, I'm not a fan of SELECT *. Yes, it has some legitimate functionality. However, by and large, using SELECT * causes performance problems. SELECT * Hurts The most fundamental place…