Real Life Microsoft IT

Microsoft Exchange 2005: Dealing with an email denial of service attack

2006-12-19T02:36:00Z

Microsoft Exchange 2005 has a # of features to make it easier to deal with spam (unsolicited email) with Intelligent Message Filtering. However dealing with an email denial of service attack is a slightly different story. A well constructed attack will probably be from someone who knows your company...and if they're smart, it won't be a spam-like message that would kick off the spam protection...but will look like an ordinary/legitimate email (albiet sent several hundred thousand times). You would think that Exchange would have something that built into it that would detect this. However Intelligent Message filtering has a large limitation in this regard...it only looks at single messages, and doesn't do any comparison across messages.

So what do you do when someone launches a DOS (denial of service) attack against you via email? It would be nice if Exchange could notify you of this...but it has no such feature. So the first time you'll find out about it, is that a user will complain that they have several hundred thousands more emails in their box than the expected. What do you do? Okay, first, take a deep breath. Then go to the headers of the emails and find the originating server. Cross your fingers and hope that they are all the same. If they are you have a simple DOS attack (a single server). You can block that IP in Exchange in "connection filtering", "global accept and deny list configuration". Click "deny" and type in that IP. Then cleanup the mess in the queues as described below.

If you have mulitiple IPs, you've got a tricker situation to deal with. Now you're under a DDOS (Distributed Denail of Service Attack). If there's only a few, then use the same technique as above (and then cleanup the mess in the queues as described below). But if the attacker is really tricky and you've got thousands, or if they constantly change...even this technique won't work. If that happens, you can't block it yourself. You need to contact your ISP becuase most ISPs have software that can stop a DDOS.

Cleaning up the mess in the queues
-----------------------------------
Now that the perimeter is secure, it's time to take out the garbage. Between the time the attack started, and you were able to cut it off, several hundred thousand or millions of email may be in the Exchange queues. They will slow your legitimate email to a crawl (not to mention potentially crash your server if you're low on disk space). So you need to deal with this problem promptly as well.

Again, there is nothing built into Exchange to help you. If you use the built in queue viewer, it will be completely overloaded and will either crash, or take hours to load 100 messages. At that rate it could take months or years to delete them all. You probably don't have that long to wait.

So instead, download this life saving tool from Microsoft called AQADMCLI.exe.
ftp://ftp.microsoft.com/pss/Tools/Exchange%20Support%20Tools/Aqadmcli/
This bad boy will delete queue messages that meet a certain specification. For example, running:
delmsg flags=sender,sender=hackeremail@lameaddress.com
will delete all messages send from the email hackeremail@lameaddress.com. There are a ton of other flgs, so you can key on sender, receipient, etc.

When you run this, the first thing you'll notice is that it appears to do nothing. In actuality, it's running and just doesn't show any output until it's completely done with each queue. If you have a few hundred thousand emails, that will take time. So take a quick coffee break and come back. When you do, your queues should be back under control.

One caveat...this tool does not seem to work on the "message pending submission" queue. If you've been attacked, and have an autoresponder on the email address (a favorite target for hackers) you will most likely have several hundred thousand emails in this queue as well. What do you do?
1) The messages are stored in a physical folder. for example: c:\program files\MyExchsrvr\mailroot\vsi 1\queue. Make note of that folder.
2) Stop all exchange services (including your antivirus, gfi mail essentials if you run it,etc.)
3) Rename that folder to something else (you can't do this unless ALL services are stopped...so if something interferes, you missed something). Example: "queue_old".
4) Restart all services. Your queue is no longer backed up...because the messages are no longer in it.
5) Now you have to salvage the good messages from the garbage ones. Do a windows search on the .eml files and delete the bad ones, or move the good ones into a seperate folder. (Yes it takes a while...but it's faster than doing nothing and hoping the queues eventually clear out on their own).
6) When you have the good ones, copy them to the \pickup folder. They will be requeued.

I hope this helps you out.

Ian Ippolito

Technorati post claim:please ignore

2006-11-16T16:33:00Z

Please ignore this posting...it is for Technorati (so the blog can be properly claimed as belonging to Rent a Coder/ Exhedra).

Technorati Profile

Technorati Profile

End post

SQL Server 2005 Database Mirroring Versus Oracle Data Guard

2006-06-01T16:22:00Z

The new database mirroring feature in SQL Server 2005 SP1 is pretty cool and should help availability.

But it still has a # of serious drawbacks that prevent it from being as useful as it could be.

The primary one from my point of view is that you can't "use" the mirror server (well) as a read only server. Yes, there is a workaround from MSFT where you can take snapshots periodically so that you can use it as a read only server. But of course the data is delayed...making is useless to offload read processing for your online system...and relegating it to a delayed reporting server only. You could increase the frequency of the snapshots, but this stops working after a while, becuase if you do it too often, you start delaying access to the data while it's being generated. So it has limited usage.

Below is an interesting article from Oracle comparing 2005 Database Mirroring to "Oracle Data Guard". It talks about some of the flaws and problems and mirroring, and how Oracle's product (which is more mature) doesn't have these issues. Oracle even has a feature to undo human errors, and many other really useful things that I'd love to see in the next version of SQL Server. Hopefully someone from Microsoft has already read this and is working hard on these things for version #2?

http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardDatabaseMirroring.html

Ian Ippolito

SQL Server 2005: The new "missing indexes" feature

2006-05-11T20:33:00Z

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%

2006-05-01T00:11:00Z

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()

2006-04-29T15:30:00Z

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

Lessons learned from upgrading from SQL 2000 to SQL Server 2005

2006-04-28T19:48:00Z

We recently upgraded from SQL Server 2000 to SQL 2005. We simultaneously did a serious upgrade of the hardware. The old server was a dual processor 32 bit machine. The new server is quad processor 64 bit machine. The dual core PLUS hyperthreading makes it appear as 16 virtual processors to the operating sytem:

Yet despite throwing tremendous amounts of hardware for SQL Server to use, we experienced terrible performance after upgrading to 2005. Getting the performance to the level where it was acceptable took literally weeks of work. Detailed knowledge of SQL Server internals was gathered by porting over release notes, MSDN articles, personal blogs written by Microsoft SQL Server developers, 3rd party website and the help of several different people in Microsoft support. This blog entry was created so that anyone else going through the same process and experiencing similar problems would have a road map to follow:
---------------------------------------------------------

1) INSTALL Service Pack 1. Now that's it available...this is a "no brainer". You will save yourself weeks of aggravation that those of us unfortunate who migrated pre-SP1 experienced. From the enahancement list:

"Several performance problems were fixed in the Database Engine query optimizer. These changes improve the upgrade process for third-party products that use SQL Server as their back-end database when you upgrade from SQL Server 2000 to SQL Server 2005."

There are also some very necessary fixes such as:

a) pre SP1, the database maintainence tasks for deleting .TRN and .BAK files didn't offer the ability to delete recursively on subfolders (as it used to in 2000). So it was a step backwards, and without it, sites with more than 1 database have to create a manual step for EVERY database added. This added alot of unnecessary administrative overhead. This oversight was restored/fixed in SP1.

b) DTA bomb outs: The Database Tuning Advisor would bomb out quite easily, and we were forced to do thing like loading workloads a small piece at a time and seperating runs for tables from indexed views and limiting the time it could run. Thankfully, this has been fixed.

2) Migration:

MANUALLY Update Statistics. http://msdn2.microsoft.com/en-us/library/ms144267(SQL.90).aspx

sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'

Yes, your database is probably set for "auto update statistics" and the English meaning of "auto" means it's a substitude having to do something manually. But just becuase the feature is named that way, doesn't mean that it acts that way. For the first few hours after migration, we thought we might be able to get away without updating stats. So as a test I ran the same query thousands of times. But the "auto" never seemed to kick in. Maybe the "auto" was a Yugo because the performance was dismal. So I took the hit and ran stats (it takes about 13-15 hours on our database due to the size...even with all of our hardware)..and what a difference it made. Throughput sped up on the query about 3x.

Why is this necessary? The engine for 2005 is so different than it was back in 2000 that the 2000 stats are of little use. So this should be the first thing you do after upgrading to SP1...even if it increases your down time...it is worth it!
Rebuild your indices (optional...but highly recommended):

Microsoft advertises SQL Server's advantage over Oracle as the fact that it is self-tuning. This is "compatively" true, if you compare it to Oracle (which seems like a prima-donna compared to SQL Server). But it's not "absolutely" true.

Some people are not aware that things like indices degrade with every INSERT/UPDATE and fragment. This is just like disk fragmentation...it slows down your server more and more over time until performance becomes unacceptable. So since your database is down for the migration, it's also a good time to rebuild them. Yes you can do a reorganization/defrag without taking it online, but this process is not as thorough and can't remove all types of fragmentation.

But what about "online rebuilding of indices" in 2005...you don't really need to take it offline to build it anymore do you? Well, the answer to that you can see in another of my blog postings. Like just say that real world databases are almost guaranteed to have a large # of tables that online rebuilding doesn't yet handle (maybe in SQL Server 2010)? Anyway, my advice is that since you're already offline to do the migration, do this offline too and get a fresh start with your indexes.
Update your usage stats.

DBCC UPDATEUSAGE

This is a little trick that is on MSDN. It avoids having your statistics messed up and performance suffering becuase of it..

"In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, we recommend that you run DBCC UPDATEUSAGE on all databases following upgrade."

http://msdn2.microsoft.com/en-us/library/ms144267(SQL.90).aspx
Update the compatibility level.

I know you're thinking "Wait a minute. I went through all the work and stress of upgrading this thing to 2005 and it's up and running, and you're telling me it isn't truly 2005 compatible yet"? The answer is "Believe it or not...yes".

It actually makes sense (although it would be nice to know about this in the upgrade wizard, rather than having to fish it out indirectly). But your upgraded database is set to be 2000 compatible, and won't support many of the cool new 2005 features such as Management views, etc. So you need to upgrade it:

sp_dbcmptLevel <dbname>,90
(where 90=sql 2005)

A word of warning...Microsoft doesn't upgrade the compatibility for a reason. It's becuase query behavior changes in 2005 on some things. So check out the 2005 documentation on the differences, update your apps for any changes (you've probably already done this before upgrading), and then you're ready to do the above.

3) Operating System settings

If you're running a quad processor machine and installed Windows 2003 x64 Standard Edition (even with SP1), then the operating system supports your 4 processors fully, right? WRONG!

If this is your situation, you need to buy an upgrade license to R2 right away. Most people think that R2 is an excuse by Microsoft to charge you for a bunch of stuff that knowledgeable people can already download for free off of the internet. That is true for a majority of the features.

However, there is a legitimate reason to upgrade to R2 that makes it worth paying for, that you can't get via a free download. Pre R2 supports 4 processors...but not SYMETRIC processing. What this means is that if you have a workload, pre R2 will fill up your first cpu until it's bursting at the seams at 100% (leaving 2 through 4 compeltely idle). Only then will it go to #2 (leaving 3 and 4 idle). Then when it's bursting at the seams, it will go to #3 and so on. This is extremely inefficient. It's like buying a high performance race car, and driving it with a manual transmission and wondering why it fells like your Honda Accord. And since SQL Server accesses the processors through the OS (it doesn't have the ability to access hardware directly)...your SQL Server is doing the same thing.

R2 on the other hand supports symetric processing. What this means is that the workload will be distributed evenly on all 4 processors, keeping them smoothly running.

So if you have 4 processors, UPGRADE TODAY.

Incidentally, this was hard won information. It took two seperately opened incidents and talking to 4 different Microsoft people before I was directed to a very helpful person in the OS team who knew and explained the difference and the consequences.

4) Hardware

The cool new hyperthreading on your expensive new box is advertised by Intel as increasing your performance. And that "could" be true. It also could be true that it is making your system run like a dog. Here are some examples of the latter:

http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx

The only way to know for sure, is to test your server under load with it on and off and see the difference. For us, hyperthreading works...it gave us a 10% boost:

https://www.rentacoder.com/CS/blogs/real_life_it/archive/2006/04/18/SQL_Server_2005_performance_testing_with_hyperthreading_and_MAX_DOP.aspx

But if you're having performance problems, you need to check this out for yourself.
Adjust MAXDOP (Max Degree of parrellism) for your # of processors.

If you're upgrading from an older non-hyperthreaded/non-dual core machine to one that is...this is important for you.

MAXDOP controls the maximum # of "processors" that SQL Server uses in a parallel query. The "processors" are actually virtual processors, because you may be like us and have 4 processors, but when you add in hypthreading (x2) and dual core (x2 again) SQL Server considers it 16 "processors".

If you set MAXDOP to a #, SQL Server will only use that # of processors in parralel queries. By deafult, SQL Server sets MAXDOP=0 which means it will be set to wahtever # of processors SQL Sever can see. On your old machine, this was a good setting, because MAXDOP= the # of physical processors (which was the old recommended practice).

However, now that your new machine has dual core and hyperthreading, that is no longer what you want. Why is this an issue? Don't you want to maximize the use of your processors? The answer is "no". That is because there is overhead in every extra processor used in a parrallel query. Add a small # and it makes the query run faster. But as you continue adding them, the overhead builds up, and eventually can cause the query to run slower than if you had just left it running on a single processor.

The old/traditional advice was to set MAXDOP=# of physical processors and ignore hyperthreading: http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx

However, nowhere in that blog or anywhere on the net could I find what to do about dual core processors. Should those be counted and added to MAXDOP or not? I ahd to call Microsoft support to get the updated answer. I was told that the new rule is that MAXDOP should be set to the # of "physical sockets". And each core is considered a physical socket. So for us we have 4 CPUs x2 (because they are dual core)=8 physical sockets. So our proper setting is MAXDOP=8.

This little jewel was hard won information...so I hope it helps you.
Memory: Beef it up!

Beefing up your memory is always a good idea. But there is an even more pressing reason to do it if you have a multi gigabyte database.

As of this moment there is a bug/feature (even after installing post SP1) in SQL Server 2005 backups. When you do your nightly full backup and do a "verify" on it, it will drain memory while it is doing it. A Microsoft tech theorized that perhaps it was loading the entire file into memory. In our case, we have 16GB of memory and a 60GB database. So the math tells you already what happens every night. All the memory is sucked out...down to the last MB, and eventually forces SQL Server to give up memory that it has claimed (flushing caches, etc.) It takes a good 45 minutes to run, even on our high speed machine, so during this time, users are treated to timeouts and pages that take extremely long to run (if at all).

The workaround was to turn off "verify" and copy it to a 2nd system and verify it there. Unfortunately, for some reason the copy process also causes a similar drain (which Microsoft is looking at right now). So currently...both the bug/feature and the workaround cause the same problem.

So the current temporary solution is that the database has several extra GIGs of memory installed and SQL Server to set to not use it. This helps allevaite the effects of the drain to a more tolerable 10-15 minutes (rather than 45).

Long term we are waiting on a true solution.

Software

Tune with DTA--but carefully.

Database Tuning Advisor will run on a trace of your production workloads and automatically tune the indices and stats so that it will be optimized for your situation. This is a great feature and you should be running this often.

But HOW you've run it has changed (at least for us) in 2005.   If you run a 24/7 environment (such as an international web site like we do) then it will change for you as well.

In SQL Server 2000 we could run the tuning advisor on the database directly (to get recommendations only...not actually apply them. That would be done later.). We could do this at any time and it wouldn't lock out people or cause any problems other than a little more load. But that is no longer the case in 2005. We tried it 3 times, and all 3 times it started causing deadlocks and all sorts of problems.  On two ocassions, just stopping DTA didn't fix the problem...and we had to physically reboot the server to release the locks. A 24/7 database can't be taken down every time you need to run DTA.

There is a new remote tuning option in DTA where you setup a 2nd SQL server and it will copy the table structure of your production database to a 2nd database and tune it from there. This not only helps prevent overloading your production server, but it stops the locking while the process is running...at least in theory. Again, real life practice showed something else.

We tried it several times during off-peak hours, and a few times we got it to work okay. However most of the time it would also produce timeouts for users while copying the tables. (We have a ticket open with MSFT on this issue).

So, the workaround is that we copy a backup to the 2nd server and then run DTA on it there, and take the recommendations and run them on production during off peak hours. It isn't ideal, because copying a multi GB file even over a GB network is slow, and it really slows down the # of iterations of DTA we can run.   However it is better than not running DTA at all, so perhaps this idea will be useful to you.

--------------------------------------

Side note: IMPORTANT BAD side effect of DTA bombouts that you should know about!

DTA creates hypothetical statistics and indices on your database and then uses them to make its recomendations. When it's done it cleans them out. However if DTA bombs out (as it did often pre SP1), this can cause a problem. It means bogus/hypoetical indices and stats will be on your production database. Any query can come in and pick one of them instead of your good/real indices and stats and your performance will suffer becuase of it. Also, they will slow down your stats refreshing and index defragging jobs unnecessarily.

A MSFT tech working on another issue found turned us onto this and we found over 4000 of these dead objects in our database.  (Apparently pre SQL 2000 SP4 or SP5 there was another flaw that would cause these objects to accumulate as well...and this database is old enough to have been around back then). And sure enough, some were being used by queries and producing sub-optimal plans and contributing to bad performance.

So if DTA crashes, makes sure you clean up its mess. Examples:

drop statistics [YourTable].[hind_88491494_3A_2A_8A]
drop statistics [YourTable].[hind_88491494_1A_4A_8A]

This is perhaps another reason to optimize on a 2nd machine and not the production server. Here is MSDN info on deleting hypothetical indices:
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
If you use SQL Server as the back end for a website that offers users the ability to page through data, swap out your inefficient SQL Server 2005 paging queries for the new ROW_NUMBER() feature. Doing this on the main query used on the site dropped processor utlization an eye popping 50%! Details are here:
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/29/481.aspx
Strongly consider "Forcing Paramaterization"

This this new 2005 feature cut CPU performance on an adhoc query load by 1/5th. It's almost too good to believe and is probably the most underhyped yet best feature in SQL Server 2005 (far better than the ubiquitously marketed "online index rebuilding" feature which don't stand up to real life situations):
Details are here:
https://www.rentacoder.com/CS/blogs/real_life_it/archive/2006/04/30/487.aspx
5/26/2006:

If your multi processor machine is not working as quickly as it should...strongly consider installing the just released SQL Server Cumulative hotfix package which fixes this issue:
"Processing performance on multiprocessor computers is somewhat slower than expected."
http://support.microsoft.com/kb/918222

Ian Ippolito

SQL Server 2005 Service Pack 1: The good news and the bad news

2006-04-27T03:01:00Z

SQL Server 2005 Service Pack 1 (SP1)includes many bug fixes and improvements to the core SQL Server 2005 engine. Given the poor performance we had been experiencing after upgrading from SQL Server 2000 (despite simultaneously doubling the processors, tripling the memory, increasing hard drive speed, etc.) I was excited to try this out. This quote from the above URL looked especially promising:

"Several performance problems were fixed in the Database Engine query optimizer. These changes improve the upgrade process for third-party products that use SQL Server as their back-end database when you upgrade from SQL Server 2000 to SQL Server 2005."

What happened?

The good:

Via an off-the cuff estimate, it appears that things are indeed running slightly better. The processor was averaging around 92% CPU usage on Monday and dropped to 84% today after installation of SP1.

However, when put into perspective...the old SQL 2000 server (with half the machine, 1/3 the memory etc) was running at 80-90% CPU. So this is just one small step in the final journey. But nonetheless...it's a step in the right direction.

The bad/ugly:

Now for the bad news. After installing SP1 I thought everything worked fine. Today there were some problems with a replicated table. After probing here is what greeted me in replication monitor:

A complete 100% failure of all replication on every single publiccation and subscription to every single database. I didn't realize right away, because database mail was not working properly pre SP1 on x64 machines. There was a KB article on how to get around this bug, but it didn't work (I had a ticket open for it). Now it's a moot point because with SP1 it's been fixed! Yes...I will now enjoy plenty of warning when future service packs break things. Goody! :)

On this one it's tricky to figure out what the cause is. The normal history and log menus are completely disabled in replication monitor so I can't even see what the error/problem is. Arrrghh. Time to open another ticket...sigh. (I opened 3 previously today on SQL Server...and it's only 11:19PM so that makes 4 in one day. Can someone remind me again why I'm paying Microsoft for this product and not the other way around?)

4/27/2006: Update on the replication issue: I got away without opening a ticket (after getting some rest and thinking about it). I noticed that SQL Server lost the names of the replication databases (for some reason) on each subscription, after SP1 was installed. With some experimentation, I found there was a "simple"workaround...blowing away all publications and subscriptions and recreating them from scratch.

Simply wiping out just the subscriptions and redoing them would have been easier, but that didn't completly work. The new subscriptions functioned, but for some reason the old ones (with missing database names) were still there, like "phantoms". Leaving it this way would have made the replication monitor useless, because it would have always indicated an error on every publication (for the phantom subscriptions)..even when the "real" ones were fine.

So fixing it required blowing away the subscriptions as well. This is not a big deal if you only have a few tables that are small. Fortunately that's our case. Unfortunately one of them allows the sharing of information on site members across the Exhedra sites (and databases). This big guy is a few million rows. So this meant resnapshotting and sending all that info over the network to the other databases again. The users on those sites are not very happy with us right now, becuase this is killing their activity on the databases/sites. But it's one of those things that "has to be done". Otherwise no one could create new accounts...not a good situation either.

So my recommendation is to allocate extra time in your Service Pack1 Upgrade planned downtime to possibly handle rebuilding all of your replication. Good luck.

Ian Ippolito

SQL Server 2005 performance testing with hyperthreading and MAX DOP

2006-04-19T03:20:00Z

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

A SQL Server 2005 Index fragmentation "feature" you NEED to know about

2006-04-14T16:01:00Z

Fragmentation of your SQL Server indices builds up over time from new data being added to your tables. This means that the more work your database does...the more it will slow down on scans, meaning that your queries will run slower and slower over time.

So it is a good idea to recreate your indices in SQL Server 2005 regularly (this gets rid of the fragmentation). You can also do an index defrag...and this can be done online. However it is not as effective as a complete recreate as it doesn't reorganize everything. So eventually you will be forced to do a rebuild.

So what are your options? The new "online rebuilding" of indices in SQL Server is as highly limited as it has been highly touted. I contacted MSFT support becuase I couldn't use it on half of my tables without the job bombing out. They informed me that if you have any # of things that appear in every "real database" (like a clustered index!) it won't work. So this means you are back to the SQL Server 2000 method of taking down your database and recreating the indcies. Yes this means a maintainence downtime window for your supposedly 24 hour/7 day a week database...and if you don't do it...your database WILL get slower and slower and the user complaints will get louder and louder. So much for the "self-tuning" advertised in SQL 2005's Executive review.

Anyway, I grudgingly did this and saw some indices vastly improve their performance. But I also noticed that I just couldn't get rid of the fragementation on some tables. The tech at Microsoft explained that on smaller tables, SQL Server will not be able to get rid of the fragmentation. I saw rates of 60-80% on tables with a few thousand rows.

Here is an example (from his testing) that shows the problem:

-----------------------

USE [mytest]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].(

[CustomerID] [nchar](5) NOT NULL,

[CompanyName] [nvarchar](40) NOT NULL,

[ContactName] [nvarchar](30) NULL,

[ContactTitle] [nvarchar](30) NULL,

[Address] [nvarchar](60) NULL,

[City] [nvarchar](15) NULL,

[Region] [nvarchar](15) NULL,

[PostalCode] [nvarchar](10) NULL,

[Country] [nvarchar](15) NULL,

[Phone] [nvarchar](24) NULL,

[Fax] [nvarchar](24) NULL

) ON [PRIMARY]

n And then we insert about 200 rows data and add a index in management studio: PK_C clustered, unique, primary key located on PRIMARY CustomerID

select object_id('c') –-the result is 1637580872

ALTER INDEX ALL on c REBUILD with (FILLFACTOR=100)

SELECT * FROM sys.dm_db_Index_physical_stats(7,1637580872,null,null,null)

--the result avg_fragmentation_in_percent will be 66.6666666666667, refer to attached doc file screen shot 1

And then we run the following command several times:

insert into c select * from c -- (23296 row(s) affected)

About the result avg_fragmentation_in_percent is 32.2%

And then we run the following command several times:

insert into c select * from c -- (186368 row(s) affected)

About the result avg_fragmentation_in_percent is 6.8%.

When we increase the data of the table, we can find the page count will increase and the avg_fragmentation_in_percent value will decrease, it is controlled by SQL Server internal design, if we continue to insert more data, we can find the value will reach nearly 0.

----------------------------------------
So this is something to keep in mind when defragging.

Ian Ippolito
http://www.RentACoder.com

Classic ASP: Not ready for prime-time?

2006-03-19T15:56:00Z

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?

2006-03-18T00:21:00Z

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)

2006-03-18T00:18:00Z

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