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
02Jan 2018 by Grant Fritchey No Comments

Snow, Heathrow Airport, Disaster Recovery

DevOps
I recently flew from Boston to the UK through the Heathrow airport. It just happened to be on the day that the UK got about 1.5 inches of snow (sorry, 3.8 centimetres, according to Weather Underground though, just 15mm, not sure about that). I spent a little more than four hours sitting on the runway at Heathrow before I was able to get out of the airplane. It was a frustrating and tiring experience, but it made me think about disaster recovery. Having a Disaster Recovery Plan Most of us have a Disaster Recover (DR) plan. Of course we do. Well, probably. Well, we take backups. Well, we occasionally take some backups. Well, we're pretty sure someone within the organization may have taken a backup once... somewhere... probably. Heathrow Airport…
Read More
26Dec 2017 by Grant Fritchey 8 Comments

SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations

Azure, SQL Server 2017
In Azure SQL Database for quite some time and now available in SQL Server 2017, Microsoft has put a lot of the knowledge they've gleaned from running more databases that any of the rest of us ever will to work with Automatic Tuning. Automatic Tuning The core of automatic tuning at this point in time (because I'm sure it's going to evolve) is the ability of the query engine to spot when a query has generated a new plan and that new plan is causing performance to degrade. This is known as a regression in the plan. It comes from bad parameter sniffing, changes in statistics, cumulative updates, or the big notorious one, the cardinality estimator introduced in SQL Server 2014 (it's been almost four years, I'm not calling it…
Read More