It’s Not Always a SAN Problem

Tomorrow I’ll be doing a new webinar on how to identify performance problems related to storage in SQL Server. Sometimes, what looks like a SAN problem to SQL Server may just be related to missing indexes, or not enough memory, or something else that’s forcing SQL Server to do way too much I/O. In this session you’ll learn how to:

  • Identify when SQL Server is having storage related issues
  • Understand what’s happening from the perspective of the O/S
  • What performance counters are useless when you are on a SAN
  • How to talk to your SAN admin about performance

Join me and my friends at Embarcadero for this great webcast at 11:00am Pacific / 1:00pm Central / 2:00pm Eastern on Wednesday September 16.

SQL Saturdays are Not Your Personal PBS Pledge Drive

I’m a big fan of PBS (and NPR), I’m a member of our local station, but I am still annoyed as hell when I watch something purports itself to be a real show on PBS, and yet it is a pledge drive. I can almost always guess them to—it’s never the McNeil-Leher news hour. It’s usually a concert from some pretty solid artist who has broad appeal across the PBS viewing audience. Anyway, I’m rambling. Let’s get back to SQL Saturday—most of the speakers SQL Saturdays, Code Camps, and to an extent the PASS Summit are volunteers, we pay our own travel expenses, sometimes take time off of real jobs to be there. Sure some people work for vendors that fund their travel, and others (I fall into this category) have their own companies setup to make travel deductible, but no one is paying us to be there. I’m not complaining about that—it’s part of what makes SQL Saturday a great event. It’s an event run for the community by the community, and I hope it always stays that way.

I was at a recent SQL Saturday, where I happen to be extremely close with the organizer, and there was a speaker (from outside of the SQL community) who was making a ton of demands on the organizer. Wanted to be in a special room, wanted peak times (not for travel reasons, just for a larger audience), special equipment, etc. Listen, if there’s one person who has it much harder than the speakers, it’s the SQL Saturday organizer. You’re trying to manage 30 speakers, some of the most interesting (could you please read your GD email) questions from attendees, dealing with venue people and caterers, and you just don’t have time to deal with the needs of a single primadonna speaker. If you want a good way to never get invited to speak at that SQL Saturday again, I recommend submitting a list of the demands to the event organizer. No seriously, don’t do that, ever. If you do it in Philadelphia, one of our rooms will likely be under construction, but only during the hour you were scheduled to talk. But wait there’s more.

Figure 1 My SQL Saturday Rider

So after all of this happened before the event, I really wanted to see this session. After all, if this speaker had all of these special demands, they must be really amazing, right? The session was professional development—and while most of the advice was reasonably sound, there was a disturbing trend of the speaker answering questions with “it’s in my book” or “it’s in the DVD on my site.” My personal favorite was when someone asked for a copy of the slides (which speakers generally upload as a PDF or even PPT to the SQL Saturday website), the speaker responded “you can buy the DVD, they’re on there, they don’t pay us to come speak at these events, so I have to make some money somehow.” Yeah, buddy, we all paid to be here you jerk. There were a couple of other things that bothered me, like some mild insulting of the audience, but that’s beyond the scope of this. This felt like the informerical where they give a you a taste of a product, but if you want the real deal you have to call 1-800-SCAM-ME and give a credit card number and wait 4-6 weeks for your delivery. But wait there’s more.

So, I left a few minutes early, but it turns out the speaker was conducting cash sales of his book and DVD from the podium after his session. To the extent that it delayed the next speaker, which is always the classiest thing you can do as a speaker at a conference (it’s a good way to show people how important you are). At SQL Saturday’s we have these people that let us run the event—they are called sponsors and they don’t appreciate when someone who doesn’t pay for the right to sell stuff at the event does.

Those are the words of my friend Vicky, who works for a frequent SQL Saturday sponsor (and full disclosure, client of mine) Idera.

//platform.twitter.com/widgets.js

If you want to make sure you aren’t doing it wrong, just be sure to follow the Denny Cherry and Associate’s HR policy. Wheaton’s Law.

Why The Query Store is Awesome and You Should Immediately Use It

I haven’t been blogging enough recently—I’ve been busy writing, traveling, and speaking all around the world, and trying to get ready for my PASS PreCon on Power BI. It’s been a busy year—it’s barely September, and I’ve already requalified for my frequent flier status. Anyway, you don’t care about me. Let’s talk about SQL 2016—there are a myriad of new features and enhancements that are truly awesome. And for those of you who are all “old man yells at cloud” most of the new features and enhancements will help you in your on-premises environment. Anyway—there’s more time for that between now and whenever 2016 gets released. Let’s talk about the Query Store.

I got an email from one of my clients (these guys are brave and already live on 2016—they along with Microsoft have that much confidence in the code) about a query that was running poorly over the weekend (worse than in the older environment). A little bit about their environment—it’s largely a data warehouse type solution, with the goal of delivering data sets to their clients. In the upgrade to 2016, we did a rearchitecture that heavily leveraged clustered columnstore indexes, and took advantage of Availability Groups for scale out reads. So remember when one of your customers would email you about something that was slow over the weekend, and you would desperately scour the plan cache, possibly writing xQuery to look for the needle in the haystack of a query that was performing poorly?

 

Figure 1 Query Tuning in SQL 2014

 

Enter the Query Store. I opened up the database, and find the Query Store in Object Explorer.

 

I can see “Top Resource Consuming Queries”—if I click on that, a report will launch. (It will default to the last hour—click configure in the top right to change—the time interval and the resource you are curious about.)

 

 

Since I wanted to see queries with heavy logical reads from last weekend, I changed the resource type to logical reads, and the duration to “Last Week”. And then, I get this:

 

Well that was easy—I see the clear outlier of a query, and I see the index recommendation. I also get the execution plan—where I note the biggest expense is that SQL Server is doing a full scan of a fact table. Writing this post took me way longer than it did to isolate that query and propose a couple of options for tuning it to the client. The Query Store is awesome.

 

 

 

 

%d bloggers like this: