07Oct 2008 by Grant Fritchey 2 Comments

SQL Server 2008: Database Engine Tuning Advisor

Tools
I don't think this thing works. I posted a bug report in Connect. If you can replicate my problem, described below, please go here and confirm the issue. Get a copy of AdventureWorks2008. Pick any of the bigger tables. In my case I went with the Sales.SalesOrderHeader table. Write a query, a simple one or a complex one, that has performance problems that can be easily fixed by the right query. Here's a simple example: SELECT  soh.Freight, soh.SalesOrderNumber FROM    Sales.SalesOrderHeader AS soh WHERE   soh.SalesOrderNumber LIKE 'SO' + CAST(6 AS VARCHAR) + '%' AND soh.Freight > 50 If you run this query and get an execution plan, it immediately flags a missing index. The plan itself is a clustered index scan and a filter operation. Take the index it suggests and build it:…
Read More
01Oct 2008 by Grant Fritchey 2 Comments

PASS Summit T-Shirt

PASS
Apparently, if you write a friends name in who is also registered for the PASS Summit, you can both get nifty t-shirts. Well, doggone it! I want a nifty t-shirt. So someone, please, click this link and include my name in your registration in the Friend-To-Friend box. Assuming you know my name.
Read More
01Oct 2008 by Grant Fritchey No Comments

Simple Talk Editorial on PASS

PASS
If you don't subscribe to the Simple-Talk newsletter, why not? This month's newsletter is chock full of interesting stuff, a DBA checklist from Brad McGehee, a workbench on keys, an article on how covering indexes are faster than clustered indexes (they are too), and an article on why one DBA is learning PowerShell (I want to, but my spare time is taken up with writing a new book). All great stuff and worth reading. I'm going to be reading all these articles, but the most fascinating piece of the current newsletter is the editorial by Tony Davis. I was actually a little shocked by it. I'm a PASS volunteer for the Special Interest Groups and the Editorial Committee. I've attended the last three PASS events. I'm the current president and…
Read More
29Sep 2008 by Grant Fritchey No Comments

Dissecting SQL Server Execution Plans at PDC

PASS, SQL Server, T-SQL
I just got word that Red Gate has printed more copies of the book that they'll be distributing at the Microsoft Professional Developers Conference that's taking place in LA. I just wish we had a 2008 version of the book now because, while most of it is still applicable, there's more that can be done with execution plans now. Have I mentioned I think the missing index information that's displayed with the statement text in the graphical execution plan in SQL Server 2008 is pretty slick? Well it is. I hope they're going to distribute it at the PASS Summit this year too. I think they are, but I don't know that for a fact.
Read More
29Sep 2008 by Grant Fritchey 3 Comments

Loop Joins, More rows on top or bottom?

T-SQL
I've seen this question come by in the web searches multiple times. The nested loop join is also called an iterative join. This is because it takes the rows from the inner part of the join and compares them through an iterative process (one-by-one) to the rows in the outer part of the join. So, if the optimizer has correctly chosen this operation for your query, you should see FEWER rows in the top, or outer, part of the join and MORE rows in the bottom, or inner, part of the join. Take this query as an example (run against AdventureWorks2008): SELECT * FROM [Sales].[SalesOrderHeader] soh JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE soh.[SalesOrderID] = 47716 Here we have a single row from the SalesOrderHeader table and 55 rows…
Read More
24Sep 2008 by Grant Fritchey 2 Comments

Index Statistics

T-SQL
The other day a developer showed up at my desk. They were getting time-outs in production on a query that didn't normally give them trouble. With the parameters they provided, I ran the query. It ran for over 30 seconds, the application side timeout, before it returned it's data. So I ran it again with an execution plan. It had a bunch of index scans with loop joins across thousands of rows and even created a table spool with 700 million rows as part of the process. Clearly not good. Next I looked at the query plan. It wasn't too bad, as these things go. It was probably moving too many columns and apparently the business wanted a pivot on the data since they were using an aggregate method to pivot some…
Read More
16Sep 2008 by Grant Fritchey 2 Comments

JumpStartTV

SQL Server, T-SQL
All the videos I recorded on execution plans for JumpStartTV are now available. Please, go and check them out. Then hang around JumpStartTV and check out all the videos that Brian Knight did on SSIS. Also, if you're interested, I think Andy and the team would like other people to submit videos as well. So if you're watching my stumbling attempts and are convinced you can do better, do it and submit it.
Read More
15Sep 2008 by Grant Fritchey No Comments

Video Lessons

SQL Server, T-SQL
A while back, I wrote a book, Dissecting SQL Server Execution Plans. Because of it, I had some conversations with Steve Jones & Andy Warren. For a SQL Server geek, heady company. Anyway, they asked me what my plans are for the book. Plans? I wrote it. I thought that was the plan. But they meant lessons, licensing and all that kind of stuff. I didn't have a clue, but they did. A few weeks ago I flew down to Florida and recorded a bunch of short video lesson plans derived from the book and from discussions with Andy Warren. It was a blast. They've now been published over at JumpstartTV. I hope you find them useful. I had a blast doing them and learned a lot from Andy and…
Read More