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…
Nice to see most of you have managed to fight your way through the shoggoths outside to attend another lecture at the Miskatonic branch of SQL University. This will be the third and final part of the introduction to indexes lecture. Please, if you're going mad, step out into the hall. Our previous two lectures introduced the concept of indexes and then talked about two types of indexes, clustered and nonclustered. This lecture will cover the concept of statistics as they relate to indexes. If you followed the previous lecture then you know that indexes are stored in a Balanced Tree or B-Tree structure. You know that this storage mechanism is intended to provide fast retrieval of data. But, how can the query engine inside SQL Server know which index…
Welcome once more to the Miskatonic branch of SQL University. Please try to concentrate. I realize the whipoorwills singing outside the window in a coordinated fashion that sounds almost like laboured breathing can be distracting, but we're talking about indexes here. We left last class with a general idea what an index is, now it's time for some specifics. There are several different kinds of indexes, as we talked about last class. But the two you're probably going to work with the most are clustered, non-clustered. Each of these indexes is stored in a structure called a B-Tree, a balanced tree, not a binary tree. That's a very important distinction. A B-Tree is a double-linked list that is defined by the keys of the indexes on the top and intermediate pages, and…
I'm continuing to evaluate Confio's Ignite database monitoring tool. I've had it collecting data on a couple of production servers for about a week now. Based on what I've seen so far, it's looking like a pretty good piece of software. Breaking with the usual tradition, I'm going to talk about the things I'm not crazy about with the software, before I start singing its praises. The first thing, that I thought was missing, but is actually just hard to find, is the ability to look at the query information that Ignite collects, broken down by database. It looks like you should be able to get to it by looking at the Databases tab, but instead you have to first drill down into a time-period, then select specific databases within that time period,…