Grant Fritchey - Page 3 of 111 - Intimidating Databases and Code

07Jan 2019 by Grant Fritchey 4 Comments

PowerShell to Simulate Load

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: [crayon-5cbf95087ee9b283856315/] The idea is to be able to easily do one of several different things. By commenting out different sections of the code, I can change the general behavior. Most of the work is done in the # Run forever section of the code. First, I'll randomly…

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: [crayon-5cbf950891957034628043/] This is a really straight forward example of a plan guide. The only thing of note is that you should see that I have formatted my @stmt value exactly the same as what…

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: [crayon-5cbf950892456880323660/] Nothing to it really. What I'm going to do is execute the query. That will load it into the cache and into query store.…

12Nov 2018 by Grant Fritchey 14 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…

29Oct 2018 by Grant Fritchey 14 Comments

Explicitly Drop Temporary Tables Or Wait For Cleanup?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn't remember the specifics, but I said it actually didn't matter. However, that answer has bugged me, so I set up a quick test. Explicitly Drop Temporary Tables We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn't: [crayon-5cbf950892ccd384845194/] I then set up Extended Events to capture the query metrics and I executed each of the queries multiple times (also, just for the test, I discarded the results because I didn't want that process mucking with my measurements). After executing both procedures 500 times, the results were quite…

22Oct 2018 by Grant Fritchey 1 Comment

Why Did a Plan Get Removed From Cache?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I'm going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let's talk about how these work. Removed From Cache Just so we can see ALL the activity, I'm creating an Extended Events session that captures a little more than just the two events: [crayon-5cbf9508966f1473246168/] I'm capturing batch start and complete, rpc start and complete, and finally all the cache statements, hit, miss, insert and remove. The first time I run a procedure, the results could look like this: Since this is the first time running the…