Wait Statistics on a Query

SQL Server 2012, SQL Server 2014, 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

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

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

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

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

Getting Started in a SQL Server 2017 VM in Azure

Uncategorized
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

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

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

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

Query Store Wait Statistics with sys.query_store_wait_stats

Azure, SQL Server 2017
The second best thing to questions that people ask is when I sit down to write a book. It's so easy to miss things in the day-to-day grind of doing work. Then, late at night, you're working on a chapter, so you read up on the documentation to ensure that you're not missing anything. Of course, then you find, yes, you are missing something. In my case, sys.query_store_wait_stats. sys.query_store_wait_stats. If you follow the link above, it'll give you what you need to know, but, I figured I'd provide a little more clarity because I think there are some pitfalls in using this data. I love Query Store (do a search to see all the exploration I've done with it). One of my favorite things is the time intervals. It breaks…
Read More