Hi,

My problem can be summarized as follows:

I have a social networking web site running on asp.net 2.0 and sql 2000.
The web site is pretty intense in terms of database load. Recently we have
updated the main page of the web site to make it display content based on
the user's country, and her relationships, as opposed to just displaying
the same content for everyone. After making those changes, the sql server
is shooting to 100% cpu usage.


Below is a breakdown of one of one of the stored procedures that is displaying
high readings in the profiler:

p.s. I can send a snapshot of the complete SQL Profiler trace if that helps.


spRankListCountryTopRankedUsers :


1. Environment and applications used for both code and DB

Visual studio 2005 software developer edition
Development language C#

Database:
SQL 2000


2. Observation (The problem):

There are a number of stored procedures that get loaded when the main page
of a site is loaded most of them have a small read and duration value. I
included on of the four stored procedures that have a higher than the average
read and duration value.



3. Requested Solution:

The question is how to reduce the read and duration value to make the page
load faster thus applying what ever solution is suggested on the other remaining
procedures?



4. The test results from the profiler:

Text Data: exec spRankListCountryTopRankedUsers @CountryID
= 0, @PageSize = 5
LoginName: muhannad
CPU: 406
Reads: 1392
Writes: 0
Duration: 403
Client ProcessID: 1900
SPID: 53
StartTime: 49:01.4


5. Database related tables and stored procedure structure


i. spRankListCountryTopRankedUsers
================================================== ==
CREATE PROCEDURE [dbo].[spRankListCountryTopRankedUsers]
/* This SP returns the list of the Top Ranking users ( in ascending order
) in a certain country ( according to the countryID), it also returns the
sign up date for each user. */

@PageSize int,
@CountryID int /* Specifies the CountryID from which we can derive the CountryName*/
AS

set nocount on
SET ROWCOUNT @PageSize

SELECT
tbl_users_stats.signup_date,
tbl_users.userid

FROM tbl_users

INNER JOIN tbl_users_rank ON tbl_users.id = tbl_users_rank.id
INNER JOIN tbl_users_stats ON tbl_users.ID = tbl_users_stats.id
INNER JOIN tbl_users_demographics ON tbl_users.ID = tbl_users_demographics.id


WHERE tbl_users_rank.rank < 20
AND tbl_users_demographics.country = @CountryID

ORDER BY tbl_users_rank.rank ASC

GO




ii. tbl_users
================================================== ==
Column Name Data Type Length Allow Nulls
================================================== ==
ID (PK) int 4 0
Userid varchar 30 0
Password varchar 50 0
FullName nvarchar 250 0
Raqam varchar 65 0
signup_ipaddress varchar 35 0
verified bit 1 0
Migrated bit 1 0
BackupEmail nvarchar 70 0




ii. tbl_users_demographics
================================================== ========
Column Name Data Type Length Allow Nulls
================================================== ========
ID(PK) int 4 0
Interest_Travel bit 1 0
Interest_Computers bit 1 0
Interest_PersonalFinance bit 1 0
Interest_Business bit 1 0
Interest_SmallBusiness bit 1 0
Interest_Sports bit 1 0
Interest_OfflineShopping bit 1 0
Interest_Music bit 1 0
Interest_Health bit 1 0
Interest_HomeFamily bit 1 0
Interest_Entertainment bit 1 0
Interest_OnlineShopping bit 1 0
Interest_TVCinema bit 1 0
Interest_Art bit 1 0
Interest_Internet bit 1 0
Interest_Cars bit 1 0
Interest_Wireless bit 1 0
Interest_Writing bit 1 0
Interest_Collections bit 1 0
Interest_Other nvarchar 300 0
JeeranInfo bit 1 0
JeeranAffiliate bit 1 0
Occupation i nt 4 0
Gender int 4 0
DOB nvarchar 50 1
Country int 4 0
City nvarchar 50 0
MailingAddress1 nvarchar 200 0
ZIP nvarchar 20 0
GSMCountry int 4 0
GSMArea int 4 0
GSMNumber bigint 8 0
WebsiteTopic int 4 0
CountryName nvarchar 100 1
DateOfBirth datetime 8 0

Note: Column “Country” is indexed



iii. tbl_users_rank
================================================== ==
Column Name Data Type Length Allow Nulls
================================================== ==
ID (PK) int 4 0
Score int 4 0
Rank int 4 0

Note: Column “Rank ” is indexed




iv. tbl_users_stats
================================================== ========
Column Name Data Type Length Allow Nulls
================================================== ========
ID (PK) int 4 0
signup_date smalldatetime 4 0
last_login smalldatetime 4 1
number_of_logins smallint 2 0
failed_logins smallint 2 0
last_email_login smalldatetime 4 1
number_of_logins_email smallint 2 0
last_hpm_login smalldatetime 4 1
number_of_logins_hpm smallint 2 0
last_ipaddress varchar 20 1
last_im_login smalldatetime 4 1
HP_status_change smalldatetime 4 1
referrer varchar 500 0
last_modified_by varchar 50 1
begining_of_payment smalldatetime 4 1
premium_expiry_date smalldatetime 4 1
last_helpdesk_login smalldatetime 4 1
last_imagestudio_login smalldatetime 4 1
number_of_logins_imagestudio smallint 2 0
last_briefcase_login smalldatetime 4 1
number_of_logins_briefcase smallint 2 0
briefcase_signup_date smalldatetime 4 1