22Aug 2011 by Grant Fritchey 3 Comments

Expert? Ha!

SQL Server, T-SQL
How do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book. Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before. I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew…
Read More
05Jul 2011 by Grant Fritchey 1 Comment

PASS SQL Rally 2011 Evaluations

PASS, SQL Server, T-SQL
  In keeping with the all my other major speaking engagements, I’m posting the results of my pre-con and session evals at the first-ever SQL Rally. I’m posting this really late and I apologize. The good news, I did a fill-in when someone cancelled and I was placed in the Grab Bag category where I had the highest rated session although it was a far cry from the actual highest rated sessions from the whole Rally. There is no bad news. Query Performance Tuning: Start to Finish I was pretty happy with this considering it’s my first, public, all day training class. I’m even happier with the feedback. I received some excellent constructive criticism that I will attempt to take into account when I present in the future (including at…
Read More
20Jun 2011 by Grant Fritchey 9 Comments

Make the Optimizer Work Harder

SQL Server
One of my favorite indicators for whether or not you have a good execution plan is when you see the “Reason for Early Termination” property in the TSQL operator like this: The optimizer considered this particular plan “Good Enough.” which is what you want to see. When you see “Timeout” as the reason, that’s an indication that the plan you have may be sub-optimal. The question is, can you make the optimizer spend more time on your queries. Well, actually, the question is, should you make the optimizer spend more time on queries. During my session on SQL Cruise I answered the original phrasing of that question, no. As usual when I present in front of people smarter than I am, I was wrong. Brent Ozar (blog|twitter) pointed out that…
Read More
30May 2011 by Grant Fritchey 7 Comments

Optimizer Timeouts with XQuery

SQL Server
I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out? This sounds like a job for XQuery! There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code. WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),  QueryPlans AS  (  SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,         RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,         --dest.text,         SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,                   (deqs.statement_end_offset - deqs.statement_start_offset)                   / 2 + 1) AS StatementText,         deqp.query_plan,         deqp.dbid,         deqs.execution_count,         deqs.total_elapsed_time,         deqs.total_logical_reads,         deqs.total_logical_writes FROM    sys.dm_exec_query_stats AS deqs         CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)…
Read More
25May 2011 by Grant Fritchey 26 Comments

Oh ****!

SQL Server
Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that…. OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been…
Read More
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
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
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