SQL Server Katmai

It is announced to be available in 2008.
I'm ready for a beta/ctp version ;-)

More information here and here.

Datasheet here.

Visual Studio 2005 Team Edition for Database Professionals - SR1 (CTP)

For those of you who are lucky and are already working with VSDBPRO, there is a service release CTP available for download (KB936202). Make sure you read the release notes before you install.

Gert Drapers also posted some additional information on his blog.

One of the biggest enhancements, in my opinion, is the support for SQLCMD variables. This enables great flexibility in your deployment scripts.

Cumulative waitstats per session/query

We were troubleshooting a long running batch job when we suddenly realized that there is no easy way to get cumulative wait information for a specific session or query. With the rich information already available to us in SQL Server 2005 this might be a great addition to the product.

I decided to launch a suggestion on Microsoft Connect. This great initiative makes it possible for everyone to give suggestions to improve Microsoft's products.

Check out my suggestion here (and while you are there do vote ;-))

A couple of interesting KB articles

Today we were installing SQL Server 2005 on our new Windows 2003 Server (SP2) cluster when we ran into the following issue: "The file C:\Windows\Microsoft.NET\Framework\Meaningless_string\mscorlib.tlb could not be loaded", the solution to this can be found in KB918685.

Dirk G from MCS Belgium was nice enough to point us to another hotfix concerning the USERSTORE_TOKENPERM issue, KB933564 contains more information about this.

KB935897 has some information about the new "Incremental Servicing Model" for SQL Server.

So what's up with all the SP2 post fixes?

It's getting quite cumbersome to understand all the releases after SP2 for SQL Server 2005. Bob Ward from PSS decided to clarify this on his blog.

Aaron Bertrand also clarifies the differences between a GDR and a QFE here.

WinDbg and SQL Server minidumps

Today a couple of our test servers started to act very weird. Some jobs were going incredibly slow and eventually most of them crashed causing a mini dump to be generated. The error log pointed to I/O problems with latch timeouts but there were no details on which file exactly it crashed.

The SAN and NAS management is done by another team and we had to pinpoint the problem to convince them that it was really an I/O issue. So how exactly do you do this on a server with several databases and processes?

Off to my favorite developer Bregt, I knew he had good knowledge of WinDbg so he could help us pinpoint the issue. Lucky for me Bregt is a nice guy and he explained to me how I could find the cause of the crash.

Download WinDbg which is part of the debugging toolkit provided by Microsoft.

Start WinDbg (Run As Administrator in Vista) and configure the symbols as follows (CTRL-S)

The url can be found in the WinDbg help and the local symbols path is one you choose (and create yourself). Don't forget to save your workspace when it asks you to (thanks again Bregt).

After you have done this you can open a crash dump (CTRL-D), this might take a while depending on the size of the dump.

Type ~kv in the command line, this gives you the call stack with the memory addresses.

Using ALT-5 you get a memory viewer, here you can check the content of the memory for the last call. It is possible however that nothing is returned for the memory address since not everything is dumped with a minidump. In our case this was "ntdll!ZwCreateFile+0xa" and this directed us to the exact filename and it was a server side trace that was being saved to a NAS drive.

How cool is that?

I feel like digging a little deeper in WinDbg when I find some time. A colleague of us (Hans De Smaele) has written a great document on this topic.

SQL Server 2005 SP2 - Another hotfix

This time we are at build 3159 with KB934459.
Those darn maintenance plans sure are causing mayhem lately :-)

Detach/Attach migration

In our effort to consolidate some environments I had to migrate a reporting database from SQL Server 2000 to SQL Server 2005. One of the easiest ways of migrating is a simple detach and attach (sp_attach_db is deprecating and being replaced by CREATE DATABASE FOR ATTACH in SQL Server 2005).

After the attach some options have to be changed like the compatibility level, page verify checksum, etc. and as a last step I issued an sp_updatestats.

When I started the test run I noticed that it was taking very long so I started issuing some sp_who and sp_lock statements. The output showed that the same locks were being held for an unreasonable amount of time until the point that tempdb ran out of space. I had 8GB of tempdb space and the query only had to return 3 rows from a couple of tables that had less than 100 rows. I decided to run the query again and monitored tempdb usage with the new DMV sys.dm_db_file_space_usage, I immediately saw tempdb allocations rise at an incredible rate.

I decided to drop all the existing statistics and issued the same query again, this time it immediately returned the 3 rows without any trouble and without any unusual tempdb usage.

Very odd behavior but this shows the importance of statistics and the apparently subtle differences between SQL Server 2000 and SQL Server 2005. Dropping the statistics is easy in development/test but for production environments you might want to script the existing statistics and recreate them. For some unknown reason the update statistics was not as effective as expected after an attach from a SQL Server 2000 database.

*UPDATE*
Things like this are always a bit more complicated than they seem at first. After the import of the new set of data for today the query went back to the bad behavior. A strange effect was that the query worked well if I took a date range of a couple of months but failed with an interval of 1 day. The key here was of course the fact that because of a bad estimation the query plan was completely different in the two cases. Creating one index on a single field out of the ten tables that were involved in the query resulted in a different query plan being chosen and completely solved the problem.

Yet again a couple of important things are proven:

• The differences in the query optimization engine between SQL Server 2000 and SQL Server 2005 make it important to test every query
• Statistics are of great importance to the query optimizer so watch them carefully and make sure they are kept up-to-date
• Having proper indexes is an absolute necessity for every environment
• Any change, how small it may seem, could have great impact on your environment

Views and the smart Query Optimizer

In general I don't really like to use views. Although they have their advantages I often see it turn out bad in projects. The main reason is that people start to use complex views that were not written for their specific functionality and many tables get touched that have nothing to do with the result they are trying to retrieve.

The query optimizer is quite intelligent when it comes to resolve the query plan for a view. It is smart enough to ignore the tables you do not need to resolve your query.

CREATE VIEW SmartView AS
SELECT i.ItemTitle, i.ItemLink, u.UserName
FROM tbl_item i
JOIN tbl_user u ON i.OwnerID = u.UserID
GO

SELECT ItemTitle, UserName FROM SmartView

SELECT ItemTitle FROM SmartView

You can see SQL Server is quite intelligent when it comes to optimizing views. Unfortunately it still cannot prevent every situation of misuse. Some people have no problem of using SELECT * FROM SmartView and use only the ItemTitle field for example. When your view uses many (large) tables this might cause a lot of grieve on your database. One of the most extreme examples I have seen in real life is a SELECT from the biggest view in the project, causing millions of reads, where the result that was being used was the rowcount - which could actually be retrieved by counting the rows in one table. The key is obviously having a good DBA to review every query ;-)

Windows Server 2003 Service Pack 2

It was very close indeed :-)

Download here - fixlist here

I/O counters in Task Manager

One of my freaky habits is to check the I/O counters in the task manager. Whenever I'm doing I/O intensive stuff I like to quickly look at the MBs processed.

Whether these counters are updated or not is controlled by a registry setting (see below). You can set the value to 0 to disable it or 1 to enable it. Disabling it gives a little performance boost since Windows doesn't have to take care of the counters but on test systems I like to enable it. SQLIOSim also requires it to be enabled to get some of it's results.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System\CountOperations

By the way, Windows Server 2003 Service Pack 2 (fixlist) is getting very close. Keep an eye out for it because it contains fixes to improve performance of SQL Server 2005 under heavy load. More details will follow.

SQL Server 2005 SP2 - Cumulative Hotfix (build 9.0.3152)

A new cumulative hotfix package has been release for SQL Server 2005 SP2.
The new build number is 3152, they are really going fast these days :-)

http://support.microsoft.com/?kbid=933097

SQL Server 2005 Performance Dashboard Reports

Keith Elmore, an escalation engineer at Microsoft, has created a "Performance Dashboard" for SQL Server 2005 SP2 to detect common performance issues. It uses the new Custom Reports functionality in SP2.

More info can be found here.

The files are installed in \Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard. You have to run setup.sql on the server(s) you wish to monitor (do remember Service Pack 2 is required!).

SQL Server 2005 Post SP2 Hotfix

The kind people of SSWUG warned us today about a potential problem with Service Pack 2.

And I quote:

"Important SQL Server 2005 SP2 Hotfix
If you're using SP2, you need to be sure to pick up the hotfix, to be posted today (Mar 6 2007). There is an issue with SP2 and a "mixed" environment of SQL Server versions. This issue specifically occurs in fairly small and particular circumstances. First, you have to have installed SP2. SP2 was released just a week and half ago or so, so many have this in a test environment at this point. Second, if you have an environment where pre-SP2 client utilities are in-use, if you use these utilities to open/edit cleanup processes on the SP2 servers, you can encounter the issue. SQL Server will incorrectly read the intervals set up for the cleanup processes, resulting in cleanup processes not running when expected.

We first had a report of this here on SSWUG last week. The report, and a few follow-on reports since, indicated that, after installation of SP2, packages apeared to have different intervals when they were reviewed after SP2 installation and compared with intervals set up prior to that time.

I spoke with Francois Ajenstat, Director of Product Management for SQL Server late in the day on Monday (Mar 5) and he indicated that a hotfix would be available on the SQL Server site late Mar 5/Early Mar 6 and that the SP2 bits have been (or will be at the same time) updated to include the updated code that corrects the issue.

What you need to know: Not correcting this issue could lead to data loss in some circumstances. According to Arjenstat, if you've installed SP2, you must apply the hotfix, available on the site. You should do this immediately to avoid the issues outlined here. Second, if you've downloaded and not yet installed SP2 on all or some of your servers, for those that hav not yet been installed, you need to get the newer SP2 update and be sure you're using that as you update your servers. While there are mitigating circumstances around this (versions, mixed environment, cleanup tasks), Arjenstat said they're being very open and aggressive in getting the news of the udpate out there and the updated bits available on the site immediately. If you are experiencing issues, PSS will work with you to help resolve them."

*EDIT*
The KB article can be found here. The SP2 download has also been refreshed so if you download the new SP2 install file this fix will be included.

Bob Beauchemin coming to the DevDays

Great news for the SQL Server people visiting the DevDays in Belgium, Bob Beauchemin will be giving a number of sessions on the second day. I will certainly be attending all of them Bob ;-)

Yet another difference in Vista Home Premium

I was trying to expose 2 drives as 1 which is possible by using dynamic disks. I'm a big fan of diskpart so I tried converting the disk using this command line utility:

diskpart
select disk 1
convert dynamic

Error: Dynamic disks are not supported by your edition

This is not a new limitation, it was actually the same for Windows XP Home Edition.

SQL Server 2005 Best Practices Analyzer (February 2007 CTP)

Get it here!

SQL Server 2005 SP2

Just like I posted from the first rumours, the end of February and here we are

SQL Server 2005 SP2

The ultimate "who blogs about SQL Server @ Microsoft" post

Check it out here

The not so visible differences between Vista Editions

I was trying to run Virtual Server 2005 R2 on my Vista machine and installed IIS as mentioned below. There was one option I couldn't find however and that was "Windows Authentication".

Apparently there are more differences between the Vista editions as there are mentioned on the website.

Windows Authentication is not available on the Home Editions :-( I also noticed that the good old user manager is not available either (only the wizard interface).

More info about IIS features here