01Apr 2014 by Grant Fritchey 15 Comments

SQL Server 2014 New Defaults

T-SQL
Today, April 1st, 2014, marks the release of SQL Server 2014. There are tons and tons of great new methods and functions and processes within the product. We're all going to be learning about them for quite a while to come. One of the most exciting though is one of the changes to the defaults. In the past there's been a lot of debate around how best to configure your databases. What cost threshold should be set for parallelism, the max degree of parallelism, memory settings, auto growth, and all sorts of other settings affect how your databases work. But, Microsoft has finally done something smart. They've bowed to the pressure of hundreds and hundreds of DBAs, Database Developers and Developers around the world. They've finally done the one thing…
Read More
25Mar 2014 by Grant Fritchey 12 Comments

Save Money On Your Training Server

Azure, T-SQL
You can spend less money. Some of us are lucky. We work for very large corporations who can easily set aside a spare desktop or even space on a rack for a server on which we can train. Others of us are not as lucky. We work for smaller organizations that have to be more careful with their money. Not only do we not get the extra machine to train on, but our laptops could be weak things that can't run two or more VMs. In this case, how can you go about learning stuff? Spend your own money? Sure, it's an option. There are some very cheap servers available out there that won't cost you even $1000 dollars to set up. And for pretty cheap you can buy some…
Read More
21Mar 2014 by Grant Fritchey No Comments

PASS DBA Virtual Chapter Talk

Azure, PASS
I almost forgot to tell you about the Database Administration Virtual Chapter meeting next week, March 26th, 2014. I'll be doing a talk about query tuning in Windows Azure SQL Database. It's a talk I've given before (it was in the top 10 at the PASS Summit last year). Come find out why you'll need to tune queries in WASD, the tools you get, and the glorious fact that you'll actually be actively saving your business money by tuning queries! Click here now to register.
Read More
19Mar 2014 by Grant Fritchey No Comments

Query Tuning Near You

SQL Server, T-SQL
It really is so much easier to just throw hardware at badly performing databases. Just buy a bigger, faster server with more and faster disks and you can put off doing tuning work for another 6-9 months, easily. But, for most of us, sooner or later, our performance problems get so big or, we just don't have any more money to spend, and we're stuck. We have to tune the queries. And frankly, query tuning is a pain in the nether regions. But, after you've tuned queries 20 or 30 times, you start to recognize the patterns and it gets easier (never easy, just not as hard). But, if you haven't done it 20 or 30 times, what do you do? My suggestion, talk to someone who has done it…
Read More
18Mar 2014 by Grant Fritchey 2 Comments

Finding Mistakes

SQL Server, T-SQL
Ever had that moment where you start getting errors from code that you've tested a million times? I had that one recently. I had this little bit of code for pulling information directly from query plans in cache: WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@PhysicalOp', N'varchar(50)') AS OperatorName, RelOp.pln.value(N'@NodeId',N'integer') AS NodeId, RelOp.pln.value(N'@EstimateCPU', N'decimal(10,9)') AS CPUCost, RelOp.pln.value(N'@EstimateIO', N'decimal(10,9)') AS IOCost, dest.text FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY deqp.query_plan.nodes(N'//RelOp') RelOp (pln) ) SELECT qp.OperatorName, qp.NodeId, qp.CPUCost, qp.IOCost, qp.CPUCost + qp.IOCost AS EstimatedCost FROM QueryPlans AS qp WHERE qp.text = 'some query or other in cache' ORDER BY EstimatedCost DESC; I've probably run this... I don't know how many times. But... I'm suddenly getting an error: Msg 8114, Level 16, State 5,…
Read More
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