Real Life Microsoft IT : Systems

SQL Server 2005: The new "missing indexes" feature

admin — Thu, 11 May 2006 20:33:00 GMT

There is an interesting and useful new feature in SQL 2005 where it takes all queries that execute and don't find an optimal index...and records the information. You can use this information to generate indexes and speed up your performance. These are only held as long as your SQL Server is running...rebooting it will flush out any information stored.

You get the information from a built in view documented in MSDN: sys.dm_db_missing_index_details
http://msdn2.microsoft.com/en-us/library/ms345405.aspx

However, sys.dm_db_missing_index_details only take you half of the way home. It lists the pieces of the index to be created, but still requires you to construct the CREATE NONCLUSTERED INDEX SQL yourself. This requires you to learn how to do it (from the above link)...which means understanding the internals of how it works. (According to this 2nd link, the SQL team initially implemented the "whole enchalida" and then later removed it: http://solidqualitylearning.com/blogs/mike/archive/2005/09/26/1455.aspx )

The link above argues that this is a good thing, becuase it says that these are just suggestions rather than guarantees (and you still need to verify that you want to truly run each one). If this is true...I would argue back that forcing someone to piece together everything to create the query doesn't reinforce the fact that it's a suggestion. It just aggravates the DBA. The proper way to do this would be to just document that these are just suggestions in the reference on this stored procedure...not make it harder on the DBA who might have to implement it.

Anyway...fortunately for you (or unfortunately...depending on your point of view), I've created the following that will create the SQL for you. :) What it does is takes the top 10 queries that the view says would improve most from new indices and creates the SQL statement for them. You can adjust the # returned by changing: SELECT TOP (10) group_handle.

SELECT     'CREATE NONCLUSTERED INDEX <NewNameHere> ON ' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
                       THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
                      THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
                      THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
                      mid.included_columns
FROM         sys.dm_db_missing_index_group_stats AS migs INNER JOIN
                      sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN
                      sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle INNER JOIN
                      sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
WHERE     (migs.group_handle IN
                          (SELECT     TOP (10) group_handle
                            FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
                            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

Sample Output:
CREATE NONCLUSTERED INDEX <NewNameHere> ON MYtable ( [PId], [PersonId], [Hidden] ) INCLUDE ([Id], [BRId], [Amount], [Comment], [OtherDate], [ParentBId]);

Caveats:

1) Before adding each one...review it and make sure you really want to add it to your database. Per
http://solidqualitylearning.com/blogs/mike/archive/2005/09/26/1455.aspx
"These are suggestions, not absolutes. It still requires a DBA to take a look at the index and decide whether it should be created."

2) Use this at your own risk. I experienced a few times where I followed the MSDN article's steps exactly (which is what this view is based on) and it wouldn't create the index properly. Example: sometimes the INCLUDE field returned by the system view has text fields in it. But actually using it in the INCLUDE results in this error:

Column 'TextField' in table 'myTable' is of a type that is invalid for use as included column in an index.

So test the SQL first on your dev server and make sure it's right.

3) The generated SQL doesn't do one thing recommended by MSDN. It says "To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first." Doing this automatically would take more time than I had to devote to this. So check the order of the SQL produced and if the index fields aren't in the order you'd like, you can change them around. And if anyone wishes to create something that handles this too...then please post it here.

Ian Ippolito

How SQL Server 2005 "Forced Parameterization" cut ad-hoc query CPU usage by 85%

admin — Mon, 01 May 2006 00:11:00 GMT

Is it my imagination or is SQL 2005 running slower?

My first impresson with SQL Server 2005 came after installing it on a test server. The test server is a dual processor machine...which was a pretty high powered box back in it's hey-day, but these days it's a commodity server. It runs both SQL Server and IIS,so it is more sensitive to load problems than the production server. This make it a good "canary in a coal-mine", because it detects and helps flush out performance problems early in the process.

The first thing I noticed with 2005, was that every time I rebooted the machine, the first time I ran a particular web page (which ran queries to SQL ), SQL Server 2005 would take a really long time to return the results. In 2000, it would run in about 7 seconds, but in 2005 it would timeout the web page (meaning it took at least 10 seconds). But after that first call it would be fine..and would actually run faster than 7 seconds. This weird problem would only occur after every reboot...and would then disappear.

Since I had hundreds of other tasks that needed to be done for the upgrade, and it only happened on bootup, I simply increased the timeout on the page to 15 seconds, and pressed on to other matters. But it stayed in the back of my mind.

SQL Server 2005: Faster to Execute but Slower to Compile?

All versions of SQL Server first compile a query and then execute it. I have seen many times where SQL Server 2005 executes queries faster than 2000...but I have also seen many times where it took longer to compile them in the first place. Most likely the two are related...it takes more time to create a better query plan.

This isn't necessarily a bad thing. In fact, if 100% of your workload is stored procedures, it is a great improvement. That is because stored procs are precompiled, so they are only compiled once and executed many times. So if this is your situation, then you will save many multiples of time more than you are giving up.

BUT...if on the other hand if your workload has a large percentage of ad hoc queries (for example: SQL called from a programming language like ASP.NET), then the situation will be reversed. That is becuase ad hoc queries have to be compiled each and every time they need to be run. And if this is your case, then they will run slower in 2005 than in 2000.

Workarounds

In an ideal world this would not be a problem, becuase everything would be written in stored procedures. However, this ideal case doesn't always exist. Some apps use 3rd party middle tier components which preclude the abilty to implement stored procs. Others require the ability to run on multiple vendor databases...meaning they can only use vendor-neutral SQL.

The more typical application is homegrown and designed only for SQL Server, so it doesn't have any of the above problems. These apps typically use stored procs when they can, but but use an adhoc query when they can't. For example, a search screen with many paramaters creates very complex SQL that morphs dramatically from query to query. If you choose one set of options, you might get a simple query, and if you choose others, you may have to INNER or OUTER JOIN to 10-11 additional tables, new fields, WHERE, HAVING, GROUP BY clauses, etc. This easy to code in a declarative language like .NET. But this sort of structure is an exponentially growing nightmare in a true TSQL stored procedure. .NET stored procs are a leg up on TSQL and can make non-set based operations easier to do. But they are little slower than TSQL. More imporantly, all stored procedures (including .NET, TSQL and even dynamic TSQL), cause a fundamental programming problem. Coding them requires maintaining an interface in two places...in the stored proc and in the code. Every change requires two changes rather than one...and every time new functionality is added, this usually requires an interface change. This translates into double the development time, double the chances for bugs and double the work.

Is there another way?

But what if there were a way to get:
1) The performance of a TSQL stored proc
2) The power and flexibility of a declarative language
3) That only requires one interface?

Before you say "Keep dreaming!"...I'd recommend that.you read the book "Good to Great". In that book the author talks about "good" companies being ruled by the "tyranny of the OR" and "great" companies believing in the "genius of the and". For example, a "good" company would believe they could either offer the cheapest price, or good quality but not both. A "great" company would find a way to do both.

Is it possible to harness the "genuis of the AND" in our database situation? The developers of SQL Server 2005 dared to ask that question.  And the result of that is a wonderous creation.  The name doesn't do it enough justice and has probably caused it to be one of the least heralded and unheard of features in SQL Server 2005. (Do a search on Google and you'll find perhaps 6 mentions on the entire web...none in Microsoft's marketing.) This little known feature is called "forced parameterization".

That sounds familiar

You may have heard of the term "parmaterization" before. Simple parameterization was SQL 2000's first attempt at finding the "genius of the AND".   Genius solutions don't come easily... and this first attempt was a nice step in the right direction but ultimately fell short.

Basically, SQL would analyze your non stored proc queries and figure out for itself if it could create a query plan on the fly with it. It did this by subbing out some of the values you entered with paramaters that could be reused by similar queries. The only problem was that it was very simple and didn't handle many real life situations (such as an IN clause for excample). Worse, changing the query just a little bit could often "fool" it into not recognizing it as being the same as the original, and it wouldn't "kick in". So while simple parmaterization helped a little, it wasn't enough of a solution to solve the problem.

Microsoft put the intervening 5 years between 2005 and 2005 to good use and basically revamped this code entirely and pumped it up on steriods.  Possibly the incredible increase in CPU processing power in those 5 years led them to believe that their customers would have the horse power to enable such a solution to run effectively. They made it more robust, more dynamic and more powerful, and yet kept it lightning quick.  They enabled it to do handle a much larger variety of queries. And most importantly...if it wasn't able to figure out a certain part of the query, it didn't just "give up" (as simple paramaterization did/does)...it just skipped that small portion and paramaterized the rest.

They called this feature "forced paramaterization".

What does this mean in real life?

So what does this mean to you? I ran this test during an offpeak time period, but during which a large # of ad hoc queries are typically run. With simple paramaterization (which is on by default), the result was a 25% average CPU utilization:

Not bad. So then I turned on forced parameterization. It's done at the database level under "properties" and "options". This was the result:

At first I thought something had to be wrong. The processor usage dropped to 1/5th of what it had been before...just 5%! Had everyone stopped using the database all of a sudden? I paniced for a minute and then settled down once I confirmed that there was no site-wide emergency and things were actually running normally. I watched it very closely for 10 minutes, and "force parameterization" took on query after query like a champ and didn't even break a sweat. The # of cached query plans rose as the CPU kept low. Clearly the compiling of the ad hoc queries was a bottleneck before-hand and "forced parameterization" wiped out that bottleneck like a truck running over an ice cream cone!

And the best thing about it is that it tends to get better with time. As more queries run, more get cached, and your server runs faster and faster. By removing that compiling bottleneck your ad-hoc queries fly!

Is there a Downside?

Genius doesn't come easy. There are some cases where even forced paramaterization doesn't work. It may have a tendency to make your queries not use indexed views (if you are using them). And now that your ad hoc queries have the pre-compilation powers of stored procs, they have the downsides of that power as well. Particularly sometimes a compiled plan for a typical situation doesn't work right for an outlier situation (or vice versa) and can effect performance. But this is a rare situation and can be handled on a case by case basis if and when it happens. You can read the help files on the feature for more information on the pros and cons of forced parameterization and decide for yourself when it might or might not be appropriate for you.

But for us, it has been a godsend and we will continue to use it in production. And I can't wait to see how this feature expands in the next release.

Kudos to the SQL Server 2005 team on this little talked about but great feature.

Links:
Here is a good blog entry from a Microsoft SQL Server developer named Cihangir Biyikoglu on the feature:
http://blogs.msdn.com/cbiyikoglu/archive/2005/11/03/488920.aspx

Ian Ippolito

A "must use" SQL Server 2005 Feature-ROW_NUMBER()

admin — Sat, 29 Apr 2006 15:30:00 GMT

If you run your SQL Server as the back end for a website, then paging is one of the most common things that you do. In SQL Server 2000, doing this paging was complicated and resource intensive. But in 2005, there is a new feature called ROW_NUMBER than you can combine with BETWEEN that will give you orders of magnitude improvement.

SQL Server Processor utlization before ROW_NUMBER (Last Saturday):

SQL Server Processor utlization after ROW_NUMBER (the cutover is plainly visible at around 1:30 AM this Saturday):

When pushed to production, he CPU INSTANTANEOUSLY dropped from an average of about 50% CPU toabout 25% CPU, and stayed there! That's a 50% reduction in CPU just from one query change! If you run a website with SQL Server, this should be one of the FIRST things you look at after you upgrade.

Here are the best sources I found on how to implement it:

1) http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx

Adam Mechanic ran some tests on the new ROW_NUMBER feature using paging and shows dramatic results. The SQL 2000 style SQL returns a cost of 5,516. The new 2005 style SQL returns a cost of 0.202!

SQL 2000 style:

SELECT x.SomewhatLargeString
FROM (
SELECT TOP 20010 A.SomewhatLargeString, COUNT(*) AS TheCount
FROM #BigTableOfStrings A
JOIN #BigTableOfStrings B ON B.SomewhatLargeString <= A.SomewhatLargeString
GROUP BY A.SomewhatLargeString
ORDER BY A.SomewhatLargeString ) x
WHERE x.TheCount BETWEEN 20001 AND 20010

SQL 2005 Style:

SELECT x.SomewhatLargeString,x.TheCount
FROM (
SELECT TOP 20010 A.SomewhatLargeString, ROW_NUMBER() OVER(ORDER BY A.SomewhatLargeString) AS TheCount
FROM #BigTableOfStrings A
ORDER BY A.SomewhatLargeString) x
WHERE x.TheCount BETWEEN 20000 AND 20010

When you look at any new queries implementing ROW_NUMBER with BETWEEN, you may first wonder why there are 2 queries (the base query nested inside a top level query). I did too. I wondered, "Why not combine it into one query"? The reason was clarified to me in a support ticket to Microsoft while trying to do this with a SELECT DISTINCT subquery. By design, SQL Server cannot referenced an aliased field using a WHERE clause (which is where the BETWEEN is located). And you can't reference the "actual" field name for ROW_NUMBER(), becuase there is no actual field name. So to work around it, the solution is to wrap the query in a top level query that pull at least the aliased field...and in that process, the field now becomes the "actual" field name at the top level and can be referenced by the WHERE/BETWEEN. If that doesn't make sense...you don't need to understand it to use it...just copy the sytax above and USE IT.

The other tip is to also use the TOP setting in the inner select and set it to the highest # in your range of rows. It will speed up the query and you don't need any rows past that anyway, so it's a "free boost".

2) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Microsoft's information

Ian Ippolito

SQL Server 2005 performance testing with hyperthreading and MAX DOP

admin — Wed, 19 Apr 2006 03:20:00 GMT

There's been alot of negative publicity about hyperthreading (see "Hyperthreading hurts server performance, say developers" which is all over the net...including: http://news.zdnet.co.uk/0,39020330,39237341,00.htm)

We had been experiencing disapointing results upgrading from a 32 bit dual processor/single core machine to a 64-bit quad processors/dual core machine with hyperthreading...so I figured this was a good setting to experiment with.

The other setting that gets alot of attention is the "Max Degree of Parallelism--Max DOP". This setting tells SQL Server how many processors to use when running a multiple query. If set to 0, it uses all available processors. Otherwise you can limit it to a lower # using this paramater. The advice on the internet ranges from "leave it to 0" (which means use all processors), to "set it to the # of processors but don't count your hyperthreaded processors as 2" to "set it to 1". So this was also a good setting to experiment with.

Environment:

So I performed several tests using the http://www.RentACoder.com database to determine if it would help. I picked a period of the night when the traffic was low (in case something got really messed up) but relatively constant and no odd batch jobs or mailing lists were running.
Equipment: Quad processor (3.1 ghz) Dual Core 64-bit machine with 16GB memory.
Software: SQL Server 2005 64 bit edition and Windows 2003 SP1.
(note: some articles said that Windows 2003 SP1 was the most optimized system for hyperthreading).

Raw Test results:

1)Baseline: Hyperthreading on...MAXDOP=0: At 9PM (a lull period) with hyperthreading enabled, the CPU usage ranged from 50-60%. The MAXDOP=0 used 16 virtual processors (4 physical x 2 cores/processor x 2 hyperthreads/processor)
2) Hyperthreading off...MAXDOP=0. The CPU usage increased a small but noticable amount...and ranged from 60-70%. The MAXDOP=0 used 8 virtual processors (4 physical x 2 cores/processor)
3) Hyperthreading off...MAXDOP=4. This test tested setting it to the # of physical processors (4). This was a CPU killer. Usage spiked to 90-100%.

Conclusion:

In this case, hyperthreading really did produce a measureable boost in performance of 10%. Deactivating it permanently, would have dropped performance unnecessarily.

Setting MAXDOP = to the # of physical processors really killed performance...the default MAXDOP setting=0 performed much better. Note: I didn't try changing MAXDOP with hyperthreading enabled...perhaps that is a test for another time.

Bottom Line:

Don't trust "experts" who tell you that hyperthreading doesn't work...test it for yourself. You may get a boost as we do from it. MAXDOP tuning did not produce an increase in performance and actually hurt performance. So if you experiment do so during a lull period where you won't flood yourself with complaints if your CPU usage spikes.

Ian Ippolito

Classic ASP: Not ready for prime-time?

admin — Sun, 19 Mar 2006 15:56:00 GMT

There is a VERY bad problem with Active Server Pages (ASP...or what is sometimes known as "classic ASP") which prevents it from being a viable system on which to build a "big IT" project. For 2 years we've been fighting a problem in which the IIS (Internet Information Server) 6.0 web servers will run fine for a while. But then after 18-20 hours, every pages will stop running properly and instead display random errors (that change from refresh of the page to refresh). The errors always say things like "Cannot find property or method" regarding classes that do exist, or "Cannot create object". The exact propery or object changes from "refresh" to "resfresh"...even on the same page. Restarting will fix the problem for an hour or so...but then it reoccurs. Only a hard reboot will fix it for another 18-20 hours. And then the cycle repeats itself.

We started a ticket over 2 years on this. Today Microsoft acknowledged the problem publicly with a KB article: http://support.microsoft.com/?scid=kb;en-us;914156

Basically the problem is that ASP fragments the heap under certain conditions:
1) When an app uses INCLUDE files
2) When an app uses significant memory
And it doesn't defragment the memory and as a result, will ALWAYS crash IIS in this way. The new restart services of IIS 6.0 don't help, becuase only a hard reboot can truly get rid of the defragmentation.

Of course, two things that any site that has any sophistication has lots of INCLUDE files and any site that has any traffic uses lots of memory.  If both of these apply to you, your ASP site is in trouble. This is what's called a "bug" in my book. I'm sure if ASP were MSFT's current technology they would have to fix this. But Microsoft has moved on to ASP.NET...so I suspect the incentive is pretty much nil.

If you look at the KB article, Microsoft gives a "workaround" (which is the same workaround that they suggested to us). The workaround is to rewrite your INCLUDE files as COM objects. The article sites a # of benefits like speed, etc..

The KB was probably written by a systems engineer who knows alot about running systems, but has no idea how computer software is developed, and how impractical this workaround is in the real world. In a previous position we went down the COM object route. We did it for other reasons...becuase it was the Microsoft "best practice" at the time. It's obvious why it no longer is. The problems we ran into were:

1) Memory: COM objects have a known memory leak problem. If two COM objects reference each other and you set them to Nothing, they don't clear themselves in memory as they should. The problem is due to the internal way that COM objects work and was fixed in .NET with a rearchitecture. But that isn't an option for you if you use COM objects. This COM memory leak bug will cause your machine to eventually crash, EVERY TIME.

2) Slowness to initialize objects: COM objects are not designed to be created and destroyed thousands of times like .NET objects.   Our object rich COM model running on the internet was VERY slow...some pages took 5-10 seconds to render.  One way to work around this is to not use objects in COM, but instead use functions. But of course, this "solution" causes almost as many problems. A non-object oriented framework is exponentially more difficult to debug and maintain which makes delivering the code on time and on budget frustratingly (and unnecessarily) difficult.

3) Slowness to develop: This was a real show stopper. Every time a change is made in the COM object code (the COM object has to be completely recompiled from scratch, which took 2-3 minutes on a state of the art machines (This # could vary depending on the size of your COM objects). Also, many times IIS would lock the COM object .dll, so in addition, IIS had to be stopped and restarted too, which took another minute or two. (The latter might be alleviated with object pooling, but all the other negatives forced us to abandon COM before even looking at this possibility).

How does this affect things? When code is created, it is never right the first time. Even creating a single function is an iterative process where it's coded, run, bugs are removed, and the process repeated. A typically complex function created by a top notch developer might require 3-4 iterations...and an average developer might require 10,15 or more.   Being able to iterate quickly is what allows you to deliver it on time and on budget. If creating just a single function requires 9 more minutes for your "star" developer and 30 more minutes for your average developers, you are going to have problems. It is FRUSTRATINGLY difficult to develop under these circumstances and try to meet time and budget commitments.

I tried to explain to the Microsoft systems engineer why this was impractical, but simply got the standard "spiel" repeated to me that COM objects were wonderful and the way to go. Obviously he believed something he read from a KB article, more than me, who he did not realize had personal experience with this supposed "solution".

Conclusion: If you develop a web app with any sophisticiation or any size, ASP (classic ASP) is NOT a suitable or scalable environment for you. Instead go to a non Microsoft platform. If you choose to say with Microsoft, choose the newer ASP.NET.

Ian Ippolito

SQL Server 2005: Are your batteries half full or half empty?

admin — Sat, 18 Mar 2006 00:21:00 GMT

Microsoft is running an ad campaign showing skyscraper sized batteries labeled "SQL Server 2005" plugged into enormous buildings. The message is obviously that this database is designed for big companies and serious mission critical systems.

SQL 2005 is packed with impressive new features...too many to mention here. Some of them are truly awesome...like the new reporting capabilities which come as part of the purchase price, rather than having to buy a seperate system. Unfortuntely my experience with running the current version on a mission critical system (PRE Service Pack 1) has not been as impressive as I was hoping.

Along with the upgrade from SQL Server 2000 to 2005, we also simultaneously upgraded the hardware substantially. We went from a 32-bit dual processor box to an expensive "screamer"...a 64-bit quad processor box with dual core (so from two 32 bit processors to virtually eight 64 bit processors). And yet performance in SQL Server 2005 is no better than in 2000. And in some cases it's worse. What as going on?

A part of the problem was the Database Tuning Advisor (DTA). SQL 2005 has so many cool new features, but they are useless if you can't optimize the database. And that lack of optimization has caused increased timeouts, deadlocks and alot of personal user dissatisifaction with our service. The first problem is that the advisor takes a LONG time to run. Just tuning a query used to take a few seconds in 2000...now it goes through a multi step process lasting 4-5 minutes. Running a real workload is worse. But that's something you can learn to live with. But the problem is that the long wait times come with an unexpected companion...timeouts and deadlocks. The old 2000 let you analyze it "live" without any reprecusions (which I did may times). But don't make the mistake of running the current version of DTA on a database with a decent load on it. I don't know what DTA does, but every time I did it, it started terminating 1/2 of my users as deadlock victims so it could do it's business. And after about 45 minutes of this, it got worse becuase it just HUNG. And it was a bad hang too...becuase just restarting the service wasn't enough. I had to physically reboot the machine to allow clients to reconnect to it. It takes a long time for that beast of a machine to reboot (about 7 minutes), and the users were not pleased with me. Thank you DTA...may I have another?

Unfortunately, DTA's answers to that question is "yes you may!". There is the abilty to run DTA on a remote server to "lessen the load" on the production server (according to the MSFT documentation). In my case, load wasn't really the problem...the CPU never went above 50% on the production machine. It's the hanging that was a problem. Anyway, I discovered that for whatever reason, remote tuning stopped the hangs. But this only led to more problems.

At first I could never complete even a single tuning session becuase I would get a cryptic "Tuning process exited unexpectedly. (DTAEngine)". I was reduced to hitting "stop" at random points before it finished to get partial recommendations...anything to stop any of those timeouts. But I needed a real solution. So I opened a ticket with MSFT and they confirmed found a bug in DTA in dealing with indexed views (and is fixing it...ETA 3 weeks).

So after I learned that indexed views were the culprit I thought I'd at least be able to tune it 50% by setting DTA to tune "tables only". Smart idea right? Well...kind of. It worked...sometimes. But about 50% of the time DTA bombs out with "Index (zero based) must be greater than or equal to zero and less than the size of the argument list. Turning process finished." Nice and vague. And again, the crash stops any recommendations from being made. So yes, I had to open another Microsoft support ticket. I have six open right now...is that some sort of record? Sadly I think it probably is not. The worse thing is that Microsoft has duplicated this bug, but didn't fix it in SP1 and may not even issue a hotfix for it. The MSFT tech explained that they cannot fix it until enough people complain about it. So until enough people running big databases adopt SQL 2005 and run into these same issues...I am stuck dead in the water on this issue. But until they get problems like this solved...people are not going to be moving their databases over. A true "Catch 22".
(update: 5/8/2006: This will be made into a post SP1 hotfix...thanks to everyone else who also complained!)

Conclusion:

SQL 2005 has some awesome features. But it doesn't do any good your database runs slower than the last version becuase of the lack of DTA. My advice to anyone that asks is to wait for Microsoft to fix these bugs with SQL Server 2005 SP1 before jumping in with anything that is mission critical.

Ian Ippolito

Beware the Ides of ISA Service Pack 2 (SP2)

admin — Sat, 18 Mar 2006 00:18:00 GMT

I've confirmed that the SendAcceptEncodingHeader metabase setting that USED to work pre ISA (Internet Security and Acceleration Server) SP2 and allowed IIS to pass through the compression headers for gzip, is no longer working with SP2 (and passed this on to Microsoft).

The other option, is to of course use ISA SP2's spiffy new (and highly touted) compression. So I turned it on to give a run. It ran fine...for about 20 minutes. Then it crashed with an error of : "ISA Server was unable to decompress a response body from /RentACoder because the following error occurred: 0x80070008." I tried turning off the decompression (unchecked the checkbox on the 3rd tab)...and still got the same crash after about 30 minutes.

So I brought this up to Microsoft support, and the engineer passed on that Jim (the ISA manager) said that the compression features were designed for branch offices. Nice to know about that qualifier in advance, huh? :)

Unfortuantely I'm stuck between a rock and hard place with SP2. I installed it becuase Microsoft reworked their handling of large attachments, and they felt it would fix the ISA crashes I was having every 1-4 weeks. And it looks like it is doing the trick (no crash for 3.5 weeks now). But now my bandwidth costs are going to be 2x-4x higher this month than they should be due to ISA SP2's new "feature". I can't win either way. (The Microsoft tech I'm dealing with, Masoud, said he'll try to get duplicate this and get something going in a week or so...I hope he can pull it off).

However, it could be worse. I've done some reading on newsgroups and Microsoft is slammed with other problems in this SP2 concerning compression in the opposite direction...people browsing from behind ISA SP2 seem to have problems. Thankfully that isn't my situation too.

Ian Ippolito
http://www.RentACoder.com

4/27/2006: UPDATE: There is a workaround to fix this problem. If you completely disable the new and "improved compression .dll that SP2 has so generously forced upon you (but that doesn't work)...the compression metabase setting works again! To do this:
Go to "configuration", "add-in", "web filters" tab. Then disable "compression filter". Viola...enjoy!

Ian Ippolito