A View Will Not Make Your Query Faster

SQL Server 2017
Twice recently, one on a blog post, and one in a forum post, I've seen people state, unequivocally, without reservation or hint of a caveat, that, "Oh, just put that query into a view. It will run faster." To quote the kids these days... Time for a rant. But First... Frequently when I post something that says, "Query Y runs faster than Query Red", I get responses from people saying, "Yeah, but if you run Query Red more than once..." or "Query Red was experiencing blocking..." or "You can't say Query Y is ALWAYS faster..." So, before we go down that road, a quick note on methodology. First, I'll be using Adventureworks because, reasons. Second, I won't run any of the following queries once. When doing something like this, I'll…
Read More

When Simple Parameterization…Isn’t

SQL Server 2017
I'm desperately working to finish up a new version of my book on Execution Plans. We're close, so close. However, you do hit snags. Here's one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it? Simple Parameterization The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this: [crayon-5b019e37b4af3262547209/] The resulting execution plan looks like this: The initial, graphical, pointer that we're seeing parameterization is right up there in the SELECT query. You…
Read More

Automating Automatic Indexing in Azure SQL Database

Azure
I've been in love with the concept of a database as a service ever since I first laid eyes on Azure SQL Database. It just makes sense to me. Take away the mechanics of server management and database management. Focus on the guts of your database. Backups, consistency checks, these easily automated aspects can just be taken care of. Same thing goes with some, not all, but some, index management. Azure SQL Database can manage your indexes for you. I call it weaponizing Query Store. Anyway, I needed a way to automate this for the book I'm writing. I couldn't find any good examples online, so I built my own. Queries in Need of Automatic Indexing Because I want this to be as simple and repeatable as possible, I'm using…
Read More

Execution Plan Shortcoming in Extended Events

SQL Server 2012, SQL Server 2014, 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: [crayon-5b019e37b7a5a402073368/] Cool beans. Does what I want, when I want, where I want. Excellent. Here is a captured…
Read More

Query Monitoring and the GDPR

DevOps
I've been reading the General Data Protection Regulation (GDPR) and discussing the ramifications of the beginning of enforcement with lots of people. The implications of it all are fascinating. The real serious issues remain primarily a business problem, with business defined solutions. However, there are technology issues that we need to think about. For example, performance metrics are going to be impacted by the GDPR. Private Data and Monitoring Queries First and foremost, let me say something I've said before. The vast majority of the focus around GDPR has to come from your business. Second, the bulk of your work and focus must be on ensuring core functionality in support of the GDPR. Third, the attack vectors and leaks for GDPR are not going to primarily be around something like…
Read More

Execution Plans and the GDPR

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

Every Single Execution Plan is an Estimated Plan

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

Adaptive Joins

TSQL
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

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

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