17Mar 2014 by Grant Fritchey 27 Comments

SQL Saturday: You can’t have it all.

PASS
SQL Saturday's are awesome! Let's get that clear up front. The organizers of SQL Saturday events are glorious individuals. Let's get that clear too. I want to be up front about those things because, well, I'm going to be critical. First though, I want to establish my bona fides for what I'm about to say. I helped organize two SQL Saturday events and two other local events before those. I also help Red Gate Software run half-day seminars all over the country. So, I have some idea what goes into the organizational side of these things. I've presented at eleven SQL Saturday events in just the last year. I'm on the schedule for, I think, 6 more between now and August. So, I think I have some idea what it's…
Read More
11Mar 2014 by Grant Fritchey 4 Comments

sp_updatestats Is Not Smart

SQL Server, T-SQL
No, I don't mean the use of sp_updatestats is not smart. It's a fine, quick mechanism for getting statistics updated in your system. But the procedure itself is not smart. I keep seeing stuff like "sp_updatestats knows which statistics need to be updated" and similar statements. Nope. Not true. Wanna know how I know? It's tricky. Ready? I looked at the query. It's there, in full, at the bottom of the article (2014 CTP2 version, just in case yours is slightly different, like, for example, no Hekaton logic). Let's focus on just this bit: if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0))) The most interesting part is right at the front, @ind_rowmodctr <> 0. That value is loaded with the cursor and comes from sys.sysindexes and the rowmodctr column…
Read More
07Mar 2014 by Grant Fritchey 3 Comments

Speaker of the Month, March 2014

Professional Development
This never gets easier. I was able to attend a bunch of sessions in the last month from a number of speakers that I'd never seen before. A lot of them were good, very good. In fact, I'd go so far as to say I think the general level of speakers within the SQL Server community is improving. Which means we'll all need to up our games. I also saw several that I've seen before because I always learn from them. In short, my cup runneth over. Anyway, the person I picked this month, well, I'd never seen him present before. But, I have hung out with him. He's got this incredible, fast, sharp wit and he'll protect you from dangerous objects in orange. I'm picking Mark Vaillancourt (b|t) and his…
Read More
27Feb 2014 by Grant Fritchey 2 Comments

Let’s Talk Query Tuning

Professional Development, SQL Server, T-SQL
I spend quite a bit of time writing about query tuning on this blog. I've written (re-written and am actively re-writing) books on query tuning. But what I like most is talking about query tuning. I love giving sessions at various events on different aspects of query tuning, but, what I like the most is spending a whole day, trying to do a complete brain dump to get as much information out there as possible. Sound attractive? Then I've got a great deal for you. Come to Louisville on June 20th, 2014. We will talk query tuning at length. You have a specific question? Let's get it answered. Then, the next day, we can all go to SQL Saturday 286 there in Louisville to get more learning and some serious…
Read More
26Feb 2014 by Grant Fritchey No Comments

SQL Intersection, Spring 2014

Azure
I am terribly jazzed to be involved with this amazing event, SQL Intersection. It's featuring some truly amazing speakers presenting on important topics. It's being held here on the East Coast, right near the Mouse, the Duck and Dog. This is one of those conferences you need to get to. Check out the lineup. That is some of the smartest, most capable people I know. I'm quite humbled to be on the list with them, so I'll do my level best to deliver good content. Look at the sessions. While I don't know precisely when SQL Server 2014 is coming out, I'm sure it's real soon, so this will be a great place to get a leg-up on understanding what this new set of technology offers, or just learn more…
Read More
25Feb 2014 by Grant Fritchey 20 Comments

Approachable? Sometimes.

DevOps, Professional Development
Deservedly so, I got called out for a bit of attitude I displayed in a recent blog post: Time for a Quick Rant. Steve Hood took the general attitude of "Do this or I will beat you" to task in his blog post The Approachable DBA. Granted, my little rant was primarily done tongue wedged immovably in cheek. But I was reflecting an attitude that the gods know I'm guilty of and that I think way too many DBAs are guilty of. Actually, I think developers are just as guilty. And sysadmins, san admins, support desk people, QA, the report writing team, those people supporting the data warehouse certainly, the SharePoint team, and that poor lady who got stuck being the Deployment manager. That attitude? I don't think you heard…
Read More
18Feb 2014 by Grant Fritchey 14 Comments

The CASE Statement and Performance

SQL Server, T-SQL
In case you don't know, this query: UPDATE dbo.Test1 SET C2 = 2 WHERE C1 LIKE '%33%'; Will run quite a bit slower than this query: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 LIKE '333%'; Or this one: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 = '333'; That's because the second two queries have arguments in the filter criteria that allow SQL Server to use the statistics in an index to look for specific matching values and then use the balanced tree, B-Tree, of the index to retrieve specific rows. The argument in the first query requires a full scan against the index because there is no way to know what values might match or any path through the index to simply retrieve them. But, what if we…
Read More
12Feb 2014 by Grant Fritchey 1 Comment

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
07Feb 2014 by Grant Fritchey No Comments

Speaker of the Month, February 2014

Professional Development
I didn't get out to many events in January, so I was somewhat limited in the pool of presenters that I could choose from. Luckily for me, that pool primarily consisted of the entire Caribbean since I was on the SQL Cruise. There I got to see some of the best in the business doing serious teaching (and networking, and water slides, and rum, and beaches, and rum). I could easily cop out, cheat, and name the Cruise as speaker of the month, but I have not yet sunk that low (plus, the rum is gone). Every speaker I saw was great too. Tim Ford doesn't let just anyone present in his watery venue. So, I could pick from any of them and be 100% right. But, I had to…
Read More
06Feb 2014 by Grant Fritchey No Comments

Thank You!

Azure, PASS
I am humbled and honored (and more than a little horrified) to be on this list of the Best of PASS Summit 2013. I mean look at those names. Every single one is a person I look up to and respect and learn from constantly. How I made a list like this... well, thanks. I appreciate the support and kindness that was shown at the PASS Summit when you filled out your evals. Oh, and while I realize intellectually and SQL skill-wise he totally kicks my behind... Neener, neener Conor. You're in the DBA track and I'm the only one in the top 10 in the Cloud track. By the gods, I'm going to pay for that, but it'll be worth it.
Read More