17Jul 2009 by Grant Fritchey 15 Comments

Execution Plan Compile Termination

SQL Server, T-SQL
Recently I've been seeing a lot of people with bad execution plans, desperately trying to tune them, but they were unable to explain why they had such bad plans. More often than no these were larger queries, with a number of derived tables, CTE's, CROSS APPLY, etc. In most cases the statistics appeared to be fine (this is usually checked by comparing estimated & actual rows within the operations in the execution plan) and the plans themselves didn't look crazy, but the execution plans were flat out, not good. If you're looking at a plan and it doesn't make much sense, one option that most people don't check... SQL Server didn't have enough time to complete optimization. The optimizer is a pretty amazing bit of code. The scary volume of…
Read More
26Jun 2009 by Grant Fritchey No Comments

Re-evaluating Execution Plans (again)

SQL Server, T-SQL
I keep thinking I've got a handle on the way execution plans are dealt with in SQL Server. I had a pretty simplistic view of things, there's the estimated plan which comes out of the optimizer and there's the actual plan which comes out of the data engine. The one shows the best guess for what will happen based on the query and the underlying statistics. The other shows what actually happened based on the query and the real data, load on the server, available memory, you get the idea. This model is easy to explain and understand. Too bad it's not quite the way things work. If you query the dynamic management function sys.dm_exec_query_plan, you can see a query plan. Once you drill down on the XML, or browse…
Read More
11Jun 2009 by Grant Fritchey 2 Comments

More Spatial Headaches

Spatial Data, T-SQL
I keep thinking I've got a handle on spatial data and I keep finding out I'm wrong. I rewrote the cursor that my co-worker used into a query that joined the list of test criteria onto our list of locations. I used an OUTER APPLY to more or less run a check for each of the test criteria since, except for the proximity to the locations, there's no actual relationship between the test criteria and the location data for me to join on. The query looked something like this: SELECT a .CriteriaDesc ,a.CriteriaLoc ,l.[Location].Lat AS LocationLatitude ,l.[Location].Long AS LocationLongitude ,l.LocationDesc FROM dbo.Alert AS a OUTER APPLY (SELECT x.[Location] FROM dbo.MyLocs x WHERE x.OrgID = 42 AND x.[Location].STDistance(a.AlertLocation) < 50000) AS l The cursor was taking almost a full minute to…
Read More
09Jun 2009 by Grant Fritchey 3 Comments

Spatial Indexes and a Lack of Data

Spatial Data, T-SQL
I was feeling quite confident about my new-found abilities with spatial indexes so I did a presentation for my team, to share what I had learned. I had also been sharing with one co-worker as I developed the knowledge of spatial indexes. While I was preparing my presentation, he was preparing his. I had focused on finding a set of data that showed it's proximity to a test location and then showing how retrieving that set of data was faster because of the spatial index. He took a different approach. He took the idea of saying, here's a list of different test locations, let's see which one of our internal locations meet the proximity test. At the same time, he tried three different spatial indexes, one with high granularity, one with medium and a final…
Read More
03Jun 2009 by Grant Fritchey 2 Comments

ORDER BY Speed

T-SQL
I answered a question on SSC with the comment that while an INT would perform better than a DATETIME in an ORDER BY query, assuming each has a viable index, that the difference wouldn't be all that terribly substantial. Then I realized, maybe that's not true. So I ran up a quick test, just to see. First I created a little test table with the right indexes and loaded it with data: CREATE TABLE dbo.IntDate (IntCol INT NOT NULL, DateCol DATETIME NOT NULL); CREATE INDEX ixInt ON dbo.IntDate(IntCol); CREATE INDEX ixDate ON dbo.IntDate(DateCol); SELECT TOP 10000 IDENTITY( INT,1,1 ) AS n INTO #Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2; INSERT INTO dbo.IntDate ( IntCol ,DateCol) SELECT t.n, DATEADD(dd,- t.n,GETDATE() ) FROM #Tally AS t; DROP TABLE #Tally; Then I ran these…
Read More
28May 2009 by Grant Fritchey 3 Comments

Spatial Index & Performance & Brain Pain

Spatial Data, T-SQL
In my previous post, Spatial Data Hurts My Brain, I showed how a query used the spatial index, but hurt performance. Several people jumped in to try to help out. Thanks to Bob Beauchamin, Isaac Kunin, Gail Shaw, and Valerie Yakich (via Twitter). I've definately learned a bit more than I knew previously about spatial data and spatial indexes. I've posted about it before, but it bears repeating, if you're just starting out, I can't recommend Alistair Aitchison's book, Beginning Spatial with SQL Server 2008, enough. Here's where I'm at. I think I was confusing/confused/confounded/something about what to expect from a spatial index. I'm going to present two queries and two indexes and try to explain why each works well, or not together, mainly as a means for enabling my…
Read More
20May 2009 by Grant Fritchey 5 Comments

Odd TSQL Behavior

SQL Server, T-SQL
Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer. This morning a developer walked up and asked me what would happen if he ran a query that looked like this: SELECT 1.SomeString I said that he'd get an error. No, he says, try it. So I try it and I get this: Somestring 1 Try it yourself. It works just fine. I'd never seen that before and didn't have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this: SELECT 'dude'.dudette Which resulted in the error:   Msg 258, Level 15, State 1, Line 1 Cannot call methods on varchar.   Which is what I would have expected. I…
Read More
30Apr 2009 by Grant Fritchey 42 Comments

Database Screening Questions

SQL Server, T-SQL
With all the cool kids posting about beginners and interview questions, I thought I'd toss my favorites out there, from the brief-case gang point of view. These are the technical phone-screening questions I use after I look at a resume. There are only 10. They're simple. Stupid simple. Silly even. Yet, I can count on eliminating 4 out 5 people who have a resume that looks like a qualified DBA. I've seen people with 10 years experience fail on these questions. I'm only going to provide the questions. If you can't find the answers on your own, you're already disqualified: What is the difference between a clustered and non-clustered index? No, don't tell me that one is clustered and the other is not. I don't need specific low-level information on this, just…
Read More
27Apr 2009 by Grant Fritchey 1 Comment

Tim Ford’s Top 5 Indexing Best Practices

SQL Server, T-SQL
All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It's a must. And might I add, I've been the bad guy in Tim's example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn't work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone…
Read More
21Apr 2009 by Grant Fritchey No Comments

Spatial Data

Spatial Data, T-SQL
I work for an insurance company. If you think that maybe, we might be interested in the physical location of the things we insure, you'd be right. Actually, we're an insurance company predicated on the idea that risk can be managed. That means that not only do we know where your factory is located. We know the wind zone, earthquake zone, flood zone, rain zone and temperature zone it's in. We send engineers out to the site to inspect it and recommend upgrades. We track the upgrades and the condition of your facility. With all that location specific information, just how important do you think it is that with SQL Server 2008 we're finally getting a spatial data type? Yeah, exactly. We're in the process of launching our first full implementation…
Read More