Apr 13 2015

Azure SQL Database Firewall Settings

The new portal for managing Azure is pretty. I’m not sure I’m in love with it, but it’s pretty.

However, one thing that I have to access regularly is the firewall settings for my Azure SQL Database. I do demos from all over the place. I never know what my IP address is going to be. Rather than expose everything, I just set up whatever IP address I’m on and then remove it later. The old portal made this easy. The new one… not so much.

So, let’s get this down real quick. Assuming you connect to the new portal and go straight to your database, you’ll see this image showing you the database and the server it’s on:

DatabaseAndServer

You won’t see anything else that suggests FIREWALL. But, you can click on the server. When you do, you’ll see another panel open up to the right of the one you’re on. It’ll have an image like this:

Server

Still nothing that screams FIREWALL, but if you click on the little gear icon that says SETTINGS you’ll get yet another panel opening to the right that resembles this image:

ServerSettings

There she blows, Firewall. Click on that and you’ll see the standard screen for editing your IP address access into the firewall:

ServerSettingsFirewall

Just remember that after adding a new IP address to your firewall you must hit the save icon at the top of the screen, or you still won’t be able to access your database.

Apr 10 2015

I Am Grateful For

I decided in January that I would write regularly about people that I’m grateful for. Now it’s April. Oops.

The concepts are simple and science based. Positive emotions help your clarity of thought. Further, your moods and actions extend out through your network to the third layer. With that in mind, I want to think more clearly and the most direct path to positive thoughts and emotions being gratitude, I’m attempting to focus and publicize my gratitude by publicly sharing it through these blogs (in short, I’m trying to tune my brain like I would tune a query).

I am grateful for Tim Ford (b|t).

Tim is a great guy. Further, Tim can be patient with thick headed dorks (raising my hand). For example, among all the other stuff Tim does (Board of Directors for PASS, Editor at SQL Mag, Track organizer at Connections), he organizes and runs SQL Cruise. Yeah, yeah, cue the laugh track. You don’t get it. SQL Cruise changes peoples lives. I’ve seen it. Now, first time I went, I had a blast, but, I’m a bit thick, so I didn’t understand everything I was seeing. What I knew was that I didn’t tweet or write a blog post for a week, therefor I must not be doing my job. So, I asked Red Gate to not send me on any more cruises. We skipped them for a while. Last year, with a lot of prodding from Kevin Kline (another person I’m grateful for, but one blog post at a time), I went on the cruise again. Yeah, sun, rum, beaches… Oh, and people doing intense networking, redirecting their careers, going through intense, long form sessions with great speakers (and me)… Whoa! I spent some time talking with Tim and his wife Amy and the light finally dawned. Tim isn’t ONLY finding a way to get a bunch of us out on a boat so we can have fun (yeah, that’s part of it), he’s literally building and engineering opportunities for people to recharge and reset in preparation for radically modifying their career (not their jobs, people after SQL Cruise have careers). And it’s done the same thing for me. I’ve been personally reinvigorated by the work that Tim has done and I’m immensely grateful for that. But it’s not just SQL Cruise. Tim has helped me personally and professionally because he’s just that sort of person. He’s one of a long list of people that I’m regularly gobsmacked are willing to hang out with me.

Thank you Tim.

Apr 07 2015

Error: Unknown Property ismemoryoptimized

If you’re starting the process of moving your databases in Azure SQL Databast to v12, you need to do one thing. Make sure you have SQL Server 2014 CU5 or better installed on your machine with Management Studio (current list of updates). Otherwise, like me, you’ll be looking at the above error.

Just a quick blog post to help out. I saw this error, did a search, and didn’t hit a single entry telling me what to do. I started the install of CU6 (I needed to catch up on cumulative updates anyway). While that was happening, I went to Twitter and posted to #sqlhelp to see if anyone else had hit this. I got a response from Amit Banarjee pointing me to this MSDB blog on the topic, reinforcing the decision I had already made. I just wish they had posted the error along with everything else in the blog post. It would make things easier.

Apr 06 2015

Constraints and SELECT Statements

I’ve posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don’t affect execution plans do they?

Yes.

Let’s take this constraint as an example:

ALTER TABLE Sales.SalesOrderDetail WITH CHECK 
ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice 
CHECK  ((UnitPrice>=(0.00)))

That will ensure that no values less than zero can slip in there. We can even validate it:

INSERT Sales.SalesOrderDetail
        (SalesOrderID,
         CarrierTrackingNumber,
         OrderQty,
         ProductID,
         SpecialOfferID,
         UnitPrice,
         UnitPriceDiscount,
         rowguid,
         ModifiedDate
        )
VALUES  (60176, -- SalesOrderID - int
         N'XYZ123', -- CarrierTrackingNumber - nvarchar(25)
         1, -- OrderQty - smallint
         873, -- ProductID - int
         1, -- SpecialOfferID - int
         -22, -- UnitPrice - money
         0.0, -- UnitPriceDiscount - money
         NEWID(), -- rowguid - uniqueidentifier
         GETDATE()  -- ModifiedDate - datetime
        );

Will give me an error:

Msg 547, Level 16, State 0, Line 470
The INSERT statement conflicted with the CHECK constraint “CK_SalesOrderDetail_UnitPrice”. The conflict occurred in database “AdventureWorks2014″, table “Sales.SalesOrderDetail”, column ‘UnitPrice’.

Let’s look at a SELECT query now. If we run this:

SELECT  soh.OrderDate,
        soh.ShipDate,
        sod.OrderQty,
        sod.UnitPrice,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.';

The resulting execution plan looks like this:

Constraint

But, if I modify the query to look like this, adding an additional AND filter on the constrained UnitPrice column:

SELECT  soh.OrderDate,
        soh.ShipDate,
        sod.OrderQty,
        sod.UnitPrice,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.'
        AND sod.UnitPrice > $0.0;

You know what happens to the execution plan? Nothing. It stays exactly the same. The optimizer knows that in order to satisfy the query, it can safely ignore the change in the WHERE clause. In fact, you can look at the SELECT operator properties for the two different plans and note that while the Query Hash values changes, the Plan Hash value stays the same. The plans are identical.

With that knowledge, I’m going to modify the query to look like this, reversing the UnitPrice reference to look for data that violates the constraint:

SELECT  soh.OrderDate,
        soh.ShipDate,
        sod.OrderQty,
        sod.UnitPrice,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.'
        AND sod.UnitPrice < $0.0;

And now we have a new execution plan:

Constraint_scan

 

The optimizer recognized that there is no way that any data can be returned with the WHERE clause above because there is an enforced constraint (note the use of the WITH CHECK clause on the constraint). This completely changes the execution plan in every possible way. Now, instead of attempting to access the data, a Constant Scan operator is put in as a place holder for an empty result set.

To sum up, yes, constraints absolutely affect the choices made by the optimizer when those constraints would have an affect on the plan, even a SELECT query. Also, I would argue, this means that the use of enforced constraints can be a performance enhancer since the optimizer can make intelligent choices about how a given query is dealt with.

UPDATE: Fixed a typo that said the constraint prevented data that was equal to or less than zero. It’s only for data less than zero.

Apr 03 2015

Speaker of the Month: April 2015

One of my favorite events of the year is the SQL Saturday in Silicon Valley. They’ve had four of them and I’ve gone to three (had to miss last year, scheduling conflict). It’s a fantastic event and Mark Ginnebaugh (b|t) does a great job putting it together. In fact, this year, we got to listen to Ross Mistry and T.K. Rengarajan have a “fireside chat” for the keynote. For those who don’t know, Mr. Rengarajan is just a VP at Microsoft. Yeah, he simply runs the ENTIRE FLIPPING AZURE DATA PLATFORM. That’s all. They had a few demos and showed us unreleased code and new versions of SSMS not yet available publicly (including functionality around Query Store and execution plans, my little heart was going pitter-pat). Anyway, if you missed it, you missed it… So… Speaker of the Month.

I am choosing Vicky Harp (b|t) and her presentation, Care and Feeding of Your System Databases.

This was a very good presentation and Vicky is a very good presenter. She presented without a microphone, even though one was available. Sitting in the back corner, I could hear her very clearly. She made excellent eye contact throughout the presentation. She used her slides as guides for what she was going to talk about. She used Zoomit appropriately and frequently. I learned a couple of things during the presentation. Vicky demonstrated excellent knowledge of the subject matter, fielding every question. Largely, I’d say this was almost a class in how to present properly. And, I think it’s a good choice because system databases are kind of easy to ignore, yet, there’s important stuff there that Vicky addresses well.

Feedback I’d give Vicky, number one, repeat the question. She handled all the questions so well. I mean she really knows her stuff. But, she didn’t repeat the questions and some of the people asking didn’t have booming voices, so despite it being a small room, I was guessing context based on Vicky’s answer (which, was easy since her answers were so good). I also thought some of the examples were overly simplistic. We might have hit more of the material in more depth by skipping a couple of the really simple examples.

I understand Vicky will be recording this for SQL Server World Wide Users Group. Not sure how to get that, but it’s absolutely worth a listen.

Mar 31 2015

PASS Board Update: March 2015

Hello all,

It’s been a while since my last update. Sorry. I’ve just been traveling and presenting and working on books and this slipped through the cracks a bit. I’ll try to do better in the future.

Nothing major to report. As a board member I’ve been working primarily on two things. First, I’ve been polishing my goals for the Chapters Portfolio. I’ll publish those below. Next, I’m working on the budget for Chapters for next year. In between times, a lot of what I do, is work with Regional Mentors (RM) and Chapter Leaders (CL) on an individual basis. As much as the stuff that PASS as an organization is involved with is around technology, the PASS organization, especially Chapters, is all about people. And you know how people are. I’ve already had online meetings with the RMs and CLs in which I let them know what was going on with the Board and with my goals. The one point I made that I want to share is my own focus. I told everyone, and I’m telling you, that I see my role as a means of figuring out what the PASS organization can do for the Chapters rather than telling them what they need to do for the organization. Other than that, at these meetings, I’ve listened and taken notes. I want to know what’s needed by the CLs and the RMs to do their work as volunteers, and, where possible, make that work easier.

I also had the opportunity to have a meeting with the user groups of the UK (whether they were PASS chapters or not). This was an opportunity to listen to what their needs are and to understand whether or not PASS is meeting them. One of the most interesting things I learned revolved around Chapter Leaders and the Summit. There are a number of requirements for a Chapter to be in good standing with the PASS organization. And, if the Chapter meets all those requirements, then they can receive one free registration for Summit. Cool beans… unless you’re overseas where the cost of getting to Summit is much higher than here in the States. Suddenly, this seemingly HUGE benefit just isn’t that big. I’ll see what I can do about this one. Not sure what yet, but I’m thinking about it.

Here is a rough draft of the goals I’ve been working on:

PASS Growth

  • Create a pilot program that allows a regional email to be sent to PASS membership that highlights user group events within that region
    • Purpose is to measure the work involved in order to understand the needs for expanding this to all regions
    • Success is arriving at a measure of work that allows for creating plan for next steps
    • Run for three months to arrive at measure
  • Increase communication between Director for Chapters, HQ and Chapter Leaders by hosting quarterly meetings

Global Growth

  • Create interface to connect Speakers with Chapters
    • Purpose is to provide for Chapters to get in touch with Speakers using our existing local speaker list in SQL Saturday
    • Success is mechanism for Chapter Leaders to find Speakers within their region
    • 3 months to arrive at minimal functionality
  • Complete definition of Regional Mentor Annual Rating process
    • Purpose is to provide Chapters with mechanism for reviewing RMs
    • Success is a completed document that has been reviewed by CL and RM represenatives
    • Time frame requires completion by EOY 2015
  • Continue efforts to establish chapters in countries with no or few existing chapters
    • Targeting Baltics, Mexico, Saudi Arabia, Argentina

That’s about it for now.

Coming up I’ll be going to the PASS Business Analytics Conference (use the code BFFGF, that’s my Friends & Family discount, to get some money off the registration). I’ll write something up about that right after it’s over.

Mar 30 2015

Does the New Cardinality Estimator Reduce Bad Parameter Sniffing

parameter sniffing https://www.flickr.com/photos/nalbertini/6224914311/in/photolist-au5j9P-oovkHJ-j5hbfL-o2tQP4-5rjH9-o63z6j-e16cSr-o6eBtd-83UkyT-61apWt-xMWdz-6vUmcL-ojqHXt-krd1A8-bwvg6r-4wvJLh-pweDW5-5UzK52-NwvaB-4uQ6em-dZ7BLC-reoTH4-7RN6Bi-kr7ViJ-5BgntE-83a6sH-duXGC-6c3j6-Rphmr-4ceWRz-9pMBSV-4mz1HK-2kLC5w-6N2QGL-bcP2Qc-62i9cA-dPe47C-6ugVmj-59x1pS-6PYR8J-9QpqJK-83XraW-xJ3MS-mkE3Fq-5uJunA-oMMXp-rEX8bF-of5Fpr-3aJrZd-6WLBU7/No. Next question.

Although, that answer can be slightly, ever so slightly, nuanced…

Parameter sniffing is a good thing. But, like a good wine, parameter sniffing can go bad. It always comes down to your statistics. A very accurate set of statistics with very little data skew (some values that have radically more/less data than other values) and a very even distribution (most values have approximately similar cardinality), and parameter sniffing is your bestest buddy on the planet (next to a tested backup). But, introduce some data skew, let the stats get wildly out of date, or suffer from seriously uneven distribution, and suddenly your best friend is doing unspeakable things to your performance (kind of like multi-statement table valued user defined functions).

SQL Server 2014 has the first upgrade to the cardinality estimator since SQL Server 7. To get the best possible understanding of what that means and how it works, I can only recommend Joseph Sack’s white paper. It’s THE source of good information. All the new functionality within the estimator, for the most part, in most situations, will probably lead to more accurate row estimates. This means, in theory, less bad parameter sniffing. But, if you are benefiting from the old estimation engine, and in some cases you absolutely are, then you may see an increase in bad parameter sniffing. It comes right back around to your statistics and your data distribution.

So, does the new cardinality estimator lead to a reduction in bad parameter sniffing? It still mostly depends on your statistics. So, I can’t say, with certainty that the new cardinality estimator in SQL Server 2014 literally reduces bad parameter sniffing. In many instances, probably even most, the changes to the model are going to resort in better row estimates which means better choices by the optimizer. In some instances this will reverse and you may see pain, directly related to the changes in the cardinality estimator. But, overall, the fundamental behavior of parameter sniffing, again, a very positive thing, are unchanged. So, to a large degree, at least in my mind, no, the new cardinality estimator doesn’t reduce bad parameter sniffing because, most of the time, it’s due to the three causes mentioned before, out of date statistics, data skew and uneven data distribution. The new cardinality estimator doesn’t change these fundamentals, so, mostly, it won’t reduce bad parameter sniffing.

Mar 25 2015

Toshiba Portege Z301-A Review

Last year I purchased a Lenovo W530. Nightmares then ensued. Nice laptop if you just want to use it as a laptop, and only a laptop. Horror show if you need to present from it. So, I replaced it with an HP… which also gave me some troubles (try presenting with your laptop bricked in front of your boss AND 100 people), but that’s OK because I dropped it the next day (totally by accident, it had just worked great all day, I was happy with it, mostly). So there I was, laptop free and in need of something that would let me work, present, and be an all round road warrior. PLUS, I had just broke a laptop and was feeling bad that company would be replacing it. So I wanted to keep the costs down. After a lot of research, I decided to pull the trigger on the Toshiba Portege.

I couldn’t be happier.

This has been my best experience with a laptop since my Lenovo W520 (which just wore out from three years on the road). This thing is snappy fast to start up. It sleeps just fine. 16GB of RAM so I can run a couple of VMs (or one really fast VM). It’s got an SSD, so that’s it for quick I/O. The smaller footprint of the 13 inch monitor is great (and, I’m not at all bothered dealing with reduced screen real estate on the road). Heck, I’m typing this blog post on the thing right now. The battery life is awesome (I’ve been working in a hotel lounge for about four hours at this point and it’s still got another four hours to go. Oh, and the power brick is tiny so my overall weight when travelling has dropped.

Nothing is perfect. The touchpad tends to catch my palm and redirects where I’m typing sometimes. That is because I’m a little lazy with my hands sometimes while typing, so, to a degree that’s actually a plus, forcing me to watch dropping my hands. I think the keyboard is just a tad tight for my hands, but not too bad. I’m still not used to the function key locations.

But, I’ve been presenting all over the world with this thing on both VGA and HDMI connections. No issues. Ever. Of any kind. I’m in absolute love with it as a presentation machine. Oh, and did I mention it has both HDMI and VGA plugs, so no dongle in either direction. Three USB ports. It’s just great.

Screen quality and resolution is good enough for my tired old eyes. It’s also touch screen so I can scroll & zoom when reading stuff on it. I’m just happy with my laptop.

Now, if I can just keep from dropping it.

Mar 24 2015

Simple Parameterization and Data Types

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:

SELECT ct.*
FROM    Person.ContactType AS ct
WHERE   ct.ContactTypeID = 7;

This query results in simple parameterization and we can see it in the SELECT operator of the execution plan:

param

We can also see the parameter that was defined in use in the predicate of the seek operation:

predicate

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, here’s a fun little bit. I just searched the XML of the plan, the data type isn’t stored anywhere that I can see. But, if you query the plan cache, look what the text of the query is:

(@1 tinyint)SELECT [ct].* 
FROM [Person].[ContactType] [ct] 
WHERE [ct].[ContactTypeID]=@1

Now, in this case, this type of conversion doesn’t hurt performance in any way. We’re still getting an index seek. I also ran some tests with strings. The optimizer was setting them to VARCHAR(8000) and I got an implicit conversion that, again, didn’t hurt performance. But, I wouldn’t be completely shocked, if, at some point, that lead to scans. What I find it really interesting is, that the data type isn’t stored with the plan. I fully expected it to be there.

Mar 16 2015

How To Speak At SQL Saturday Events

The PASS SQL Saturday events are meant to be a place to grow the pool of speakers, provide a mechanism for the speakers to learn, and fulfill the PASS goals of Connect, Share and Learn. So, you’ve decided you want to start speaking at a SQL Saturday event. Cool. You went to the <Insert Large, Popular, SQL Saturday> event last year, so you submitted this year… and didn’t get accepted. Now what?

First, submit. You won’t get accepted if you don’t try.

SQL Saturday, especially the big, popular ones, may not be the best place to present for your very first time. In fact, with the large ones, you may not get accepted because people who already have a reputation are submitting to those (we all want to talk to big audiences and go to the good venues). So, start smaller. Find your local user group and speak there to get started. Better still, contact the person running the SQL Saturday event. Most of them are also running their local user group, or are associated with it, and most user groups are desperate for speakers. Go and speak at the organizers user group. That’s going to do two things for you. You’ll get some practice in a friendly space, and you’ll get your face and name in front of the SQL Saturday organizer. PASS maintains a list of local user groups that are associated with PASS as Chapters.

Next, go to the SQL Saturday web site. Find the one that you’re thinking of submitting to. Go to the Schedule page. You’ll see a list of people and topics they submitted. At the bottom, there is a place for suggested topics. Sometimes, but not always, you’ll see the stuff there that the organizer wants to see. They’re looking for a session on the VARIANT data type and you’ve written 33 blog posts on VARIANT in the last six months? GOLDEN! Submit under that topic. You can also try contacting the organizer to see what they’re looking for. Ask them, what topics they’re interested in presenting? Now, be sure you actually can present on that topic, but, here’s your in, giving them what they want.

Go to a smaller event. Yeah, presenting at the big event so you can hang out in the speaker room with <Insert Popular Speaker> would be cool. But, it really is hard to get into some of those events because everyone wants to do the same thing. So, start smaller. Some of the events just aren’t drawing lots of big name speakers. Personally, I don’t think that matters at all (people disagree on this topic), but, it’s an opportunity for you. These events need speakers. I know some where they’ve accepted every single session submitted, turning down no one (and I even presented a couple of extra sessions while I was there to help out). So, if you want to speak and, if you drove an extra hour or three you could speak, done.

Finally, your title and abstract do matter. The abstract needs to define a clear problem and solution that you’re going to present. The title… my opinion, let’s have a clear, descriptive title. I intensely dislike the cute and clever titles. Further, I don’t think they help people, especially new people, get accepted. I think they work well for the name speakers because it shows off their personality, and sometimes that’s what people are going for. You’re just getting started, tell us what you’re presenting.

None of this guarantees you get in, but it should all collectively help to get you in front of an audience at SQL Saturday.