04Jun 2018 by Grant Fritchey 5 Comments

Extended Events, the system_health Session, and Waits

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I advocate for, use, document, teach, and just downright love, Extended Events. They are so much better than the old Trace Events (aka, Profiler) that it's sometimes difficult to keep from just gushing. Let's talk about a common situation that you're going to run into on your servers all the time and how you can put Extended Events to work to help you, without actually doing any work at all. What's that? Be lazy and get rewards? Yes. The Extended Events system_health Session On your servers, any of them that are SQL Server 2008 or newer, right now, unless you've performed actions to prevent this, you're running the Extended Events system_health session. It's just happening, currently, on all your servers. Nothing you need to do about it at all. I'll…
Read More
16Apr 2018 by Grant Fritchey 4 Comments

Execution Plan Shortcoming in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events, they're just marvelous... until you capture an execution plan. Execution Plans in Extended Events Don't get me wrong. Capturing execution plans with Extended Events is the way to go if you're attempting to automate the process of capturing plans on specific queries on an active system. It's step two that bugs me. So, we capture the plan. Here's an example script. Captures all the various plans and the batch, puts 'em together using causality tracking: CREATE EVENT SESSION ExecutionPlansOnAdventureWorks2014 ON SERVER ADD EVENT sqlserver.query_post_compilation_showplan (WHERE ( sqlserver.database_name = N'AdventureWorks2014')), ADD EVENT sqlserver.query_post_execution_showplan (WHERE…
Read More
26Mar 2018 by Grant Fritchey 6 Comments

Query Store and Automated Cleanup

SQL Server 2016, SQL Server 2017
Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount of data currently in the store. I didn't know the answer, so we tried it. Things got a little weird. Bryan Hundley of Marathon Consulting asked the question, so Bryan, this blog post is for you. Automated Cleanup There are actually two kinds of automated cleanup inside the Query Store. First, you have a time-based cleanup. By default it keeps queries that have been accessed within the last 30 days. Anything older, it tosses. If you have the stale_query_threshold_days (all in sys.database_query_store_options) set to 0, it…
Read More
12Mar 2018 by Grant Fritchey 6 Comments

Execution Plans and the GDPR

SQL Server, SQL Server 2016, SQL Server 2017
What? Execution plans and the GDPR? Is this it? Have I completely lost it? Well, no, not on this topic, keep reading so I can defend myself. GDPR and Protected Data The core of the GDPR is to ensure the privacy and protection of a "natural person's" information. As such, the GDPR defines what personal data is and what processing means (along with a bunch of additional information). It all comes down to personally identifying (PI) data, how you store it, and how you process it. More importantly, it's about the right for the individual, the natural person, to control their information, up to and including the right to be forgotten by your system. OK. Fine. And execution plans? Execution Plans and PI Data If you look at an execution…
Read More
05Mar 2018 by Grant Fritchey 11 Comments

Every Single Execution Plan is an Estimated Plan

SQL Server, SQL Server 2016, SQL Server 2017
All the execution plans are estimated plans. All of them. There fundamentally isn't any such thing as an "Actual" plan. Where Do You Get Execution Plans? There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store. All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they're all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This…
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
11Dec 2017 by Grant Fritchey 2 Comments

Execution Plan Metrics and Units of Measure

SQL Server, SQL Server 2016, SQL Server 2017
Have you ever looked at the properties inside an execution plan and wondered what the units of measure were? I know I have. I also get the question pretty frequently about all sorts of properties. What does EstimatedAvailableMemoryGrant display it's units in anyway? For that matter, what the heck is an EstimatedAvailableMemoryGrant? Showplan Schema and Units of Measure The answer to those questions and a whole lot more is pretty easy to find. You just have to look to the ShowPlan Schema. Microsoft has all the schema's published, going back to 2005. The next time you're faced with a question such as, what are the units of measure of the MemoryGrantInfo: Or, what the heck is SerialDesiredMemory and why is it exactly matching RequestedMemory, you just have to go and…
Read More
04Dec 2017 by Grant Fritchey 12 Comments

Why I Use AdventureWorks for Demos

SQL Server, SQL Server 2016, SQL Server 2017
I know that when some people see AdventureWorks, their vision turns all red around the edges, their blood pressure spikes and they begin to foam at the mouth. I do understand. AdventureWorks, Microsoft's very old, near ubiquitous, sample database suffers from a lot off issues. What's The Matter With AdventureWorks? Let's start off with the general design of the schemas. It's broken up in odd ways. Production has a TransactionHistory and a TransactionHistoryArchive instead of Sales? That doesn't make any sense. What the heck is up with the general database design anyway? I mean, pick on one example. A person can have more than one phone number. Fine. But, what about people who share phone numbers like that ancient land line in my house that is only EVER used by…
Read More
27Nov 2017 by Grant Fritchey 6 Comments

Database Fundamentals #15: Modifying Data With T-SQL

Database Fundamentals, SQL Server, SQL Server 2016, SQL Server 2017
The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to use the GUI to get that done, it's not a very efficient mechanism. T-SQL is efficient. UPDATE The command for updating information in your tables is UPDATE. This command doesn’t work the same way as the INSERT statement. Instead of listing all the columns that are required, meaning columns that don’t allow for NULL values, you can pick and choose the individual columns that you want to update. The operation over-writes the information that was stored in the column with new information. In addition to defining the table and columns you want to update, you have to tell SQL Server which rows you’re interested in updating. This introduces the WHERE…
Read More
20Nov 2017 by Grant Fritchey 3 Comments

Get That Profiler Feel in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I know. You love Profiler. I hear you. You're wrong, but that's OK. Kidding... mostly. Unfortunately though, I think a lot of what passes for issues and problems with Extended Events is actually a lack of knowledge about how they work. Let's take an example and run with it. No Grid in Extended Events One of the pushbacks I hear about using Extended Events is that the Live Data GUI just doesn't have that neat Profiler grid output. Instead you see a list of events in the top pane and then you have to look at the details in the bottom pane. It looks like this out of the gate: You're right. That's a royal pain. That's it. Toss Extended Events. Back to Profiler. Well, hang on a second. Let's…
Read More