I keep seeing these concepts that have long been disproven, posted again and again on newsgroups as if they were valid information. The latest? Table variables are better because they're only in memory where as temporary tables write to the disk through tempdb. This one is abjectly wrong. I'm not even going to hedge with "it depends." From a macro point of view, there are only a few differences between temporary tables and table variables, statistics being the biggest. Temporary tables have 'em and table variables don't. Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size. Some of the minor differences, and why you might want to use table variables over temporary tables, table variables won't cause a statement recompile while temporary tables will, table…
It's not enough to look at the longest running query. What if you have two queries, one runs for 5 minutes and the other runs for 5 seconds. Which do you tune? The first one, of course. But, let's add a little information to our thought experiment. The one that runs for 5 minutes is called 2AM, your local time, when there are almost no users on the system and the one that runs for 5 seconds is called 40 times a minute all day long. Now, which one do you tune? That's right, suddenly that 5 second query takes on a HUGE significance based on how often it's called. But how do you know how often a query is called? The easiest way to gather that information is not…
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…
I'm still barely scratching the surface working with spatial data in SQL Server 2008. We've ported some of the data into a table where we built a geography spatial data column and we're begginning to work with point data. The requirements from the developers are, so far, very simple. They'll feed me a point and I find all the locations "close" to it. We had to go round & round on what defines "close" but finally settled on, I think, 15km. The query to answer a question like this is ridiculously simple (a few object names have been changed): SELECT ebe.[Location].STDistance(@Location) AS Distance, ebe.[InterestId], ebe.[Location].Lat AS Latitude, ebe.[Location].Long AS Longitude, ebe.[OrgId] FROM dbo.[ebe] AS ebe WHERE ebe[OrgId] = @OrgId AND ebe.[Location].STDistance(@Location) < @CloseDistance I'm not even hard-coding the "close" value…
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…
This code is not in the same order as the book. A number of edits were done to the document after it was produced. USE [AdventureWorks]; GO --Listing 1 --Select --highlight this and click "Display Estimated Plan" on tool bar, right click & select from menu, --select from the Query menu above, or type CTL-L. SELECT * FROM [dbo].[DatabaseLog]; GO --Listing 2 --SelectJoin -- click "Include Actual Excecution Plan" on tool bar, highlight and execute SELECT e.[Title] ,a.[City] ,c.[LastName] + ',' + c.[FirstName] AS EmployeeName FROM [HumanResources].[Employee] e JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] = ed.[EmployeeID] JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID] JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] ; --Listing 3 --SelectJoin2 SELECT e.[Title] ,a.[City] ,c.[LastName] + ',' + c.[FirstName] AS EmployeeName FROM [HumanResources].[Employee] e JOIN [HumanResources].[EmployeeAddress] ed ON…
I've seen this question posted in a lot places and I've seen the search come in to the blog: "How do you break down a complex execution plan?" The short answer; just like eating an elephant, one bite at a time. The longer answer... First you have to define what you mean by a complex execution plan. For example, I've seen 16 page stored procedures that consist of about 50 or more different statements executed in series (and I 've heard of worse). No single statement was very big, but trying to dig through all the statements to identify which may be causing slow execution was a problem. This is the easiest type of complex execution plan to solve. In SQL Server 2005 and 2008, when you turn on a graphical…