Optimize ASP.NET 2.0 Profile Provider before you go live
Do you know there are two important stored procedures in ASP.NET
2.0 Profile Provider that you can optimize significantly? If you
use them without doing the necessary optimization, your servers
will sink taking your business down with you during heavy load.
Here're a story:
During March,
Pageflakes was shown on MIX
2006. We were having a glamorous time back then. We were right on
Showcase of Atlas web site. The first company, not the second
or third. Number of visits per day were rising sky high. One day we
noticed, the server was no more. We restarted the server, brought
it back, again it died within an hour. After doing a lot of
postmortem analysis on the remaining of server's body parts, we
found that it was having 100% CPU and super high IO usage. The hard
drives were over heated and turned themselves off in order to save
themselves. This was quite surprising to us because we were very
intelligent back then and we profiled every single web service
function. So, we went through hundreds of megabytes of logs hoping
to find which webservice function was taking the time. We suspected
one. It was the first function that loads a user's page setup. We
broke it up into smaller parts in order to see which part is taking
most of the time.
private
GetPageflake(
string
source,
string
pageID,
string
userUniqueName
)
{
if( Profile.IsAnonymous ) {
using
(
new
TimedLog
(Profile.UserName,
"GetPageflake"
))
{
You see, the entire function body is timed. If you want to learn
how this timing works, wait until I make a post on it. We also
timed smaller parts which we suspected were taking the most
resource. But we could find not a single place in our code which
was taking any significant time. Our codebase is always super
optimized (after all, you know who is reviewing it, me).
Meanwhile, users were shouting, management was screaming,
support staff was complaining on the phone. Developers were
fainting one by one. Remaining ones were furiously sweating and
blood vessels on their forehead were coming out etc. Nothing
special, just a typical situation we have couple of times every
month.
Now you must be shouting, "You could have used SQL Profiler, you
idiot!" We were using SQL Server workgroup edition. It does
not have SQL Profiler. So, we had to hack our way through to get it
running on a server somehow. Don't ask how. After running
the SQL Profiler, boy, were we surprised! The name of the
honorable SP which were giving us so much pleasure was none
but the famous and the most pretigious one, which we all know by
name due to its incredible contribution to the development
of human civilization. Please welcome, the
great stored procedure "dbo.aspnet_Profile_GetProfiles"!
We used (and still use) Profile provider extensively.
Here's the SP:
CREATE PROCEDURE
[dbo].[aspnet_Profile_GetProfiles]
@ApplicationName
nvarchar
(256),
@ProfileAuthOptions
int
,
@PageIndex
int
,
@PageSize
int
,
@UserNameToMatch
nvarchar
(256)
=
NULL
,
@InactiveSinceDate
datetime
=
NULL
AS
BEGIN
DECLARE
@ApplicationId
uniqueidentifier
SELECT
@ApplicationId
=
NULL
SELECT
@ApplicationId
= ApplicationId
FROM
aspnet_Applications
WHERE
LOWER
(@ApplicationName)
= LoweredApplicationName
IF
(@ApplicationId
IS
NULL
)
RETURN
--
Set the page bounds
DECLARE
@PageLowerBound
int
DECLARE
@PageUpperBound
int
DECLARE
@TotalRecords
int
SET
@PageLowerBound
= @PageSize
* @PageIndex
SET
@PageUpperBound
= @PageSize
- 1 + @PageLowerBound
--
Create a temp table TO store the select results
CREATE
TABLE
#PageIndexForUsers
(
IndexId
int
IDENTITY
(0,
1)
NOT
NULL
,
UserId
uniqueidentifier
)
--
Insert into our temp table
INSERT
INTO
#PageIndexForUsers
(UserId)
SELECT
u.UserId
FROM
dbo.aspnet_Users
u, dbo.aspnet_Profile p
WHERE
ApplicationId = @ApplicationId
AND
u.UserId
= p.UserId
AND
(@InactiveSinceDate
IS
NULL OR
LastActivityDate
<= @InactiveSinceDate)
AND
(
(@ProfileAuthOptions = 2)
OR
(@ProfileAuthOptions
= 0
AND
IsAnonymous
= 1)
OR
(@ProfileAuthOptions
= 1
AND
IsAnonymous
= 0)
)
AND
(@UserNameToMatch
IS
NULL OR
LoweredUserName
LIKE
LOWER
(@UserNameToMatch))
ORDER
BY
UserName
SELECT
u.UserName,
u.IsAnonymous, u.LastActivityDate,
p.LastUpdatedDate,
DATALENGTH
(p.PropertyNames)
+
DATALENGTH
(p.PropertyValuesString)
+
DATALENGTH
(p.PropertyValuesBinary)
FROM
dbo.aspnet_Users
u, dbo.aspnet_Profile p, #PageIndexForUsers
i
WHERE
u.UserId = p.UserId
AND
p.UserId
= i.UserId
AND
i.IndexId
>= @PageLowerBound
AND
i.IndexId
<= @PageUpperBound
DROP
TABLE
#PageIndexForUsers
END
END
First it looks up for ApplicationID.
DECLARE
@ApplicationId
uniqueidentifier
SELECT
@ApplicationId
=
NULL
SELECT
@ApplicationId
= ApplicationId
FROM
aspnet_Applications
WHERE
LOWER
(@ApplicationName)
= LoweredApplicationName
IF
(@ApplicationId
IS
NULL
)
RETURN
Then it creates a temporary table (why not table data type?) in
order to store profiles of users.
-- Create a temp table TO store the select results
CREATE
TABLE
#PageIndexForUsers
(
IndexId
int IDENTITY
(0,
1)
NOT
NULL
,
UserId
uniqueidentifier
)
--
Insert into
our temp table
INSERT
INTO
#PageIndexForUsers
(UserId)
If it gets called very frequently, there will be too high IO due
to the temporary table creation. It also runs through 2 very big
tables - aspnet_Users and aspnet_Profile. The SP is written in such
a way that if one user has multiple profiles, it will return all
profiles of the user. But normally we store one profile per user.
So, there's no need for creating a temporary table. Moreover,
there's no need for doing
LIKE LOWER
(@UserNameToMatch).
It is always called with a full user name which we can match
directly using the equal operator.
So, we opened up the stored proc and did a open heart bypass
surgery like this:
IF
@UserNameToMatch
IS
NOT NULL
BEGIN
SELECT
u.UserName,
u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH
(p.PropertyNames)
+
DATALENGTH
(p.PropertyValuesString)
+
DATALENGTH
(p.PropertyValuesBinary)
FROM
dbo.aspnet_Users u
INNER
JOIN
dbo.aspnet_Profile
p
ON
u.UserId
= p.UserId
WHERE
u.LoweredUserName =
LOWER
(@UserNameToMatch)
SELECT
@@ROWCOUNT
END
ELSE
BEGIN
-- Do the original bad
things
It ran fine locally. Now it was time to run it on the server.
This is an important SP which is used by the ASP.NET 2.0 Profile
Provider, heart of ASP.NET framework. If we do something wrong
here, we might not be able to see the problem immediately, but may
be after one month we will realize users profile is mixed up and
there's no way to get it back. So, it was a pretty hard decision to
run this on a live production server directly without doing enough
test. We did not have time to do enough test anyway. We are already
down. So, we all gathered, said our prayer and hit the "Execute"
button on SQL Server Management Studio.
The SP ran fine. On the server we noticed from 100% CPU usage it
came down to 30% CPU usage. IO usage also came down to 40%.
We went live again.
Here's another SP which gets called on every
page load and webservice call on our site because we use Profile
provider extensively.
CREATE PROCEDURE
[dbo].[aspnet_Profile_GetProperties]
@ApplicationName
nvarchar
(256),
@UserName
nvarchar
(256),
@CurrentTimeUtc
datetime
AS
BEGIN
DECLARE
@ApplicationId
uniqueidentifier
SELECT
@ApplicationId
=
NULL
SELECT
@ApplicationId
= ApplicationId
FROM
dbo.aspnet_Applications
WHERE LOWER
(@ApplicationName)
= LoweredApplicationName
IF
(@ApplicationId
IS
NULL
)
RETURN
DECLARE
@UserId
uniqueidentifier
SELECT
@UserId
=
NULL
SELECT
@UserId
= UserId
FROM
dbo.aspnet_Users
WHERE
ApplicationId
= @ApplicationId
AND
LoweredUserName
=
LOWER
(@UserName)
IF
(@UserId
IS
NULL
)
RETURN
SELECT
TOP
1
PropertyNames, PropertyValuesString, PropertyValuesBinary
FROM
dbo.aspnet_Profile
WHERE
UserId = @UserId
IF
(@@ROWCOUNT
> 0)
BEGIN
UPDATE
dbo.aspnet_Users
SET
LastActivityDate=@CurrentTimeUtc
WHERE
UserId
= @UserId
END
END
When you run the SP, see the statistics:
Table
'aspnet_Applications'
.
Scan
count
1,
logical reads 2, physical reads 0,
read
-ahead
reads 0, lob logical reads 0, lob physical
reads 0, lob
read
-ahead
reads
0.
(1 row(s) affected)
Table
'aspnet_Users'
.
Scan
count
1,
logical reads 4, physical reads 0,
read
-ahead
reads 0, lob logical reads 0, lob physical
reads 0, lob
read
-ahead
reads
0.
(1 row(s) affected)
(1 row(s) affected)
Table
'aspnet_Profile'
.
Scan
count
0,
logical reads 3, physical reads 0,
read
-ahead
reads 0, lob logical reads 0, lob physical
reads 0, lob
read
-ahead
reads
0.
(1 row(s) affected)
Table
'aspnet_Users'
.
Scan
count
0,
logical reads 27, physical reads 0,
read
-ahead
reads 0, lob logical reads 0, lob physical
reads 0, lob
read
-ahead
reads
0.
(1 row(s) affected)
(1 row(s) affected)
This stored proc is called whenever you access some
property on Profile object.
First it does a SELECT on aspnet_application to find out the
application ID from application name. You can easily replace this
with a hard coded application ID inside the SP and save some
effort. Normally we run only one application on our production
server. So, there's no need to lookup application ID on every
single call.
From client statistics, you can see how expensive it is:
Then look at the last block where aspnet_users table is updated
with LastActivityDate. This is the most expensive one.
This is done in order to ensure Profile provider remembers when
was the last time a user's profile was accessed. We do not need to
do this on every single page load and web service call. May be we
can do it when user first logs in and logs out. In our case, a lot
of web service is called while user is on the page. There's only
one page anyway. So, we can easily remove this in order to save a
costly update on the giant aspnet_users table on every single web
service call.