Careful when querying on aspnet_users, aspnet_membership and aspnet_profile tables used by ASP.NET 2.0 Membership and Profile provider

Such queries will happily run on your development
environment:

Or you can get some user’s profile without any problem
using:

Even you can nicely update a user’s email in aspnet_membership
table like this:

But when you have a giant database on your production server,
running any of these will bring your server down. The reason is,
although these queries look like very obvious ones that you will be
using frequently, none of these are part of any index. So, all of
the above results in “Table Scan” (worst case for any query) on
millions of rows on respective tables.

Here’s what happened to us. We used such fields like UserName,
Email, UserID, IsAnonymous etc on lots of marketing reports at
Pageflakes. These are some
reports which only marketing team use, no one else. Now, the site
runs fine but several times a day marketing team and users used
call us and scream “Site is slow”, “Users are reporting extreme
slow performance”, “Some pages are getting timed out” etc. Usually
when they call us, we tell them “Hold on, checking right now” and
we check the site thoroughly. We use SQL profiler to see what’s
going wrong. But we cannot find any problem anywhere. Profile shows
queries running file. CPU load is within parameters. Site runs nice
and smooth. We tell them on the phone, “We can’t see any problem,
what’s wrong?”

So, why can’t we see any slowness when we try to investigate the
problem but the site becomes really slow several times throughout
the day when we are not investigating?

Marketing team sometimes run those reports several times per
day. Whenever they run any of those queries, as the fields are not
part of any index, it makes server IO go super high and CPU also
goes super high – something like this:

"http://omar.mvps.org/images/203b504bbfce_14EA7/clip_image0016.png">
"http://omar.mvps.org/images/203b504bbfce_14EA7/clip_image001_thumb2.png"
width="655" alt="">

We have SCSI drives which have 15000 RPM, very expensive, very
fast. CPU is Dual core Dual Xeon 64bit. Both are very powerful
hardware of their kind. Still these queries bring us down due to
huge database size.

But this never happens when marketing team calls us and we keep
them on the phone and try to find out what’s wrong. Because when
they are calling us and talking to us, they are not running
any of the reports which brings the servers down. They are
working somewhere else on the site, mostly trying to do the same
things complaining users are doing.

Let’s look at the indexes:

Table: aspnet_users
Clustered Index = ApplicationID, LoweredUserName
NonClustered Index = ApplicationID, LastActivityDate
Primary Key = UserID

Table: aspnet_membership
Clustered
Index = ApplicationID, LoweredEmail
NonClustered = UserID

Table: aspnet_Profile
Clustered
Index = UserID

Most of the indexes have ApplicationID in it. Unless you put
Application=’…’ in the WHERE clause, it’s not going to use
any of the indexes. As a result, all the queries will suffer from
Table Scan. Just put ApplicationID in the where clause (Find your
applicationID from aspnet_Application table) and all the queries
will become blazingly fast.

DO NOT use Email or UserName fields in WHERE clause.
They are not part of the index instead LoweredUserName and
LoweredEmail fields are in conjunction with ApplicationID field.
All queries must have ApplicationID in the WHERE
clause.

Our Admin site which contains several of such reports and each
contains lots of such queries on aspnet_users, aspnet_membership
and aspnet_Profile tables. As a result, whenever marketing team
tried to generated reports, they took all the power of the CPU and
HDD and the rest of the site became very slow and sometimes
non-responsive.

Make sure you always cross check all your queries WHERE and JOIN
clauses with index configurations. Otherwise you are doomed for
sure when you go live.