Kalen Delaney : Upgrade

Did You Know? Microsoft is/are Still Fixing Bugs On SQL Server 2005

Kalen Delaney — Tue, 17 Jun 2008 16:45:00 GMT

I taught a private class last week near Portland (Oregon) and although the company has a lot of SQL Servers, there was not much excitement at all about SQL Server 2008. They still have many SQL Server 2000 installations, and are debating whether to upgrade those to 2005. Most of the interest was in the real value of the new 2005 features, like partitioning, and the new large object types, as well as interest in the new metadata, such as the DMVs.

They're trying to run a business, and keep their systems running well. They don't have a lot of time to get excited about features that are way in the future.

I can't believe that my client is the only organization that is very interested in the continued health of SQL Server 2005, but I was surprised to see no blog posts here on SQLBLOG about the latest Cumulative Update for SQL 2005.

Yesterday, Microsoft released CU#8 and you can read about it here:

http://support.microsoft.com/kb/951217

So what are you waiting for?

~Kalen

Did You Know? The Best SQL Server 2005 Feature

Kalen Delaney — Wed, 05 Dec 2007 05:44:00 GMT

This post really should have a topic of "Pet Peeve" but I really didn't want to sound quite so negative.

It seems like almost every time I see a clever solution in a newsgroup response, or in a blog post, about how to extract some very useful troubleshooting information using the Dynamic Management Views, someone will ask for an equivalent query in SQL Server 2000. It just happened again as I was doing a google search and came across a post by Kevin Kline right here on sqlblog about using the new metadata to get information about space usage in tempdb. It was actually referring to a query developed by Aaron Bertrand, who also posts here. Someone asked if there was a way to do the same thing in SQL 2005, but we assume they meant SQL 2000. Our own Adam Machanic responded that the CROSS APPLY operator was not available in SQL 2000, but that is the least of our worries. The query refers to seven different Dynamic Management Objects (6 views and a function), most of which have no equivalent in SQL Server 2000.

It just seems that some people seem to think that anything we can do in one version should be doable in a previous version. If that were true, why would you upgrade? Do you only upgrade for performance benefits? I don't think so.

In my opinion, the BEST SQL Server 2005 feature is the new system metadata. It is absolutely worth the price of admission. There are so many things that you couldn't even begin to find out about with the metadata from SQL 2000, and the only way to get that level of internal detail is to UPGRADE!

If you're running SQL 2005, the Dynamic Management Objects can provide hours and hours of fun and exciting explorations!

~Kalen

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio

Kalen Delaney — Tue, 17 Jul 2007 22:16:00 GMT

In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections appears to be left over from SQL Server 2000. I know there are a lot of different dialog boxes available through the Tools | Options menu, but I find it amazing that this one was completely overlooked when updating the old Enterprise Manager GUI.

Here's how to get to the dialog:

Tools | Options

Then expand Query Execution to SQL Server and then to Advanced...

The list of the set options to enable does not include anything new in SQL Server 2005.

1. It does not include SET SHOWPLAN_XML or SET STATISTICS XML (but includes SHOWPLAN_TEXT)

2. Transaction Isolation Level does not include SNAPSHOT

3. Deadlock Priority only allows NORMAL and LOW, not the numbers -10 to 10, or the value HIGH
(see my earlier post: http://sqlblog.com/blogs/kalen_delaney/archive/2006/09/09/192.aspx )

Maybe there are others that I haven't noticed yet.

So now you know...

~Kalen