22Jul 2019 by Grant Fritchey No Comments

Importance of Testing

DevOps
I've always said if you're running a script for the first time in a production environment, you're doing it wrong. Testing is fundamental to technology, yet it is one of the single most frequently skipped processes. Let's talk about this a moment. Developing in Production Through my work, I travel quite a bit. That means I get to meet a lot of different people with varying circumstances on their systems. I've even met someone who did all their development directly in production. Why? They ran a software that ran an assembly line. There wasn't a test assembly line. Oh yeah, they had checks and validations that all their inputs and outputs were valid prior to ever putting the code into production. However, they had no mechanisms, of any kind, for…
Read More
23Nov 2016 by Grant Fritchey 5 Comments

PowerShell to Test a Query

DevOps, PowerShell, SQL Server, SQL Server 2016, T-SQL
So you want to do some tuning, but you're not sure how to test a query on it's performance. Not a problem. Here's a very rough script that I use to do some recent testing. This script to test a query is post #11 of the #enterylevel #iwanttohelp effort started by Tim Ford (b|t). Read about it here. The Script The goal here is to load a bunch of parameter values from one table and then use those values to run a query to test it. To do this I connect up to my SQL Server instance, naturally. Then I retrieve the values I'm interested in. I set up the query I want to test. Finally a loop through the data set, calling the query once for each value. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #…
Read More
06Jul 2015 by Grant Fritchey 4 Comments

But I Don’t Have a Test Server…

Database Lifecycle Management, DevOps, SQL Server, T-SQL
I frequently see statements on forums along the lines of "I don't have a test server, so I'm going to do something I've never done before directly on my production servers..." This is usually followed by questions along the lines of: But that's OK and I won't get fired, right? So how do I know if it worked? Is that dangerous? The replies are: No. You should be fired You won't By All the Gods! Yes! It's stupidly, insanely, I don't really like my production server, my data, my boss, or my job, dangerous. Yes. I understand. You're working for a not-for-profit, so you can't afford tons of new servers. You're looking at a 50tb production server of which, you can't make a copy. Your bosses just don't want to spend the…
Read More
30Dec 2009 by Grant Fritchey 1 Comment

Practice Your Restores

SQL Server
Steven Jones posted an excellent editorial today all about how your backups are only good if you know that you can restore from them. He couldn't be more correct. I posted the following thoughts in the comments, but I know not everyone reads the comments in articles & editorials. Although, if it's a good article, you should read the comments, especially on SQL Server Central. Frequently the discussion about the article can be as enlightening as the article itself. But I digress. Steve's point, pretty clearly stated but I'll repeat it, backups don't matter, restores do. I'm going to pile on to this point just a bit, because it can't be emphasized enough. Nothing is more important than verifying backups, except, verifying that you know how to run a restore. You're…
Read More
13Aug 2008 by Grant Fritchey 18 Comments

View vs. Table Valued Function vs. Multi-Statement Table Valued Function

T-SQL
About five years ago, I was checking an app before it went to production. I hadn't seen the app before then and a junior dba had worked with the developers designing and building the app. It didn't use a single stored procedure or view. Instead, it was built entirely of multi-statement UDF's. These UDF's called other UDF's which joined to UDF's... It was actually a very beautiful design in terms of using the functions more or less like objects within the database. Amazing. It also would not, and could not, perform enough to function, let alone scale. It was a horror because they thought they were done and ready to go to production, but no one had ever tested more than a couple of rows of data in any of…
Read More
20Mar 2008 by Grant Fritchey 1 Comment

Slick New Tool from RedGate

Tools
I have no intention of this becoming "tool of the day" or anything, but I can't help tooting the horn for a tool that I've been using a lot from RedGate. It's new and in beta right now, but it's going to be pretty good. It's a data generation tool called, are you ready, Sql Data Generator. Who saw that coming? Ok. I know. I'm not funny. Anyway, this is a great little tool. I've been using it to quickly slap large amounts of data into small sets of tables to test queries that I'm writing or for checking answers that I'm posting over at SQL Server Central. The tool lets you pick which tables and the columns inside those tables that you want filled with data. It has a…
Read More