04Oct 2010 by Grant Fritchey 1 Comment

How to Tell if Execution Plans are Reused

SQL Server, T-SQL
I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I've seen a lot lately is along the lines of "how do you know if an execution plan is being reused." Since compiling an execution plan can be an extremely expensive operation, it's worth your time to understand how well a given plan is getting reused. If you've seen me present, I'll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren't getting plan reuse it would have been an even worse system than it was. There are…
Read More
27Sep 2010 by Grant Fritchey 3 Comments

Parameter Name Size And Performance

SQL Server, T-SQL
I saw an odd statement the other day, "The size of the name of the parameter does not affect performance." My first thought was, "Well, duh!" But then, I had one of those, "Ah, but are you sure" thoughts. And you know what, I wasn't sure. If size of the parameter name did affect performance, I figured, the one sure place where that would be evident is in the size of the execution plan. Right? I mean, if there was an impact on memory, and hence on performance, that's probably where you'd see evidence of it. I wrote two queries: DECLARE @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance int SET @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance = 572 SELECT soh.SalesOrderID ,sod.SalesOrderDetailID FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance DECLARE @v int SET…
Read More
20Sep 2010 by Grant Fritchey 2 Comments

SQL Saturday 46 (#sqlsat46) Recap

PASS
I was privileged to be able to attend and present at SQL Saturday 46 in Raleigh, NC, this last weekend. It was a great collection of people presenting some amazing stuff. I want to say, right off, I think this is the best SQL Saturday event I've been to. I say that despite the fact that I've helped put on a SQL Saturday. I also say that despite the fact that my sample size on SQL Saturday's is fairly low. I've only been to three (including the one I put on). You have to understand, the people who put on #sqlsat46, the Triangle SQL Server Users Group, did an absolutely outstanding job. They had clearly done the early work of getting sponsorships and organizing. This weekend, all that early work…
Read More
07Sep 2010 by Grant Fritchey 1 Comment

Links From Twitter

Misc
Normally, I try to stick to posting technical info or community stuff on the blog, but there were a couple of links from Twitter today that are too good not to share. First, an interesting take from Tom LaRock on the issue around the lack of quality DBA's. He suggests that it's actually a lack of quality managers. Go read it & comment there. Second, this is Not Safe For Work (NSFW). Please, please please understand that before you click on this link. It's a hilarious discussion about NoSql. Put on headphones & give it a listen. Back to your regularly scheduled blog posts...
Read More
03Sep 2010 by Grant Fritchey 5 Comments

Recursive Hiearchies in Reporting Services

Uncategorized
I am not a Reporting Services guru and nor do I play one on TV. I am however forced to be all things Microsoft Data where I work. So I frequently find myself stretching way beyond my abilities. I just had to get a report running that feeds from a web service and has a recursive hiearchy with customized aggregation on multiple fields with drill down to a different set of details. Yeah, through the internet I can see the SSRS monsters rolling their eyes at the ease of this task. But for us mere mortals it was work. Since I spent so much time learning how to do it, I thought I'd share. XML as a Source First, because we have a very paranoid (and appropriately so) group of…
Read More
30Aug 2010 by Grant Fritchey 4 Comments

Upcoming Presentations: #24HOP & #SQLSAT46

PASS
Blogging has been a bit quiet of late. That's because I've been spending a lot my spare time getting ready for presentations that I have to give. Two of them are in about two weeks. First, and this one is going to be a big deal, is 24 Hours of Pass: Summit Preview. At the PASS Summit this year I have two spotlight sessions, both on tricks and tools for tuning queries, one on using execution plans and the other on using DMVs. Since the 24HOP presentation is supposed to be a lead-in to the PASS presentations, I decided that before you started tuning queries, you need to know which queries to tune. The presentation is titled: Identifying Costly Queries. I understand there are already nearly 2000 people registered. Let's…
Read More
23Aug 2010 by Grant Fritchey 4 Comments

PASS Elections

PASS
There are a lot of things I'd like to say about the PASS elections. I was on vacation last week when the news about the slate of candidates broke. I've gone through a lot of emotions thinking about this, which is funny, because, really, what does it matter? But, because of the people, and let's face it, PASS is nothing without the people involved, it does matter. But, I've decided to say as little as possible because I'm not convinced I can add constructively to the conversation and that is what is most needed at this time. First, I want to endorse a few candidates. The people I'm going to endorse, I'm endorsing because I know them personally. I've worked with them, I've talked to them, I've read their stuff.…
Read More
11Aug 2010 by Grant Fritchey No Comments

Viva Las Vegas!

SQL Server, T-SQL
I won't be going to Las Vegas, but I will be presenting to the Las Vegas SQL Server Users Group, S3OLV. The sesssion will be "Introduction to Execution Plans." Please swing by if you're in the area. I'm not sure if they'll make the LiveMeeting available to the public or if they'll record the session. But if they do, please attend that way too.
Read More
11Aug 2010 by Grant Fritchey 3 Comments

24 Hours of PASS: Summit Preview

PASS, T-SQL
Registration is open for the second 24 Hours of PASS this year. This one is going to be a preview of the Summit itself. So all the sessions are tied, in some manner, to sessions being given at the summit.Here's a link to go and register. I'm very excited to be able to say that I'll be presenting in this 24HOP. One of my presentations at the Summit this year is Identifying and Fixing Performance Problems Using Execution Plans. It covers pretty much what it says, methods for fixing performance problems by exploring the information available within execution plans. But, how do you know you have a performance problem? That's where my preview session comes in. Identifying Costly Queries will show you several ways to gather metrics on your system so that you…
Read More
01Aug 2010 by Grant Fritchey 6 Comments

A Milestone

Uncategorized
I just passed 100,000 views on the blog. That's from 400 posts over a period of time starting in March of 2008. You guys have posted 1129 comments. The average views per week went from 39 in 2008 to 160 in 2010. I realize others out there are hitting 100,000 a week, but for some of us small time bloggers, this is a big deal. I just wanted to say thanks for stopping by and I hope my attempts at understanding SQL Server, PowerShell, SCOM, spatial data, Visual Studio, ORM and whatever else I've posted about has been helpful in some way.
Read More