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

Windows Vista

If you are a freak like me and can't wait to install Vista, although you know there will be many compatibility problems so early after a release, you might encounter some problems when you try to install SQL Server 2005 Express Edition on a Vista machine (MSDE will NOT be supported).

There is a website however that can help you with configuring everything to make SQL Server work on your Vista PC. So if you can't wait for Service Pack 2 check out this website.

There is also a little post-SP1 download for Visual Studio 2005 available.

And for the people who want to run Virtual Server 2005 R2 there is this link.

Oops I got tagged

I got tagged by Bart Bultinck, so here it goes... 5 things you may or may not know about me

1. My nickname dis4ea comes from a documentary about coffee, disphoria is extreme unhappiness and people who are addicted to coffee might suffer from it when they lack coffee.
2. One of my grandmothers was actually Dutch and my father lived in The Netherlands during the first years of his life.
3. My brother is a system engineer, my father worked as a manager in the technical department of a computer hardware retailer and my mother worked as a sales representative in a computer hardware retailer.
4. I don't drink alcohol either (respect Bart) and it is indeed very difficult to convince people that you really don't...
5. My favorite instrument is the guitar and I love all kinds of music where the guitar is the important instrument (especially blues). My favorite artists are Stevie Ray Vaughan, Joe Satriani, Buddy Guy, Jimi Hendrix and Metallica.

My turn to tag:

Bregt
NickVDA
Killspid
Sven Cipido
General

Big Procedure Cache in SQL Server 2005

As I once mentioned in a post I felt that the procedure cache was really out of proportion. We were assured that this was not an issue but now I stumbled upon the following post where they explain that the behavior has been seriously changed in SP2.

Align SQL Server 2005 plan cache size limit to a size similar to that of SQL Server 2000: The maximum size limit of the SQL Server 2005 Plan Cache in RTM and SP1 is significantly larger than in SQL Server 2000. To reduce the SQL Server 2005 plan cache maximum size limit to a size similar to that of SQL Server 2000, the limit for signaling internal memory pressure on a cache is changed from that of SQL Server 2005 RTM and SP1. The following table shows how the maximum limit of the plan cache is determined for each version of SQL Server:

SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB

SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

Example:
For a SQL Server with 32Gb total SQL server memory,
SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB

SQL Server 2005 SP2 cachestore limit will be 75% X 4 + 10% X (32-4) = 5.8GB

I suppose I was not the only one who thought this was just too much. I love the way SQL Server is self tuning for a great part but maybe it would be nice to allow expert users to configure some aspects themselfs anyway, especially with x64 where memory usage is lifted to a whole new dimension. When looking at the fixlist for Service Pack 2 I am getting more and more convinced to wait for it before installing SQL Server 2005 in large production environments.

Troubleshooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1

The SQL Programmability team have started a series of posts that everyone - using SQL Server 2005 - should read (not for the faint of heart though).

They could help you isolate performance issues very fast and we all know this can be the difference between success or catastrophic failure.