Grant Fritchey

09Apr 2018 by Grant Fritchey 25 Comments

GDPR, Database Backups, and the Right to be Forgotten

DevOps

I've said it before, but it bears repeating, there is no cause for any kind of panic when it comes to the GDPR. None. There are however, a number of concerns. One of those concerns is, well, concerning. How does the right to be forgotten within the GDPR impact database backups? Let's discuss what we know. The Right To Erasure Each of the articles within the GDPR lays out a topic. Article 17 is pretty darned clear about the topic: Right to erasure ('right to be forgotten') Basically, the individuals, also known as the data subject, also known as natural persons, in short, people, can request that you remove their data from your system. The first sentence lays out the gist of the idea quite well: The data subject shall…

02Apr 2018 by Grant Fritchey 4 Comments

Buggy Whips 2.0

DevOps, Professional Development

I recently found myself rereading a very old blog post of mine, from the very beginning of this blog, discussing Buggy Whips. I'll save you the long read, I was learning new tech, it made me second guess my working assumptions, I was curious if I was manufacturing a buggy whip while watching an automobile drive by. 2008 to 2018 Well, I'm still here. In fact, Feature Driven Development has disappeared from the lexicon and the project that it was introduced to took years longer than anticipated, performed horribly, and had to have a major redesign and rework to be fundamentally functional (all after I left the old organization). So, my fears that database design was a thing of the past were just that, fears... right? Yes and no. Here…

30Mar 2018 by Grant Fritchey 4 Comments

YouTube Channel Update: March 30, 2018

Professional Development

Hey everyone! Here's the latest stuff on my new YouTube Channel. Please let me know if these videos are helpful. Also, if you have requests for videos on a particular topic, even a video of a blog post I did here, let me know. I take requests and I'm more than happy to help. I've been talking a lot about the GDPR. I released three videos on this topic this week alone: [embedyt] https://www.youtube.com/watch?v=FSbrhv1Fuh4[/embedyt] [embedyt] https://www.youtube.com/watch?v=YFyS45kvtMY[/embedyt] [embedyt] https://www.youtube.com/watch?v=YLYxflj8O9A[/embedyt] I also released a video on using SQL Prompt to help your team audit their code: [embedyt] https://www.youtube.com/watch?v=4TBHGuhgS4E[/embedyt] Finally, I added a video discussing database mirroring as it relates to Azure SQL Database [embedyt] https://www.youtube.com/watch?v=I26K-FWilXA[/embedyt]

26Mar 2018 by Grant Fritchey 6 Comments

Query Store and Automated Cleanup

SQL Server 2016, SQL Server 2017

Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount of data currently in the store. I didn't know the answer, so we tried it. Things got a little weird. Bryan Hundley of Marathon Consulting asked the question, so Bryan, this blog post is for you. Automated Cleanup There are actually two kinds of automated cleanup inside the Query Store. First, you have a time-based cleanup. By default it keeps queries that have been accessed within the last 30 days. Anything older, it tosses. If you have the stale_query_threshold_days (all in sys.database_query_store_options) set to 0, it…

19Mar 2018 by Grant Fritchey 1 Comment

Query Monitoring and the GDPR

DevOps

I've been reading the General Data Protection Regulation (GDPR) and discussing the ramifications of the beginning of enforcement with lots of people. The implications of it all are fascinating. The real serious issues remain primarily a business problem, with business defined solutions. However, there are technology issues that we need to think about. For example, performance metrics are going to be impacted by the GDPR. Private Data and Monitoring Queries First and foremost, let me say something I've said before. The vast majority of the focus around GDPR has to come from your business. Second, the bulk of your work and focus must be on ensuring core functionality in support of the GDPR. Third, the attack vectors and leaks for GDPR are not going to primarily be around something like…

12Mar 2018 by Grant Fritchey 6 Comments

Execution Plans and the GDPR

SQL Server, SQL Server 2016, SQL Server 2017

What? Execution plans and the GDPR? Is this it? Have I completely lost it? Well, no, not on this topic, keep reading so I can defend myself. GDPR and Protected Data The core of the GDPR is to ensure the privacy and protection of a "natural person's" information. As such, the GDPR defines what personal data is and what processing means (along with a bunch of additional information). It all comes down to personally identifying (PI) data, how you store it, and how you process it. More importantly, it's about the right for the individual, the natural person, to control their information, up to and including the right to be forgotten by your system. OK. Fine. And execution plans? Execution Plans and PI Data If you look at an execution…

05Mar 2018 by Grant Fritchey 11 Comments

Every Single Execution Plan is an Estimated Plan

SQL Server, SQL Server 2016, SQL Server 2017

All the execution plans are estimated plans. All of them. There fundamentally isn't any such thing as an "Actual" plan. Where Do You Get Execution Plans? There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store. All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they're all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This…

02Mar 2018 by Grant Fritchey No Comments

YouTube Channel Update

Misc

Hey! Here's the latest on my YouTube Channel. Click on through and subscribe. I'll be doing lots of stuff on the Microsoft Data Platform, GDPR, DevOps, Redgate, and all sorts of IT related information. Here are the last couple of weeks worth of videos: Very popular was my discussion of the idea that the reason DBAs don't adopt the latest and greatest piece of shiny is because we're all beholden to vendors instead of trying to make good decisions for our the organizations that pay us (as if the "best" decision is to always grab the new shiny). OK, I'm giving away my thoughts, here's the video: [embedyt] https://www.youtube.com/watch?v=0TVeRSc3gLg[/embedyt] Next most popular was my question, so, if we're not resisting change because of vendor lock (and we're not) why are…

28Feb 2018 by Grant Fritchey 4 Comments

Precons in Richmond, Philadelphia and New York

Professional Development

I love when I get the opportunity to present at SQLSaturday events. Even more than that, I love when I get the opportunity to do a precon at a SQLSaturday event. Well, I've got three coming up. All three are an all day session entitled "SQL Server Tools for Query Tuning." Seating at all the events is limited, so please register early. First, in Richmond, on March 23, 2018, you can register here. Then, I'll be in Philadelphia on April 20, 2018. You can sign up here for that event. I will be in New York, NY, my old stomping grounds, May 18, 2018. Go here to register for that event. I hope to see you at one of these events where I'll do my best to share as much…

27Feb 2018 by Grant Fritchey 6 Comments

Adaptive Joins

T-SQL

I was surprised to find out that a lot people hadn't heard about the new join type, Adaptive join. So, I figured I could do a quick overview. Adaptive Join Behavior Currently the adaptive join only works with columnstore indexes, but according to Microsoft, at some point, they will also work with rowstore. The concept is simple. For larger data sets, frequently (but not always, let's not try to cover every possible caveat, it depends, right), a hash join is much faster than a loops join. For smaller data sets, frequently, a loops join is faster. Wouldn't it be nice if we could change the join type, on the fly, so that the most effective join was used depending on the data in the query. Ta-da, enter the adaptive join.…