10 cool web development related articles in 2007

Here’s a list of 10 cool ASP.NET, AJAX and web development
related articles and blog posts that I have written this year that
you might want to take a look:

disasters for production website and their solutions

Talks about 13 production disasters that can happen to any website
any time and bring down your business.

Google IG like Ajax Start Page in 7 days using ASP.NET Ajax and
.NET 3.0

This block buster article shows how ASP.NET AJAX, Linq to XML, Linq
to SQL and Workflow Foundation can be used to create a Google IG
like start page in just 7 nights. Learn how to put together such
hot technologies into one project and make a production site out of

Serve extensionless URL from ASP.NET without using ISAPI module or
IIS 6 Wildcard mapping

Currently there are only two ways to service extentionless URL like
www.pageflakes.com/omar that
hits something besides the default document – use a custom ISAPI
module or use IIS 6 wildcard mapping. Both has performance and
scalability problems because both intercepts each and every hit.
Learn how you can solve it by using a custom 404 handler.

Request format is unrecognized for URL unexpectedly ending in

Since ASP.NET AJAX 1.0 release, Microsoft prevented JSON hijacking
by adding a special content type header. But this caused us some

Cleanup inactive anonymous users from ASP.NET Membership

When you store anonymous user profile using ASP.NET Membership
provider and Anonymous Identification provider, you soon end up
with lots of idle anonymous user data where those users never come
back. We (Pageflakes) went through a lot of difficulty keeping our
database size down as we allow anonymous users to do almost
everything that a registered user can do. This introduces
scalability challenge. See how we solved this problem.

Prevent Denial of Service (DOS) attacks in your web

Web applications can be brought down to its knees by hitting the
site repeatedly or by calling expensive webservices randomly.
Anyone can write a simple loop that hits a webserver very
frequently from a high bandwidth connectivity and bring your
production server down. See how to prevent such application level
DOS attacks.

ASP.NET Ajax Extender for multi-column widget drag &

It’s an ASP.NET AJAX extender that allows Pageflakes style drag
& drop functionality between columns and rows.

ASP.NET Ajax in-depth performance analysis

While building an open source start page using ASP.NET AJAX, I have
done a lot of performance analysis on AJAX framework in order to
improve first time load and perceived speed of javascript rich
pages. Check out my analysis.

Think you know how to write UPDATE statement? Think again.

Learn how to optimize common UPDATE statements

Make a surveillance application which captures desktop and then
emails you as attachment

Some time back I needed to capture a certain computers desktop in
order to find out what that user is doing every day. So, I made a
.NET 2.0 Winforms Application which stays on system tray (optional)
and capture the desktop in given time interval (say every 60 secs)
and emailed the captured images to me as message attachment (say
every 30 mins).

Today I received MVP award for the 3rd time on Visual C#. Thanks
to Microsoft for the award and setting up my new blog. I will continue
both my MVPS Blog and this
blog from now on.

Cleanup inactive anonymous users from ASP.NET Membership Tables

ASP.NET 2.0 Websites that allow anonymous visit and anonymous
user profile have a unique challenge to cleanup unused data which
is generated by anonymous users who never come back. Every first
visit is creating one anonymous user, page setup, and other user
specific content. If the user never comes back, it still remains in
the database permanently. It is possible user might come back
within a day, or a week or a month. But there’s no guaranty
if user will ever come back or not. Generally sticky users are max
30% of the total users who come to most websites. So, you end up
with 70% unused data which are never needed. All these requires
cleanup, otherwise the database keeps growing uncontrollably and
gets slower and slower. This cleanup operation is humongous for
busy websites. Think about deleting millions of rows from several
tables, one after another while maintaining foreign key
constraints. Also the cleanup operation needs to run while the site
is running, without hampering site’s overall performance. The whole
operation results in heavily fragmented index and space in the MDF
file. The log file also becomes enormous in order to keep track of
the transactions. Hard drives get really hot and start sweating
furiously. While the CPU keeps smiling having nothing to do with
it, it’s really painful to watch SQL Server go through this
every day. Unless you clean up the database and maintain its size
under control; you can’t keep up with SQL Server’s RAM and
Disk IO requirement.

When a user visits the site, Asp.net Membership Provider updates
the LastActivityDate of aspnet_users table. From this field, I can
find out how long the user has been idle. The IsAnonymous bit field
tells me whether the user account is anonymous or registered. If it
is registered, no need to worry. But if it is anonymous and more
than 30 days old, I can be sure that the user will never come back
because the cookie has already expired. If you repeatedly logout
from your start page, all cookie related to the site gets cleared.
That means you are producing one new anonymous user record during
each log out. That anonymous record is never used because you will
soon log in to have your customized pages back and then you will
log out again. This will result in another anonymous user account
which again becomes useless as soon as you log in.

Here’s how the whole cleanup process works:

  • Find out the users which are old enough to be discarded
  • Find out the pages user has
  • Delete all the widget instances on those pages
  • Then delete those pages
  • Remove rows from child tables related to aspnet_users like
    aspnet_profile, aspnet_UsersInRoles, aspnet_PersonalizationPerUser.
    Remove rows for users to be deleted
  • Remove the users from aspnet_users
  • Pray that you did not accidentally remove any good user

Here’s the giant DB script which does it all. I have put
enough inline comment so that you can understand what the script is

   1: -- Number of days after which we give users 'bye bye'
   2: DECLARE @Days int
   3: SET @Days = 14
   5: -- No of users to delete per run. If it's too high, database will get stuck
   6: -- for a long time. If it's too low, you will end up having more trash than
   7: -- you can cleanup
   8: DECLARE @NoOfUsersToDelete int
   9: SET @NoOfUsersToDelete = 1000
  11: -- Create temporary tables which holds the users and pages to delete
  12: -- As the user and the page is used to find out other tables, instead
  13: -- of running SELECT ID FORM ... repeatedly, it's better to have them
  14: -- in a temp table
  15: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PagesToDelete]') AND type in (N'U'))
  16: DROP TABLE [dbo].[PagesToDelete]
  17: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnetUsersToDelete]') AND type in (N'U'))
  18: DROP TABLE [dbo].[AspnetUsersToDelete]
  20: create table PagesToDelete (PageID int NOT NULL PRIMARY KEY)
  21: create table AspnetUsersToDelete (UserID uniqueidentifier NOT NULL PRIMARY KEY)
  23: -- Find out inactive anonymous users and store the UserID in the temporary
  24: -- table
  25: insert into AspnetUsersToDelete
  26: select top(@NoOfUsersToDelete) UserID from aspnet_Users where
  27: (isAnonymous = 1) and (LastActivityDate < (getDate()-@Days))
  28: order by UserID -- Saves SQL Server from sorting in clustered index again
  30: print 'Users to delete: ' + convert(varchar(255),@@ROWCOUNT)
  31: GO
  33: -- Get the pages of the users which will be deleted
  34: insert into PagesToDelete
  35: select ID from Page where UserID in
  36: (
  37: select UserID from AspnetUsersToDelete
  38: )
  40: print 'Pages to delete: ' + convert(varchar(255),@@ROWCOUNT)
  41: GO
  43: -- Delete all Widget instances on the pages to be deleted
  44: delete from WidgetInstance where PageID IN
  45: ( SELECT PageID FROM PagesToDelete )
  47: print 'Widget Instances deleted: ' + convert(varchar(255), @@ROWCOUNT)
  48: GO
  50: -- Delete the pages
  51: delete from Page where ID IN
  52: ( SELECT PageID FROM PagesToDelete )
  53: GO
  55: -- Delete User Setting
  56: delete from UserSetting WHERE UserID IN
  57: ( SELECT UserID FROm AspnetUsersToDelete )
  58: GO
  60: -- Delete profile of users
  61: delete from aspnet_Profile WHERE UserID IN
  62: ( SELECT UserID FROm AspnetUsersToDelete )
  63: GO
  65: -- Delete from aspnet_UsersInRoles
  66: delete from aspnet_UsersInRoles WHERE UserID IN
  67: ( SELECT UserID FROm AspnetUsersToDelete )
  68: GO
  70: -- Delete from aspnet_PersonalizationPerUser
  71: delete from aspnet_PersonalizationPerUser WHERE UserID IN
  72: ( SELECT UserID FROm AspnetUsersToDelete )
  73: GO
  75: -- Delete the users
  76: delete from aspnet_users where userID IN
  77: ( SELECT UserID FROm AspnetUsersToDelete )
  79: PRINT 'Users deleted: ' + convert(varchar(255), @@ROWCOUNT)
  80: GO
  83: drop table PagesToDelete
  84: drop table AspnetUsersToDelete
  85: GO

Now the question comes, when can I run this script? It depends on
several factors:

  • The lowest traffic period. For example, USA
    midnight time when everyone in USA is sleeping if your majority
    users are from USA
  • The period when there’s no other
    maintenance tasks running like Index Defrag or Database Bakup. If
    by any chance any other maintenance task conflicts with this
    enormous delete operation, SQL Server is dead.
  • The operation will take from 10 mins to
    hours depending on the volume of trash to cleanup. So, consider the
    duration of running this script and plan other maintenance jobs
  • It’s best to run 30 mins before INDEX
    DEFRAG jobs run. After the script completes, the tables will be
    heavily fragmented. So, you need to defrag the indexes.

Before running this script, there are some preparations to

  • Make sure you have turned of AutoShrink from Database Property.
    Database size will reduce after the cleanup and if SQL Server tried
    to shrink the database, there will be a big IO activity. Turn off
    auto shrink because the database will grow again.
  • Make sure the LOG file’s initial size is big enough to
    hold such enormous transactions. You can specify 1/3rd of the MDF
    size as LDF’s Initial Size. Also make sure the log file is
    not shrunk. Let it occupy HD space. It saves SQL Server from
    expanding the file and shrinking the file. Both of these require
    high Disk IO.

Once the cleanup job runs and the INDEX DEFRAG runs, the
database performance will improve significantly. The tables are now
smaller. That means the indexes are now smaller. SQL Server need
not to run through large indexes anymore. Future INDEX DEFRAGs take
shorter time because there’s not much data left to optimize.
SQL Server also takes less RAM because it has to work with much
less amount of data. Database backup size also reduces because the
MDF size does not keep increasing indefinitely. As a result, the
significant overhead of this cleanup operation is quite acceptable
when compared to all the benefits.

Note: I will be posting some stuffs from my old blog to new blog.
Please ignore if you have read them before.