30Jun 2014 by Grant Fritchey 11 Comments

The Curse of Working With A DBA

DevOps, Professional Development
I no more than finished my rant from last week than I started thinking about all the reasons why a healthy chunk of the reasons that developers want to bypass relational database is not the horror of the relational database itself, although, that's there. No, a very large reason why is the DBA. We're on a blog called The Scary DBA. I earned that title, well sometimes. Sometimes I got it and I wasn't sure why. However, it's perfectly in keeping with how many people view their database administrators; grumpy, obstructionist, slow, difficult, control freak, etc.. There are even jokes about it, "What's the DBAs favorite word? No!" And for those answering "It depends" that's two words. I understand why. In large part it's that phone in your pocket (used…
Read More
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