02May 2011 by Grant Fritchey 3 Comments

I’ve got 99 Problems, but a disk ain’t one

nHibernate, Object Relational Mapping, SQL Server, T-SQL, Tools
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…
Read More
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 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
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
17Feb 2011 by Grant Fritchey 1 Comment

SQL Rally

PASS
I believe I’ve been far too quiet about this event. In May, in Orlando Florida, there will be a two day SQL Server conference called SQL Rally put on by PASS. This is a somewhat unique event since the sessions have all been chosen by the community. That’s right, a series of votes was held online (and if you missed it, you weren’t paying attention) so that all of you were able to decide which sessions you’d most like to see. I’m bringing this up because, now, it’s up to you to register and go. Did I mention the pre-con? No? Well, there will be a one-day pre-conference seminar. There are multiple sessions of excellent material at the pre-con. These were also voted on by the community. One of them…
Read More
06Dec 2010 by Grant Fritchey 12 Comments

Review: Idera SQL Doctor

SQL Server, T-SQL, Tools
Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over. SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong. All that sounds very clinical, just laid out like that. But the fact…
Read More
22Nov 2010 by Grant Fritchey 48 Comments

Do Foreign Key Constraints Help Performance?

SQL Server, T-SQL
Most people are very aware of the fact that having a foreign key constraint in place on your tables adds overhead to inserts and deletes. And many people believe that there is no benefit to foreign keys beyond referential integrity (which, the application can handle perfectly well, right?). But is that an accurate statement? Here's the basis for our investigation, a query in AdventureWorks2008R2: SELECT p.LastName + ', ' + p.FirstName AS 'PersonName' FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; This query results in the following execution plan: I know that is an ugly query and an ugly query plan, but bear with me for a moment. Do you…
Read More
18Nov 2010 by Grant Fritchey 18 Comments

Reason for Early Termination of Statement

SQL Server, T-SQL
Wouldn't you like to know why the optimizer stopped tuning the execution plan you're looking at? It's actually possible and simple to get this information. I talked about this a little more than a year ago, but I left out some information that might be useful. Let's take a very simple query: SELECT * FROM Person.Address AS a; This generates a simple execution plan, in fact a trivial plan: I know that this is a trivial plan one of two ways. The hard way is to look at the XML (BTW, this is the type of thing you can't see it in STATISTICS PROFILE, which I understand a lot of people are using). Right at the top is the SELECT element. You can see the value for StatementOptmLevel property is…
Read More
28Oct 2010 by Grant Fritchey 1 Comment

SQL Rally: Performance Tuning Abstract

PASS
I get the call, you get the call, everyone gets the call. "Hey, my app/procedure/query/report is running slow." Now what do you do? You go to my full day session at SQL Rally, that's what. Assuming you vote for it. I didn't post the abstract I submitted for the SQL Rally before because I thought that it would be redudant. However, since it's not right off the voting page (unless they updated it since I voted), if you're interested, here's what I thought I would do for a day. If it sounds good to you, please go here and vote for it. One of the most common problems encountered in SQL Server is the slow running query. Once a query is identified as performing poorly, DBAs and developers frequently don’t understand how…
Read More