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…
Read More
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.…
Read More
26Feb 2018 by Grant Fritchey 8 Comments

GDPR: Your Hair Is Not On Fire

DevOps, Professional Development
Along with a lot of other people, I've been attempting to call people's attentions to the new General Data Protection Regulation (GDPR) that was created two years ago and becomes effective in May of this year. The regulation defines processes and practices around the privacy and protection of personal data of any EU citizen. While the regulation is defined by the EU, since it's applicable to the data of EU citizens, the applicability is anywhere that data may exist, even in other countries. So, the GDPR applies to you and your data if you have EU citizens data in your databases. Different countries have trade treaties in effect with the EU which will allow the EU to enforce this, even though you and your data are located somewhere else. None…
Read More
05Feb 2018 by Grant Fritchey 3 Comments

Wait Statistics on a Query

SQL Server, SQL Server 2016, SQL Server 2017
Wait statistics are a vital part of understanding what is causing your system to run slowly. Capturing them can be done through a variety of mechanisms from sys.dm_os_wait_stats (use this query for that) to sys.dm_db_wait_stats in Azure SQL Database. Those cover the system and the database, however, what about capturing query wait statistics on a specific query? Query Wait Statistics There was a time when this was actually kind of difficult. However, now we have a lot of different tools to capture query wait statistics. First up, and really, one of the best and easiest ways to deal with this, is to use the wait statistics captured by the Query Store. The only drawback to this method is that it is an aggregation of query wait statistics for the given…
Read More
29Jan 2018 by Grant Fritchey 1 Comment

Youtube Channel Update

Misc
Hello, I just wanted to take a moment to promote the work I've been doing with the YouTube Channel. I've published four videos in the last week. The most popular was an introduction to the GDPR: [embedyt] https://www.youtube.com/watch?v=uADi45AVUM4[/embedyt] I'll be talking about that quite a bit more in the coming weeks. I thought this video which shows you how to compare plans in SQL Server Management Studio would be more popular: [embedyt] https://www.youtube.com/watch?v=KHnZCtUAo44[/embedyt] However, this one showing wait statistics in execution plans seems to be getting more hits: [embedyt] https://www.youtube.com/watch?v=hTf82c6L4oE[/embedyt] Finally, my comparison between the mistaken missile alerts in Hawaii and database design fell over flat. No one was all that interested: [embedyt] https://www.youtube.com/watch?v=MYy9xmieFmM[/embedyt] I hope these videos are proving as useful to you as the blog posts I do…
Read More
23Jan 2018 by Grant Fritchey 10 Comments

You Need a New Hobby

Misc
Not sure who this is that is attempting to hack into my blog so desperately, but seriously, time for a new hobby: administrador 202.137.154.1 20 mins ago administrador 186.101.223.223 1 hour 41 mins ago administrador 115.84.92.197 3 hours 2 mins ago administrador 200.63.105.23 4 hours 20 mins ago administrador 137.59.225.11 5 hours 38 mins ago administrador 181.198.216.161 6 hours 56 mins ago admin 168.195.206.130 9 hours 36 mins ago admin 115.84.92.48 10 hours 55 mins ago admin 200.43.234.138 12 hours 14 mins ago admin 115.84.92.3 13 hours 33 mins ago admin 115.84.99.18 14 hours 48 mins ago admin 187.189.27.236 16 hours 7 mins ago admin 103.1.94.110 17 hours 26 mins ago admin 177.19.164.181 18 hours 44 mins ago admin 202.137.141.190 20 hours 5 mins ago admin 186.101.223.216 21 hours 25…
Read More
22Jan 2018 by Grant Fritchey 4 Comments

Database Fundamentals #16: Removing Data With T-SQL

Database Fundamentals
Deleting data from a table using T-SQL works quite a lot like the UPDATE statement. How it Works In the same way you supply the statement, DELETE, and then the table name. You’re not going to specify columns in any way because deleting data is all about removing a row. If you just wanted to remove the values in a column, you would use the UPDATE statement. Because of this, the only other thing you need for a DELETE statement is the WHERE clause. Just like with the UPDATE statement, if you don’t supply a WHERE clause, then the DELETE statement will remove all data in the table. Be very careful about using this statement. Make sure you’ve always got a WHERE clause. This example would delete all the rows…
Read More
16Jan 2018 by Grant Fritchey 4 Comments

Updated YouTube Channel

Misc
Hello all, I've recently updated my YouTube channel. I'm going to be posting a lot more videos up there. If you would like to take advantage of it, please go here and click on the subscribe button. I'll be covering pretty much the same type of thing there that I blog about here, Microsoft Data Platform, DevOps, Redgate Software, Database Lifecycle Management, Data Protection & Privacy, and other IT stuff as it takes my fancy. Also, I'm taking requests. If there's something you've seen on the blog that you'd like to see as a video, I'll see what I can do to help you out.
Read More
15Jan 2018 by Grant Fritchey 2 Comments

Getting Started in a SQL Server 2017 VM in Azure

SQL Server 2017
You say you're ready to dip your toes in the Azure ocean? Come on in, the water's fine! Oh, you want to really dip your toes. You're starting with Virtual Machines? OK. I guess. It's not where the real excitement is. You should be checking out Azure SQL Database and Azure SQL Data Warehouse and CosmosDb and... VMs. OK. Let's get you started. Set up Azure First Microsoft maintains seriously good documentation on how to work with Azure. I'm honestly blown away by how much information there is and how well written it is. In fact, everything I'm about to tell you is documented better, here. However, I'm going to give you the TLDR version. First, you have to have an Azure account. If you have an MSDN license, that…
Read More
08Jan 2018 by Grant Fritchey 3 Comments

Statistics Use, Extended Events and Execution Plans

SQL Server 2017
Query tuning ain't easy. Figuring out which index is getting used is one step, and generally simple, look at the execution plan to see which index is in use and whether it's being used in a SEEK or a SCAN. Done. However, when your index isn't being used, how do you tell how or why something else is being done? Well, that's largely down to row counts which brings us to statistics. Which Statistics are Used Years ago I was of the opinion that it wasn't really possible to see the statistics used in the generation of a query plan. If you read the comments here, I was corrected of that notion. However, I've never been a fan of using undocumented trace flags. Yeah, super heroes like Fabiano Amorim and…
Read More