Grant Fritchey

21Jan 2019 by Grant Fritchey 1 Comment

Get Your Learn On

There is one truth that I can say about technology with an absolute certainty: It's going to change. Get your learn on! Technology is going to be shifting under your feet, constantly. Even if all you ever do is work with SQL Server, on premises, on hardware, without VMs, containers, clouds or any of that foofaraw, things are going to change. Dealing With Change What's the best way to deal with change? Get your learn on! Learning all the new stuff is absolutely necessary. It's unavoidable. Even if you're not running SQL Server 2019 in production today (and very few of you should be as I write this because it's still in preview) because you're still on SQL Server 2005 (and, by the way, support ended in April of 2016,…

14Jan 2019 by Grant Fritchey 14 Comments

DevOps and Automation Will Eliminate the DBA

DevOps

I've been reading about the death of the DBA ever since I first made the jump from full time developer to full time data professional. The first time I heard it was when SQL Server 7.0 was released. Did you know that SQL Server 7.0 was self-tuning? In fact, it was so self-tuning that the DBA is a relic of the past and no one will be paid for that kind of work any more. Right. So, twenty years later, several versions of SQL Server with tons of improvement from back in the day, and I'm still working (and I hope you are too). Object databases were going to eliminate the DBA. ORM tools were going to eliminate the DBA. Then of course, NoSQL absolutely eliminated the DBA. In fact,…

07Jan 2019 by Grant Fritchey 4 Comments

PowerShell to Simulate Load

PowerShell

Gathering metrics is quite difficult if there are no queries. So, if you're working in non-production environments, but you still want to see some sort of load on the server, how can you do it? I use a simple PowerShell script to simulate load. Simulate Load I've posted a sample of the meat of the script before. It's a simple way to test a procedure. However, I've modified it to do a little more than just run the procedure forever. Here's the script: # connect to the database $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=WIN-8A2LQANSO51;Database=AdventureWorks2017;trusted_connection=true' # gather values $RefCmd = New-Object System.Data.SqlClient.SqlCommand $RefCmd.CommandText = "SELECT th.ReferenceOrderID, COUNT(th.ReferenceOrderID) AS RefCount FROM Production.TransactionHistory AS th GROUP BY th.ReferenceOrderID;" $RefCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $RefCmd $RefData = New-Object System.Data.DataSet…

17Dec 2018 by Grant Fritchey No Comments

Redgate Reviews 2018, Predicts 2019

Uncategorized

Our boss came to us and said, "We need one more webinar to round out the year." Well, we're just like you guys, counting down the hours until our holidays start like a short-timer in the military looking at that discharge date.Â The boss wants an entertaining, engaging, informative webinar. You know, the usual stuff that Redgate always delivers. Yet, we are seeing the holiday light at the end of the work tunnel so none of us wanted to take on too much of a burden. So, we came up with a solution. We'll all work together to deliver a single webinar. A good round table discussion of all the cool stuff we've seen this year from Redgate, Microsoft, and the rest of the world of data. Plus, we'll toss…

10Dec 2018 by Grant Fritchey 4 Comments

Privacy and Protection in the Age of the GDPR

Redgate Software

We're seeing more and more GDPR-style laws coming out from various governments. With the GDPR starting to do enforcement at long last, it's pretty clear that privacy and protection is a growing thing. Add to this the fact that SQL Injection is still an issue along with all the other ways you can get hacked. Let's face it. We have to secure our servers better. SQL Census The Redgate Foundry is pretty much what it sounds like, a place to build tools. Basically, it's where pie-in-the-sky style experimentation takes place at Redgate. That's not to say that we don't also do very down-to-earth experimentation. In fact, for this whole privacy and protection thing, we're working on a number of tools. The one I want to draw your attention to right…

03Dec 2018 by Grant Fritchey 9 Comments

Database Fundamentals #20: Using the JOIN Operator, Inner Join

Database Fundamentals

It is entirely possible to try to JOIN two tables on almost any field, as long as the two data types can, in some way, be made to reconcile to each other, you can try to join the tables. But, most database designs assume a much more directly relationship and provide a column or columns in one table that match the identifying column or columns in the other table. INNER JOIN The INNER JOIN will return the parts of both data sets that match. Frequently, what you'll see when joining two tables is the same column name in each table. With that, you have to be sure to identify the owner of each column. I've introduced what is called an alias to make it so I don't have to type…

26Nov 2018 by Grant Fritchey 5 Comments

Forcing a Plan That Has a Plan Guide

SQL Server 2016, SQL Server 2017, T-SQL

The question that came up during a recent class I was teaching was: What if you have a plan guide to get the plan you want, but then decide, instead of using the plan guide, you'll just force the plan? Ummmmâ€¦. No idea. Let's test it. First, Create a Plan Guide I have a couple of queries I use to teach about how statistics affects plan choice, so we'll use that here. I'm going to also define and create a plan guide that makes this plan use a small row count for all queries against it: CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; GO…

19Nov 2018 by Grant Fritchey 1 Comment

Query Store and Plan Cache Plans Compared

SQL Server 2016, SQL Server 2017

Query Store plans and the plans in cache are identical, right? There won't be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up. Differences Between Plans In order to compare the two plans, first, we need a query. Here's a stored procedure that I'm going to use to generate a plan that will be in cache and in the query store: CREATE PROC dbo.ProductTransactionHistoryByReference ( @ReferenceOrderID int ) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID…

12Nov 2018 by Grant Fritchey 16 Comments

Bad Query Performance Tips Rebutted

T-SQL

Once upon a time, someone, somewhere, wrote a list of bad query performance tips and they've been copied all over the internet, over and over. Even worse, sometimes the tips are copied or edited incorrectly, making a bad suggestion even worse. Can we please, stop copying this list? I've tried to poke holes in this list before, one point at a time. This time, I'm just taking it all on. Follow This Advice I'm going to list all the bad query performance tips and I'll explain where they're good and where they're bad. However, the single most important thing I can tell you is, test your own system. Some of these tips have some merit, situationally. You'll need to see if you're in the situation. So please, don't just trust…

05Nov 2018 by Grant Fritchey No Comments

What Is Happening, Right Now, At the PASS Summit?

PASS, Redgate Software

Honestly, that's a very hard question to answer. I mean, first of all, you can look at the schedule. There will be all day pre-conference seminars going on today (when this is published) and tomorrow. The rest of the week has all the breakout sessions. During the event this week, if you're not there, you can take part in some of the event by visiting the PASSTV web site. Other than that, you can follow me on Twitter (@gfritchey). It really is one of those "you need to be there" kind of events. If you're not there this year, start planning for next year. However, Redgate is here to help. What We Learned at PASS Summit Redgate is going to hold a webinar on Tuesday, November 20th. The title of…