07Jul 2008 by Grant Fritchey No Comments

Words of Wisdom

PASS
Every week the Database Weekly Update comes out from SQL Server Central. There are always good things to read there. Links to interesting tid-bits of information posted by really smart people. One of them that jumped out at me this week, to the point that I read it out of order, was a blog entry by Andy Warren. If you ever get the opportunity to listen to Andy speak, jump on it. He's great. His writing is wonderful too. Anyway, he wrote an entry giving advice on how to break in to the national scene as a speaker. I know I dwell on this WAY too much, but I've been given the opportunity to speak at PASS this year. Notice I said "been given." I don't think I earned it…
Read More
02Jul 2008 by Grant Fritchey 1 Comment

Code from "Dissecting SQL Server Execution Plans"

T-SQL
This is the complete code listing from the book "Dissecting SQL Server Execution Plans." You need a copy of AdventureWorks. Please note, AdventureWorks changes. It changed three times while I wrote the book. These changes can be very subtle causing variations in statistics which will make some of the queries generate execution plans in a different manner than what was published in the book. Some of these changes can be pretty radical causing the queries to not work at all. Also, the book went through quite a few edits including rearranging the order in which sections appear. This listing is the order in which things were written and it might vary from the book. In other words, caveat emptor, your mileage may vary, keep your hands and feet inside the vehicle…
Read More
01Jul 2008 by Grant Fritchey 10 Comments

Constant Scan in Execution Plans

SQL Server, T-SQL
I see a lot of searches from people apparently trying to find out what having a Constant Scan in their execution plan means. I can understand why. Here's the definition from the Books Online: The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used to add columns to a row produced by a Constant Scan operator. OK. Very precise and yet, unless you know what the sentence means, reading it can be pretty confusing. The key is to see what Compute Scalar means: The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate…
Read More
01Jul 2008 by Grant Fritchey 2 Comments

PASS Community Summit Program Details Published

PASS
I'm pretty jazzed to see that the PASS Community Summit has finally put up the list of sessions and the abstracts. However, I'm more than a bit nervous to see my name at the very top of that list. I couldn't have been buried somewhere in the middle? It'll probably change. I don't doubt I'll be delivering during the final session on Friday. This sure makes it more real. I'll have to get to work on the slides and demo's now.
Read More
23Jun 2008 by Grant Fritchey 5 Comments

Article Ideas, specifically on SQL Server 2008

Misc
I'm trying to come up with some ideas for an article (or six) on SQL Server 2008. I know I'm getting about 40 views a day on this blog. That must translate to about 20 users (the statistics on this site aren't clear, so I'm guessing). Do any of you have any good questions about new functionality in SQL Server 2008? I've been thinking about looking at sparse columns and filtered indexes or maybe the spatial data types. Those are the topics I'm most excited about as a database developer and designer. If I think about it in terms of being an admin, policy management, the data collector, and those types of new functionality are very interesting. I'm not even sure what's new in the BI space. So, if you're…
Read More
23Jun 2008 by Grant Fritchey No Comments

Breaking Down Complex Execution Plans

SQL Server, T-SQL
Peter Ward, the editor at SQL Server Performance, has published an article of mine on Breaking Down Complex Execution Plans. I go way beyond the blog entry below and show how the estimated costs in execution plans can mess you up, how to use the XML in execution plans to search through them for costly operations or operations that have mismatched estimated rows & actual rows and some other tips and tricks. Hopefully it's worth a read.
Read More
18Jun 2008 by Grant Fritchey No Comments

SQL Server Management Pack Resources

Tools
There aren't any. That's not entirely true. There is the SQL Server Management Pack Guide from MS. It gives you the basics. After that you have to fall back generic documentation and help for the most part. If you haven't already, get a copy of System Center Operations Manager 2007 Unleashed. It doesn't cover the SQL Server Management Pack in any detail, but it gives you a lot of what you need to understand the management pack in general. The next best source of information is the newsgroup microsoft.public.opsmgr.sql. From there, you have to go to System Center Forum. Watch for articles by Tom LaRock. He seems to know as much about it as anyone. He's presenting at the PASS Summit this year, so you might want to attend. There…
Read More
13Jun 2008 by Grant Fritchey 1 Comment

Why are Subqueries Dangerous?

T-SQL
If you go around to the various forums, you'll see postings, including some I've put up, that say using subqueries, especially correlated subqueries in the SELECT statements of queries is bad because it effectively acts as a cursor. I got called on it. So I had to do a bit of research. Books Online talks about it right off when describing subqueries. That's not proof though. I did some more looking around. Adam Machanic does a nice job of slicing up Functions, which are basically the same thing. But it's not exactly the same. I kept looking. This blog entry is just flat wrong, but the second comment points out the fallacy. Jeff Moden would also like this example since it shows the disparity between the actual cost of queries…
Read More
13Jun 2008 by Grant Fritchey No Comments

Performance Data Warehouse

Uncategorized
The new functionality coming out with 2008 includes the Data Collector which feeds to the Performance Data Warehouse. I presented this topic at the SNESSUG Heroes Launch event. It's pretty slick functionality. I posted about it once before. A new article by Derek Comingore is available now over at SQL Mag. It's worth the read. The beauty of the new tool is the fact that it's not all that new. It's doing all this work using tools that we know and are comfortable with. This makes it very easy to implement and maintain. I agree with Mr. Comingore and I hope they expand the client to cover SQL Server 2000 and 2005.
Read More
12Jun 2008 by Grant Fritchey No Comments

VSTS 2008 Database Edition GDR: Final First Impression

Tools, Visual Studio
I finally finished my initial set of tests with the GDR CTP release. It's great! They've solved so many of the deployment problems that we had been experiencing that it's now hard to wait until they actually release the product. The bad news is, it completely changes my presentation at PASS. I'm still going to cover 2005/2008, but now I'm going to cover the GDR as well. I have one hour to hit both processes... That's not going to be easy. Anyway, they've broken everything down to either work locally only or as part of the larger project. It all gets checked into source control. It all comes back out. No manual processes (like checking in the .user file) necessary. Thank you Gert Drapers, wherever you are.
Read More