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

Targeted Plan Cache Removal

Azure, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
A lot of times you'll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they'll fix it by running the following code: [crayon-5b0198fc1ce1f041447132/] BOOM! Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing... Your fault for going nuclear. Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here's a little piece of code to help out: [crayon-5b0198fc1ce2e732787071/] Take a look at the documentation for FREEPROCCACHE.…
Read More

Generating Estimated Plan and the Plan Cache

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
Does generating an Estimated Plan cause that plan to be loaded into the plan cache? No.   What? Still here? You want more? Proof? Fine. Let's first run this bit of code (but please, not on your production server): [crayon-5b0198fc24ebe044168588/] That will remove all plans from cache. Now, let's take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the "Display Estimated Execution Plan" button on the toolbar): [crayon-5b0198fc24ecd375194303/] This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let's run another query: [crayon-5b0198fc24ed1971549157/] That's just an easy way to see if a plan_handle exists. If a plan was stored in cache for this query, I should see a result. I don't. Now, you might say that this is because…
Read More

Simple Parameterization and Data Types

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But... Let's take this example. Here's a very simple query: [crayon-5b0198fc27500627014963/] This query results in simple parameterization and we can see it in the SELECT operator of the execution plan: We can also see the parameter that was defined in use in the predicate of the seek operation: Hang on. Who the heck put the wrong data type in there that's causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I'll get two more plans and I can see them all by querying the cache. But,…
Read More

Saving Execution Plans on Azure SQL Database

Azure
In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it's pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can't think of at the moment? To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most…
Read More

How to Drop One Plan from Cache

SQL Server 2008, SQL Server 2012, TSQL
While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is: [crayon-5b0198fc2a099288831335/] Not much to it is there? The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don't need to drop the entire procedure cache as people so frequently do. The…
Read More

Changing DB_CHAIN Can Clear the Plan Cache

SQL Server 2008, TSQL
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. [crayon-5b0198fc303ab323125067/] The script is almost self-explanatory. I want to point out that I put in one cross-database query to imply the possibility of cross-database ownership or access, but also to show that regardless of what’s referenced, all queries from this database are flushed from cache. The first of the simple DMO queries returns two rows, the second returns no rows because everything is out of the cache because…
Read More

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

SQL Server 2005, SQL Server 2008, TSQL
Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: [crayon-5b0198fc3069d906577894/] And as ad hoc TSQL goes, that one is actually some what clean. They get a lot worse. But, in this case, each and every time the @value variable is changed, you're going to get a different execution plan. That's because the full text of the query is used to determine if the existing plan will…
Read More

Procedure Cache and Dynamic Management Views

SQL Server 2005, SQL Server 2008, TSQL
I'm just going through the chapter on the procedure cache in the new book and I'm having a blast playing with the dynamic management views and functions that let you access the procedure cache. It's just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints.…
Read More