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…
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…
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…
Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment. I'm going to create a simple stored procedure: CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT) AS SELECT soh.AccountNumber, sod.LineTotal FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @SalesOrderID When I create this procedure and run it, you can see the general performance of the query being run…
Or, another way to put it, in most cases, shooting yourself in the foot. I was not aware that the cumulative update for SQL Server 2008 back in June included a switch that allows you to turn parameter sniffing off within SQL Server. Thanks to Kendra Little (blog|twitter) for letting me know about it (although she let me know by "stumping the chump" during my lightening talk at the Summit, thanks Kendra!). When I first saw the switch, I thought about the places where switching off parameter sniffing could be helpful. But, as I thought about it, the more I realized that this was an extremely dangerous switch. Why? Because, most people only ever hear about parameter sniffing when they run into a problem. Someone says "Parameter sniffing" and you see people cringe.…
Today is Dr. Dewitt. The ballroom, where the keynotes are held, is filled with extra chairs. The Summit organizers expect extra attendance today, and well they should. Dr. Dewitt was amazing last year. I suspect this year will be more of the same. Rick Heiges is introducing the day (waiting for Dr. Dewitt). Lynda Rab is leaving the board. Sad. I started volunteering for the PASS organization working for Lynda. She's great. The new board members are Douglas McDowell, Andy Warren and Allen Kinsel. The spring SQL Rally event was announced. I'll be presenting a full day session on query performance, Query Performance Tuning, Start to Finish. Look for (a lot) more blog posts on this. The Summit next year has been moved to mid-October. WHOOP! This is great because…
Ted Kummert is still talking. For the cloud, of course, they're talking about SQL Azure. Microsoft really is throwing themselves into the cloud, completely. The emphasis is that they offer both a cloud and an on-premises solution. I don't mind saying, I'm still trying to get the full business proposition for an old school, fat, business like the one I work for. What should we be doing with the cloud. I just haven't seen the magic. I see where smaller businesses, or start-ups, or temporary surge capacity for businesses that may have that type of thing can use the cloud, but... traditional work, it just doesn't seem to jive yet. We're going to see some made-up scenarios for how Azure can manage Contoso Bikes. He shows how the report can…
 Quote: "Database stuff, all this programming stuff, is easy. Anyone can do it. That's why everyone in the company has sa privileges." For nine months, I worked in an environment where everyone, from developers to QA to the sales people to the receptionist, had SA privileges. You know what? DBA skills are necessary. I speak from the point of view of someone that has had to recover a server after a salse person helpfully "cleaned up the temporary stuff on the server" by dropping tempdb, causing a late deployment for a client. I speak from the point of view of the guy who kept a window open on his desk with the database restore script ready to run, all day long, because of "accidents" that stopped development until I could get the…
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…
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…