05Dec 2016 by Grant Fritchey 3 Comments

Presentations for SQL Server Beginners

Azure, PASS, PowerShell, Professional Development, SQL Server, SQL Server 2016
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
Read More
28Nov 2016 by Grant Fritchey 1 Comment

Database Clone

SQL Server 2016, T-SQL
There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It's moving all the data, not just once, but twice. You move the data when you export it and you move it again when import it (same thing with backup and restore). That makes these methods slow for larger databases. How can you create a database clone without moving the data multiple times? Don't Move the Data At All New with SQL Server 2016, expanded in SP1, and added to SQL Server 2014 SP2 is a new command, DBCC CLONEDATABASE. This is like a dream come true. The use is extremely…
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
22Nov 2016 by Grant Fritchey 19 Comments

SQL Server on Linux: Boring

SQL Server 2016
As a concept, SQL Server on Linux is thrilling. Due to this, the OS doesn't stand in the way of taking advantage of everything that SQL Server offers. Because we have Linux, we have opportunity for all sorts of new fun. I first saw SQL Server running on Linux about six months ago. The install was shockingly easy. I saw a few command line commands run and it all looked good. I didn't have the opportunity to set up my own full blown install then, but I do now. My Installation Today I did all the work necessary to get this thing running. I got a copy of Ubuntu Desktop and put it on VMWare Workstation in a virtual machine. That was a painless process. SIDE NOTE: It was a painless process…
Read More
21Nov 2016 by Grant Fritchey 5 Comments

Query Data Store Data

SQL Server 2016, T-SQL
The data in the Query Data Store is what makes all the magic happen. From collecting the aggregate performance metrics of a query to the various plans in use by that query to being able to force a plan, it's all controlled by the data within the Query Data Store system tables. The Question When I was presenting on this topic at the PASS Summit a few weeks ago, one great question came up (great question = answer is "I don't know"), well, I defaulted to an "I don't know" answer, but my guess was, "No." The question was: can you take a plan from one server, let's say a test server, export it in some way, and then import it to production? In this manner, you ensure that a…
Read More
18Nov 2016 by Grant Fritchey 2 Comments

Book Review: Marshmallow Test

Professional Development
Different people approach their career in different ways. My working assumption, all the time, is that I don't know enough and I'm not good enough. Don't get me wrong, I've got an ego (way too big of one really). I just know that there's room for improvement. It's one of the reasons I read books like the Marshmallow Test. The core concept is simple. You show a kid a treat, a marshmallow. You let them know that they can have that marshmallow right now, or, if they wait, they have two later. Kids who choose to wait, and succeed in waiting, generally do better in life, have better jobs, lower BMI, all sorts of things. It's about discipline and self-control, and even more importantly, delayed gratification. "Yeah, I can something…
Read More
09Nov 2016 by Grant Fritchey 1 Comment

Azure Data Platform Learning Resources

Azure
I know that more and more of you are moving more and more resources into the Azure Data Platform. In an effort to help you learn about all that Azure offers I created a GitHub repository of blogs, teachers, courses, etc. It's open to the community to update. New material has been recently added and there will be more and more all the time. Check it out. If you know of resources that should be listed there, please issue a branch pull and add them right in. This is your resource.
Read More
08Nov 2016 by Grant Fritchey 2 Comments

PASS Board Update: Post-Summit 2016

PASS
Monday I got in on Sunday and chose to have a small dinner with a couple of friends, quiet, preparing. Monday was a less hectic day than the others . The Board had the morning off, although Redgate had me go and give a session at an event. Monday afternoon was one of our three in-person board meetings. The minutes will be published soon. I was responsible for running the meeting. I also presented two topics, first, and most importantly, our current financial status. Then I presented the initial set of thoughts towards some SMART goals for Global Growth, which I will share once they are further developed . Monday evening I had two events I had to attend. First, as part of the Executive Committee, I attended the kick off dinner…
Read More
07Nov 2016 by Grant Fritchey 2 Comments

sp_executesql Is Not Faster Than an Ad Hoc Query

SQL Server, SQL Server 2016, T-SQL
This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL. You must parameterize your T-SQL because the lack of parameters in building up and executing strings is a classic SQL Injection attack vector. Using straight ad hoc T-SQL is an extremely poor coding choice because of SQL Injection, not because there is something that makes one method faster than the other. Yet, I see in performance checklists that you should be using sp_executesql over straight ad hoc T-SQL because it will perform faster. That statement is incorrect. Some Discussion Let me reiterate the caveat before we continue. I 100% advocate for the use of sp_executesql. This function is preferred over ad hoc SQL because, used properly (and isn't that usually one of the main problems,…
Read More
03Nov 2016 by Grant Fritchey 1 Comment

Reinforcing the Importance of Statistics on Row Estimate

SQL Server, SQL Server 2016, T-SQL
I recently wrote an introductory post about the importance of statistics. I just received a reinforcement on how important they are during my own work. Bad Estimate I hit a weird problem while I was setting up a query to illustrate a point (blog to be published next week). Let's take the basis of the problem and explain it. I wanted data with distribution skew, so I ran this query to find out if there was a wide disparity between the top and bottom of the range: SELECT i.BillToCustomerID, COUNT(i.BillToCustomerID) AS TestCount FROM Sales.Invoices AS i GROUP BY i.BillToCustomerID ORDER BY TestCount ASC; Sure enough, the bottom of the range returned three (3) rows and the top returned 21,551. If I then run a query to retrieve just a few rows…
Read More