14Apr 2011 by Grant Fritchey 2 Comments

Performance Tuning: Start to Finish

PASS
The very first ever SQL Rally is taking place in a little less than four weeks in Orlando Florida. It’s going to be quite the event. There will be two full days of sessions on any number of topics. But before that all starts, there’s going to be a set of full day pre-conference seminars. These too are on a number of topics, but I’m hoping to draw your attention to just one, mine. I’ve put together a seven hour session on query performance tuning. I’ve tried to make it as complete as I possibly can. I’m going to cover the whole process from collecting data on your machines to identify where problems may be, to understanding the optimizer so you know how things work, to reading execution plans so…
Read More
06Apr 2011 by Grant Fritchey 4 Comments

SQL University: Index Usage

SQL Server, T-SQL
Greetings. Welcome once more to the Miskatonic University branch of SQL University. Does anyone know where to buy some camping equipment, cheap? I’ve been tagged to go an expedition to Antarctica and I need some cold weather gear a bit more substantial than my LL Bean boots. Evidently the last expedition found some caves in some mountains down there. Sounds like the perfect place to get away from all the crazy stuff that goes on here at Miskatonic. I mean, what could happen? Anyway, our last several talks have all been about indexes and indexing. One of the things that we haven’t talked about is how to tell if, how or when your indexes are being used. Starting with SQL Server 2005, and continuing to 2008 and R2, there has…
Read More
04Apr 2011 by Grant Fritchey 4 Comments

SQL Saturday #71 Wrap-up

PASS
It’s over. It’s finally over. This year, unlike previous years, Adam Machanic (blog|twitter) was very good about delegating the work. Which, is actually a mixed blessing. I did a lot more work this year than I have done in years past. Luckily, we had more help this year from, new MVP, Mike Walsh (blog|twitter) and Tom LaRock (blog|twitter). The team came together and pulled off the event with the help of a great set of volunteers. Of special note, Allen White’s (blog|twitter) wonderful wife, Cindi, stood at the front desk all day, and in other ways helped us organize and run the event. It would not have been the same without her. Thanks so much. We couldn’t have pulled it off without our vendors. Here is the rather substantial list.…
Read More
04Apr 2011 by Grant Fritchey 6 Comments

SQL University–Recommendations for a Clustered Index

SQL Server, T-SQL
Welcome, SQL University Students to another extension class here at Miskatonic University, home to the Fighting Cephalopods (GO PODS!). Never mind the stains on the floor, or the walls…or those really nasty ones on the ceiling. There was a… oh what did the dean call it… an incident last week when one of the students had a little accident after reading Die Vermiss Mysteriis one too many times. But we’re not here to talk about arcane tomes and unspeakable horrors today. No, today we’re here to talk about clustered indexes. SQL Server storage is really predicated around the idea of clustered indexes. Don’t believe me? Let’s list a few places that require a clustered index: Partitioning. A table in SQL Azure In order to create XML indexes What about the…
Read More
28Mar 2011 by Grant Fritchey 10 Comments

SQL Saturday #67 Wrap-up

PASS
Just… Wow. What an event. What a great group of people. I’m just so lucky to be involved with fantastic individuals like these. Thanks for having me out to play everyone, I really appreciated it. SQL Saturday #67 started for me with my second FreeCon (follow the link for details on the first one). Brent Ozar (blog|twitter) put together another great session where we spent a lot of time talking about blogs and blogging as well as swoops through other topics. We, by the way, is like a who’s who of great SQL Server people. I’m not going to post the list just in case everyone doesn’t want to be outed. However, I found the event extremely useful. I have a ton of notes and action items for myself and…
Read More
21Mar 2011 by Grant Fritchey 10 Comments

Communication

Object Relational Mapping, SQL Server, T-SQL, Tools, Visual Studio
It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It's a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap. Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic…
Read More
17Mar 2011 by Grant Fritchey 4 Comments

Where does slow performance come from?

SQL Server, T-SQL, Visual Studio
I have my opinions and experience, and I’ve no doubt you have yours. Paul Randal (blog|twitter) has put up another one of his interesting surveys to try to collect our opinions and our experience. You should run right over to here and vote immediately. Now that you’ve completed that, I’ll tell you what I think. Based on my experience (neither particularly broad, nor particularly deep, but there has been quite a bit of it), I’ve mostly seen problems in code. When talking about databases, the T-SQL code. When talking about apps, the application code. This is followed not too far back by really poor database structures and poor indexing strategies. I’m sure other people have seen other things, but these really are the areas where I’ve seen the most problems. The one…
Read More
14Mar 2011 by Grant Fritchey 1 Comment

ANSI Connection Settings

SQL Server, T-SQL
It’s reasonably well known that you can get different execution plans if you change the ANSI connection settings. But the question comes up, fairly often, how do you know what the settings are. It’s actually surprisingly simple. They’re stored right inside the execution plan, but they’re in one of the operators that most people ignore. Heck, I used to ignore this operator. Which operator is it you ask? Let’s find out. Let’s use AdventureWorks2008R2 (because I’m lazy). We’ll call one of the stored procedures there like so: EXEC dbo.uspGetBillOfMaterials @StartProductID = 0, -- int     @CheckDate = '2011-03-10 02:31:39' – datetime If you execute this with “Include Actual Execution Plan” enabled you’re likely to end up with the following execution plan: Don’t worry about the fact that you can’t really…
Read More
07Mar 2011 by Grant Fritchey 1 Comment

Execution Plan Stability

SQL Server
I’ve talked before about one of the primary things that the Query Optimizer team at Microsoft tries to avoid, regressions. Basically, they want a plan that worked well in SQL Server 2005 to work well in SQL Server 2008 R2. What’s more, they want everything to work well between service packs, updates and cumulative updates. Ever wonder how they do it? Well, they cheat. OK, that’s mean and not entirely accurate, but it gets the idea across. No, what they do is, identify when they have a breaking change, when they’ve got a special cumulative update or service pack that fixes some bad behavior, but that can cause plans to “break,” they wall it off. Note, in most cases, this “breakage” is actually a question of plans working correctly, but…
Read More