26Jun 2014 by Grant Fritchey 25 Comments

Passion

PASS
I know I tend to be overly passionate. It's something that has gotten me into trouble in the past. It's also probably a huge factor in the things I've been able to accomplish in life. I'm bringing it up at this time because I think passion is causing some conflict within the community around the Professional Association for SQL Server (PASS). On the 25th of June just past the announcements went out for the sessions accepted at the PASS Summit 2014. I found this stressful and exciting two ways. First, and for me personally, most importantly, because I had submitted several sessions and I desperately wanted to speak at the PASS Summit (I've spoken there every year since 2008 and I've made the Top 10 sessions two years in a row,…
Read More
25Jun 2014 by Grant Fritchey 2 Comments

The Utility of Execution Plans in Natively Compiled Procedures

T-SQL
I'm actually having problems identifying the utility of execution plans when working with natively compiled procedures. Or, put another way, why bother? I've posted a couple of times on natively compiled procedures and SQL Server execution plans. I've found the differences interesting and enlightening, but I'm seriously questioning why I should bother, at least currently. I'm sure there will be many changes to the behaviors of the natively compiled procedures and their relationship with execution plans. But right now, well, let's look at an example. I have three simple tables stored in-memory. Here's the definition of one: CREATE TABLE dbo.Address ( AddressID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 50000), AddressLine1 NVARCHAR(60) NOT NULL, AddressLine2 NVARCHAR(60) NULL, City NVARCHAR(30) COLLATE Latin1_General_100_BIN2 NOT NULL, StateProvinceID INT…
Read More
18Jun 2014 by Grant Fritchey 12 Comments

The Curse of Relational Databases

Professional Development
Let's face it, none of Information Technology is easy. Oh yeah, there are those few geniuses that have an absolute grasp over some small aspect of the stack, or those other geniuses that have a very shallow knowledge level, but understand the entire stack. But the stack itself, it's vast, deep, wide, utterly unfathomable. So what do you do? You cheat. You take shortcuts. You ignore things you don't like/understand/appreciate. And then there's all the things you just don't know. Or, you cheat another way, you get experts that have drilled down on a particular technology so that they'll provide you with the knowledge you need. Ah, but then you have to listen to them and what happens when your local genius (deep or wide) doesn't agree with your hired…
Read More
17Jun 2014 by Grant Fritchey 2 Comments

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: CREATE PROC [dbo].[AddressDetails] @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID JOIN dbo.CountryRegion AS cr ON…
Read More
10Jun 2014 by Grant Fritchey 3 Comments

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: --natively compiled CREATE PROC dbo.AddressDetails @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL…
Read More
06Jun 2014 by Grant Fritchey No Comments

Speaker of the Month, June 2014

Professional Development
It's not like I can't find plenty of great presentations here in the US, but, while I was over in Belgium at Techorama I checked out several of the presenters there. They were awesome. This was the first ever Techorama. It's a developer focused event, but there was stuff there for data-centric people too. They had a great international collection of speakers from all over. The venue was a movie theater which was a lot of fun to present in, although may be a little too comfy to watch presentations (I fell asleep in one, I sure hope I didn't snore). It was such a great event that I decided to pick my speaker of the month from there. I saw a bunch of very good presentations (even the one…
Read More
05Jun 2014 by Grant Fritchey 2 Comments

Natively Compiled Procedures and Execution Plans

T-SQL
The combination of in-memory tables and natively compiled procedures in SQL Server 2014 makes for some seriously screaming fast performance. Add in all the cool functionality around optimistic locking, hash indexes and all the rest, and we're talking about a fundamental shift in behavior. But... Ah, you knew that was coming. But, you can still write bad T-SQL or your statistics can get out of date or you can choose the wrong index, or any of the other standard problems that come up that can negatively impact all those lovely performance enhancements. Then what? Well, same as before, take a look at the execution plan to understand how the optimizer has resolved your queries. But... Yeah, another one. But, things are a little different with the natively compiled procedures and…
Read More
13May 2014 by Grant Fritchey 8 Comments

Add an Instance to SQL Server Azure Virtual Machine

Azure, SQL Server
How do you add an instance to your local SQL Server installation? You run the executable that you probably downloaded from MSDN or maybe from a CD. Works the same on an Azure VM right? Sure... but wait. Do I have to go and download the software to my VM instance? Let's assume that you're running one of the VMs from the Gallery, then, the answer is "No." Just navigate to C:\SQLServer_12.0_Full. There you'll find the full installation setup for SQL Server. And you're off and running... Until you realize that you don't have the Product Key for this thing. What happens when you get to this screen: You can look around all you want and you won't see a product key anywhere. At least no where that I could…
Read More
09May 2014 by Grant Fritchey No Comments

Carolina Cruise

Misc
I'm going to speak at three user groups in three days in North and South Carolina. Evidently this is known as the Carolina Cruise.  Here are all the details. And yeah, that's me and a bunch of Scouts down in the Florida Keys at Seabase. I'm really looking forward to this event. If you're in the area, let's get together and talk.
Read More
02May 2014 by Grant Fritchey 8 Comments

Speaker of the Month, May 2014

Professional Development
Whoa! Another month gone by already? I guess I better pick a speaker of the month then. I went to several events this month, so selection was difficult, getting to see so many great speakers. But, one stood out in my mind, partly because he's the least experienced speaker I've seen in quite a while. But his inexperience didn't show. Speaker of the Month for May 2014 is Andy Yun (t) and his presentation Every Byte Counts. The session was about using the right data types. You'd think this is self-obvious, but from the way Andy packed the room along with the attention and questions from the attendees, it's clearly a topic that needed attention. I really liked how he did a presentation on the problem space before showing his goals…
Read More