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…
I was asked if I would look at a new tool from a company I hadn't heard of. It's SQL Inspect from Yohz Software. Nominally this is a SQL query editor. However, strictly as a query editor, especially when compared to what's available in SQL 2008 or what tools like Red Gate's SQLPrompt or SQLRefactor can do for you, it's not so hot. Luckily, that's not where its strengths lie. Instead, it's all about performance tuning your queries and it does this very well indeed. It takes a query and returns a tree structured execution plan, looking a bit like the old text plans, but much more sophisticated with roll-ups, etc. It shows you all the details of the operations, just like an execution plan, but immediately accessible on the screen,…