Natively Compiled Procedures and Bad Execution Plans

Uncategorized
I've been exploring how natively compiled procedures are portrayed within execution plans. There have been two previous posts on the topic, the first discussing the differences in the first operator, the second discussing the differences everywhere else. Now, I'm really interested in generating bad execution plans. But, the interesting thing, I wasn't able to, or, rather, I couldn't see evidence of plans changing based on silly things I did to my queries and data. To start with, here's a query: [crayon-5ba60a830be2b344542406/] And this is a nearly identical query, but with some stupid stuff put in: [crayon-5ba60a830be3c275843153/] I've change the primary filter parameter value to a VARCHAR when the data is NVARCHAR. This difference is likely to lead to differences in an execution plan, although not necessarily. If I load my tables up…
Read More

Differences In Native Compiled Procedures Execution Plans

Uncategorized
All the wonderful functionality that in-memory tables and natively compiled procedures provide in SQL Server 2014 is pretty cool. But, changes to core of the engine results in changes in things that we may have developed a level of comfort with. In my post last week I pointed out that you can't see an actual execution plan for natively compiled procedures. There are more changes than just the type of execution plan available. There are also changes to the information available within the plans themselves. For example, I have a couple of stored procedures, one running in AdventureWorks2012 and one in an in-memory enabled database with a few copies of AdventureWorks tables: [crayon-5ba60a830f412950477246/] The execution plans are obviously a little bit different, one going against in-memory tables and indexes and the other…
Read More

SQL Server 2014 and the New Cardinality Estimator

Uncategorized
Cardinality, basically the number of rows being processed by an operation with the optimizer, is a calculation predicated on the statistics available for the columns in question. The statistics used are generally either the values from the histogram or the density. Prior to SQL Server 2014, and going all the way back to SQL Server 7.0 (in the Dark Ages when we had to walk uphill to our cubicles through 15 feet of snow battling Oracle DBAs and Fenris the whole way), there's been one cardinality estimator (although you can modify the behavior somewhat with a traceflag in 2008R2 and 2012). Not any more. There's a possibility for really complex, edge-case queries, that you may run into a regression from this. You control whether or not you get the new…
Read More

Praise and a Suggestion for AlwaysOn

Uncategorized
One of my favorite additions to SQL Server 2012 is the Availability Groups, referred to as AlwaysOn. These things are awesome. It's basically shared nothing clustering at a database-by-database level. You can set up a series of secondary machines that will enable you to failover a database in the event of an outage. This is a huge advantage in high availability and disaster recovery scenarios. We're talking serious business continuity. Further, you can set up one of those secondary machines to allow for reads, meaning, you get a reporting database that allows you to offload read queries from a transactional machine. Another giant win. But wait, it gets better. Now, with the capabilities that are coming with Azure Virtual Machines and Azure Virtual Networks you can go even further. It's…
Read More

Does Encryption Affect Seeing Statements in Deadlock Graphs?

Uncategorized
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: [crayon-5ba60a8312748947008391/] Then I’ll execute things in the following order. From one connection this query: [crayon-5ba60a8312755383458181/] From a second connection, my stored procedure: [crayon-5ba60a8312759492835135/] Then, back on the first connection, this query: [crayon-5ba60a831275d054036160/] That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: [crayon-5ba60a8312760088495507/] Note, the statement offset is crazy, but that’s because it’s coming from an encrypted stored procedure. But the short answer is, yes you can (at least in this test).
Read More

AlwaysOn Failover Bug

Uncategorized
I just found a little bug in AlwaysOn. It’s actually not that big a deal, but it’s interesting. In a nutshell, if you have a table with an IDENTITY seed less than 1000, when you do a failover the table is reseeded to 1001. The steps to reproduce are posted on this Connect item. If you have feedback on this, please post it directly to the Connect item since I don’t think Microsoft reads my blog much.
Read More

SQL Server 2012 AlwaysOn & A Thorough Setup

Uncategorized
It’s surprisingly easy to set up the new AlwaysOn features. I’ve done it on VMs running on my laptop, from scratch, three times in the last few weeks. It’s easy because there are a set of validations that your run for the cluster and for the AlwaysOn setup that ensure you’re going to get a successful install… or do they? I hit a situation where it didn’t work correctly, so I thought I’d share it in case others ran into it. The setup is straight forward. I have network, contoso (yes, I’m using Microsoft training & documentation, it’s a beta, but you should see it available soon), with a domain controller and five servers all in a failover cluster. They passed the cluster test, so all five are hooked in.…
Read More

Changes to SQL Server 2012 Execution Plans

Uncategorized
I’ve been working with execution plans quite a lot in SQL Server 2012. There are a number of changes, most of them associated with new or different functionality. I had not noticed anything really fundamental until recently. I’ve become a huge proponent of always checking the properties of the SELECT statement. There’s so much useful information in there about what’s happened with the plan generation in the optimizer (not low level stuff, but the big picture items) that you should always be checking it first as a fundamental part of your plan examinations. Here’s an example from SQL Server 2008R2: You can see cool stuff like the size of the plan, the time it took to compile, the optimization level, the reason for early termination. Many of the properties on…
Read More

Installing SQL Server 2012 RC0

Uncategorized
Very short informational post. If you’re like me and you’ve been running the public Client Technology Preview (CTP3) of SQL Server Denali, uh, I mean 2012, then the news about the release of RC0 is pretty exciting stuff… until you realize that you’re going to have to tear down your virtual test machine, removing the CTP in order to prep for the install of RC0. I love playing with the toys, but I hate working on the toys, if you know what I mean. Great news. You can upgrade from CTP3 to RC0 in place. I know because I’ve done it. When you run the install and you get to the screen that asks what type of install you want, select Upgrade, as highlighted below: I know it doesn’t say…
Read More

Extended Events Data

Uncategorized
I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer magnitude of what you can do with extended events is just becoming clear to me. The interesting thing though is how much the basics are similar to trace. Similar mind you, not the same. For example, the best way to gather trace data is to output it to a file and then read the file into a table for later querying. It’s the same with extended events. There’s even a function that acts as a table: [crayon-5ba60a8319f5f189366530/] This can take advantage of roll-over files just like the old function used for traces. You can also provide offsets to read a sub-set of the file, which is cool. The event fields come…
Read More