Virtual Drives

SQL Server 2005, SQL Server 2008
I published an article describing an attempt my company made at using virtual servers as a full-fledged development environment. It didn't work real well. I'm not a systems guy. I know a bit. I pay attention and learn things. I certainly listen to the people around me that are systems experts. When several of them commented on this great assessment of virtual drives, I went and read it. It sounds like some really cool technology only with the ability to really make our lives harder. Like the article says, how often do you check to see if the drive has slowed down?
Read More

Benefits of a Tear Down & Rebuild in a Database

Tools, Visual Studio
Using the Team Edition of Visual Studio for Databases (VSDB) enables you to build a database out of source control. This means you can treat your database like code, as much as you can. The fact is, because of persistence, a database just isn't code. Because you have to keep all the data previously entered, when you deploy a new version of your database to production, you don't simply get to replace the database like you do with the code. You have to run scripts that alter that which can be altered, but preserve the existing data everywhere. That's just how it is in production. You have to do the work necessary to protect your data. Not so in Development. Development (and QA, Test, Financial Test, Performance Test) is the place…
Read More

Serious Error

TSQL
When I wrote the book "Dissecting SQL Server Execution Plans" I knew I was going to get things wrong. Several people have pointed out things over the last couple of months. They've all been in the details. None of them were serious errors of fact. Andy Warren just found a huge one. In the section on Table Hints I detail how to apply an INDEX() hint. It's on page 123 in the electronic version or 124 of the first print version. I state that index number starts at 0 with the clustered index. That's just flat wrong. A clustered index is always 1. A 0 indicates a heap. Other indexes will have values greater than 1. If you were to supply a 0 to the INDEX() hint, as shown in the book, it forces either…
Read More

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

Code from "Dissecting SQL Server Execution Plans"

TSQL
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

Constant Scan in Execution Plans

SQL Server 2005, SQL Server 2008, TSQL
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

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

Article Ideas, specifically on SQL Server 2008

Misc, SQL Server 2008
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

Breaking Down Complex Execution Plans

SQL Server 2005, SQL Server 2008, TSQL
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