Training and Learning

SQL Server, Tools
I think these are usually two different things, but most people conflate the two. There was a great discussion over at SQL Server Central based on an editorial by Steve Jones. It's worth reading through to see how people learn or get themselves trained. One common theme is reading books. I'm looking at stacks of them all over my desk, so it would be hard to deny their use. If you too like to read, then I've got something for you. Red Gate is doing a promotion where, when you purchase SQL Tool Belt, you can also download five E-Books offered by Apress. Several of them look pretty interesting, so this is a good deal. Not to mention, if you're not using some of the tools from SQL Tool Belt already, you're…
Read More

SQLPLAN Glitch

SQL Server, T-SQL
While I was doing some work for Andy Warren (more on this after Sept 1st), I came across an interesting little glitch when saving XML execution plans out as SQLPlan files. It's easy enough to replicate. Just get an XML execution plan from your query: SET STATISTITCS XML ON; SELECT... Click on the link to open the XML plan. Click on the "File" menu and then the "Save As" menu item. It opens the familiar file save window. Click on the "Save as type" drop down and switch to "All Files (*.*)" Save the file with an extension of ".sqlplan." Good. Now you've got an execution plan file that can be opened and viewed as a GUI execution plan. Without closing the XML, try to open this new plan. You…
Read More

Deadlocks vs. Blocks

SQL Server, T-SQL
It makes me crazy when I interview someone who has five or more years as a DBA, but they don't know the difference between a block and a deadlock. It's a complete showstopper for me. If you don't know this, you're an entry-level DBA, don't talk to me about your years of experience. Sorry, but there it is. Here's someone that's kinder than I am in every way. Not only have they cut people slack on this question, but he's provided a well done answer to the question. For those who may interview with me in the future, go and read this and understand it.
Read More

SQL Server DBA’s Sound-Off

SQL Server
This is a great read about the attitudes and beliefs of quite a few SQL Server DBA's. It's very interesting how certain beliefs and attitudes seem to be common. I was also reassured that my attitude about a certain DBA was accurate... less said the better. These are interesting and informative guys doing the same kind of work that I try to do. If you're trying to, go read it and learn. I read about it on StatisticsIO. Another place with good things to read.
Read More

Virtual Drives

SQL Server
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

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

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

Visual Studio Team Edition for Databases 2008

SQL Server, Visual Studio
Great news. All the processes we had created for automating our deployments and builds in VSDB 2005 work in VSDB 2008. Bad news. All the work arounds and crutches we had to figure out and maintain are still necessary. I've heard that it makes the MS guys crazy, but we found that the only way to get the configurations to work with VSDB 2005, and now 2008, was to check-in the .USER file along with the code. This then needed to be checked out of source control, replacing the local copy (and heaven help you if you don't use a forced get on the check out) before doing any work with the project. I did a full build & deploy from 2008 through our usual batch file. No problems at…
Read More

Top Vs Max

SQL Server, T-SQL
I finished a set of research and tests. I put it all together and gave it to Chuck Heintzleman at SQL Server Standard (that web site can use some help). Hopefully he likes it and publishes it. I understand that the upcoming issue will be on performance tuning. If he doesn't like it, I'll get it over to Peter Ward at SQL Server Performance or to Steve Jones at SQL Server Central. I'm posting this because I see so many web searches looking for information comparing TOP to MAX. Here are some short answers for those interested. Data to support these suggestions is included with the article. ROW_NUMBER works best with smaller data sets. TOP has more consistent operation over a range a data. MAX frequently resolves as a TOP operation, but sometimes…
Read More