Search Results for: query+store

Execution Plan Shortcoming in Extended Events

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

Execution Plans and the GDPR

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

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

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

SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations

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

Database Fundamentals #15: Modifying Data With T-SQL

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

Extended Events and Profiler: XE Profiler

There's a war on in the SQL Server world. On the one side is Profiler (although, really, everyone uses Trace Events). On the other, the "new" (they came out in 2008 with a full GUI in 2012, so...) Extended Events. Lots of people have picked sides on this, including Microsoft. New Trace Events There are none. All the new functionality of every sort from Availability Groups to Query Store to R & Python, have Extended Events created for them. Trace Events, and the technologies supporting them in the form of Profiler, are a dead end. Don't fear. While Trace is on the deprecation list, there doesn't appear to be any fear of that technology being removed completely. At least it won't be removed in the foreseeable future. A future which,…
Read More

Database Fundamentals #14: Modifying Data Through SSMS

I've said it before, but I feel I should repeat myself. Using the SSMS GUI for data entry and data manipulation is not the preferred mechanism. T-SQL is the right way to manipulate the data in your database. For purposes of completion though, I will show the GUI methods in this blog series. Information doesn’t go into the database and stay there, unchanged, forever. Data is modified. This occurs because information changes, such as when a person marries and changes their name, or information was incorrectly entered, in which case you need to fix it, or just about anything else. You have to have a mechanism for modifying existing information. Modifying Data You start modifying data in the tables the same way you did the insert, by taking advantage of…
Read More

Database Fundamentals #13: Data Entry Through T-SQL

T-SQL provides lots of functions that help to make data entry through T-SQL much more powerful. Over time you won’t be typing everything into T-SQL directly as we’ll do here. You’ll be able to use stored procedures and parameters to automate the use of scripts. These will also be generated or used by applications. To  start using T-SQL, you need to open a query window. You can do this by right clicking on a database and selecting the “New Query” command from the context menu. This will open a new query window in the main window on your screen. This is basically just a big, open text box into which you can type commands. The INSERT Statement To add rows using T-SQL, the principal statement is the INSERT statement. The…
Read More

Database Fundamentals #11: Why Learn T-SQL

If you've been following along with the previous 10 Database Fundamentals blog posts, you have a SQL Server installed and a database with a table in it. You may have more if you've been practicing. Now would be the time to start adding data to the database, but first, I want to talk about the importance of T-SQL Why T-SQL? The way SQL Server accepts information is very different than most programs you’re used to using. Most programs focus on the graphical user interface as a mechanism for enabling data entry. While there is a GUI within SQL Server that you can use for data entry, and I will do a blog post on it, the primary means of manipulating data within SQL is the Transact Structured Query Language, or…
Read More