06Apr 2015 by Grant Fritchey 8 Comments

Constraints and SELECT Statements

Azure, SQL Server, T-SQL
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…
Read More
03Apr 2015 by Grant Fritchey No Comments

Speaker of the Month: April 2015

Professional Development
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…
Read More
31Mar 2015 by Grant Fritchey 6 Comments

PASS Board Update: March 2015

PASS
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…
Read More
30Mar 2015 by Grant Fritchey No Comments

Does the New Cardinality Estimator Reduce Bad Parameter Sniffing

T-SQL
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…
Read More
25Mar 2015 by Grant Fritchey 1 Comment

Toshiba Portege Z301-A Review

Tools
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…
Read More
24Mar 2015 by Grant Fritchey 3 Comments

Simple Parameterization and Data Types

SQL Server, T-SQL
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: 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…
Read More
16Mar 2015 by Grant Fritchey 8 Comments

How To Speak At SQL Saturday Events

PASS, Professional Development
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…
Read More
13Mar 2015 by Grant Fritchey No Comments

Speaker of the Month: February & March 2015

Professional Development
It's not a question of scheduling. I just haven't been to lots of community events in the last several months so that I can see community speakers and find one to give an award to. I've been trying. So, we're giving out two awards this month (my blog, my award, my rules). Unfortunately, neither one is going to a full-blown community speaker. Hey, not my fault. I'm trying. Anyway, on with the show. The first award goes to ALL the speakers on the SQL Cruise. That's Jes Borland(b|t), Jeff Lehman(L), David Klee(b|t), and Kevin Kline(b|t). Look, I get it, Tim Ford(b|t), the guy who runs SQL Cruise, goes after top speakers (and, somehow I get in too). But, until you've watched these people present, in the long form sessions that…
Read More
12Mar 2015 by Grant Fritchey 13 Comments

Monitoring for Timeouts

T-SQL
The question came up at SQL Rally, "Can you use Extended Events to monitor for query timeouts?" My immediate response was yes... and then I stood there trying to think of exactly how I'd do it. Nothing came quickly to mind. So, I promised to track down the answer and post it to the blog. My first thought is to use the Causality Tracking feature to find all the places where you have a sql_batch_starting without a sql_batch_completed (or the same thing with rpc calls). And you know what, that would work. But, before I got too deep in trying to write the query that would find all the mismatched attach_activity_id values that have a sequence of 1, but not one of 2, I did some additional reading. Seems there's…
Read More
09Mar 2015 by Grant Fritchey No Comments

Upcoming Events Where We Can Chat

Professional Development
I get around quite a bit. Next week I'll be visiting three cities in Germany talking to user groups in Frankfurt, Cologne and Munich on the 17th, 18th, and 19th of March, respectively. Here's the one link I can find for information. EDIT: Here's Cologne and here's Munich. Next, you can see me at the Redgate DLM Seminar in Silicon Valley on March 27th. It's a free event and there are a few seats left. Click here to register. The next day I'll be presenting at Silicon Valley SQL Saturday on March 28th. After that, I'm staying close to home to get to Boston SQL Saturday on April 18th. Then, one of the big events this year, I'll be travelling to my very first PASS Business Analytics Conference in Santa Clara, California,…
Read More